In modern day Cloud ERP warfare it’s always good to have a Swiss Army Knife, a multi-tool pocket knife indicating extreme utility applicable to more or less any scenario at hand.

Swiss Army Knife was known as “Offiziersmesser“, the German name (lit. “officer’s knife”) – Source Wikipedia.

Acumatica’s reporting tools offer a similar combination of utilities such as the pocket knife for data extraction, a multi-tool capability to extract data for novices, and advance users of the ERP system.

Having a multi-tool like this comes with many benefits allowing businesses to extract and consume data as it’s being fed through various departments across an organization.

Based on your scenarios knowing when to use these tools is the key to using the Swiss Army Knife, this article reviews Acumatica tools and gives you an understanding of which tool is best for data extraction from your ERP system.

Acumatica’s Reporting Tools

In the latest release of 2020 R2, the following reporting tools are available for Acumatica users.

• Acumatica Report Designer, windows based desktop application
• Acumatica ARM – Analytical Report Manager, within the Acumatica system
• Generic Inquiries – GI, advanced query builder for data extraction along with pivoting capabilities
• Third Party Integration – With oData feed and API’s you can expose your data to 3rd party vendors or data analytics such as PowerBI, Data Self and others
• Velixo, powerful Excel based integration

I. Acumatica Report Designer

Acumatica’s home-grown solution Report designer, a windows based desktop application installed on your desktop/laptop, used for editing existing pre-defined reports or to create a new report from scratch to match your business needs.

Acumatica Report Designer provides visual tools that you can use to design custom reports.

With the Acumatica Report Designer, you can select data source for a report, compute values based on the selected data, and customize a report layout.

Design – You can access any report through the report designer directly from your hosted instance, using build schema you can join tables, relationship, filter parameters, sorting and grouping of the data.

Tools Area – Drag and drop the controls and assign the fields and formatting, set the properties per control as well. Categorize your reporting data to different section of header, detail and footer sections.

Preview Mode – It also provides a preview mode to check your output of report with sample data from your instance, gives you a good first hand look at your formatted report.

2020 R2 – In the latest release you can now prepare all your table relationship in GI and export as a report, makes life much easier to use this visual tool to drag and drop fields.

When to use Report Designer Tool?

Acumatica provides hundreds of pre-defined reports out of the box, you can use the canned reports for your business, you can edit the style-sheet to match your Company colors, make changes to layout by editing and managing the edited versions.

II. ARM Reports Tool Kit

The Analytical Report Manager toolkit (ARM) gives you the ability to create financial reports, and is also used to create Analytical reports in Acumatica ERP.

Reports that are constructed with this toolkit are called ARM reports to distinguish them from the reports created in the Acumatica Report Designer.

ARM reports is a pure example of another tool built within Acumatica framework to extract financial data, and it doesn’t need much technical expertise to retrieve financial data.
Read more about how to manage financial calendar.

The data in the analytical report can be displayed in monthly, yearly, and quarterly views, and the data can provide information from the point of view of departments, selected account classes, and other dimensions.

To design a report structure, you need to do the following:

• Define the row set and column set
• Optional: Specify the unit set for the report, if you want to split the report into sections
• Optional: Specify the report formatting settings

When to use ARM Report Manager?

ARM reports would be purely a CFO or accounting team’s request to slice financial data, if there requests are to see GL transactions data by month or year comparison then you are better off creating a ARM report, you can also consolidate reports from different data source.

III. Generic Inquiries Data Inquiry Tool

The most versatile reporting tool is GI’s (Generic Inquiries). If you ever wanted to extract data from multiple sources/tables within Acumatica then GI is the perfect tool to help.

GI is a customizable data inquiry tool, much like SQL Server profiler where you can write queries and execute to see the results output, a powerful advanced query builder system.

GI’s would be the best tool kit for data extraction, the quickest and easiest way to pull data for following scenario’s

• Show me a list of customers who purchased products in last 3 months in state of NY
• Show me a list of customers who purchased certain products during specific time periods and applied a coupon code, group it by state and customer class

GI is the basis for data extraction within Acumatica, you can view GI results and consume to create dashboard KPI’s Or create Pivot view Or expose to oData feed.

We could distribute or share these data views with third party applications like PowerBI or Velixo, or even export it to create new reports, it’s a mother of data extraction.

Design Elements – It’s quite essential to understand the data access layer (DAC) before you set out to create your generic inquiries. DAC is the data layer for pulling records from respective table/objects within Acumatica aka PX objects.

Identify the tables required for the data your planning to display as a result-set, set the relationship between these tables, parameters, sorting and grouping as highlighted in the screenshots.

Generic Inquiries allows to share your views with other users by providing access rights to your GI’s, share them on workspace’s or expose them as oData feed or on to mobile space.

GI can also be used for dashboard KPI’s to show aggregate values, you can also slice the data using pivoting technique within Acumatica.

When to use GI’s?

Utilize generic inquiries when you want to fetch new set of data for your business needs, it could be simple listing of customers or vendors or products.

IV. Velixo, Excel based reporting for Acumatica

A powerful Excel spreadsheet integration with Acumatica using oData feed and Generic inquires, it’s simple to use and packs a punch. It’s more like ARM reports on steroids using Excel’s capabilities to pull and push data.

Connect using your Acumatica login credentials and start building reports within Microsoft Excel spreadsheets that instantly include Acumatica data.

Velixo adds a ribbon to Excel to manage your connections, GL write and distribution list, more than 50 functions that are specially designed to work with general ledger, project data and generic inquiries.

When to use Velixo?

Velixo is a powerful Excel integration with Acumatica’s data, it has more than 50 different functions to pull data and has the ability to update your GL from Excel.

Visit Velixo website for product information and demo’s.

V. Third Party Integration Tools

Acumatica marketplace has several ISV’s with integrated business intelligence and reporting tools to choose from for your business.

You can preview all the providers by visiting Acumatica Marketplace.

In summary whatever your reporting needs may be, Acumatica provides you with a wide range of tools, similar to the concept of a Swiss Army Knife, so you can skin the data in many ways.

As an Acumatica user you have several options for data extraction, business intelligence and reporting needs, reach out to us if you have unique reporting needs or implement any of the above tools or services and guide our customers in the right direction.