Building Your First Acumatica ARM Report
Here’s a guide on how to get started building your first Acumatica ARM Report. ARM is an acronym for “Analytical Report Manager.” With a little practice, a technical power user or a motivated CFO can be building and editing the income statement and balance sheet in a few hours. Not all ERP systems have good reporting tools, so it’s great that Acumatica provides the ability to build and modify the core financial reports within the software. ARM reports are specifically designed for reports based on general ledger and project accounting modules. For other business reports, you may need to use Generic Inquiries or the Acumatica Report Designer.
Using this post, you’ll be able to build your own Acumatica ARM reports using the web-based tools built into the system.
Let’s get started.
In this example you’ll create a balance sheet. Our balance sheet will compare this month’s data against last month’s.
Start by navigating to the “Report Definitions” screen (Screen ID CS2060PL). Here we have all the report definitions that ship with Acumatica, and any report definitions that have been created for your organization. Open a basic balance sheet ARM Report to examine its properties and preview the report. On the Balance Sheet ARM report, please note a few things:
- The row set (in this case DBALSHEET)
- The column set (DBALSHEETP)
Click ‘Preview’ to enter parameters and run the report. This is to show you the basic layout of the balance sheet that ships with Acumatica.
Acumatica ARM Report Steps:
In a nutshell, your steps to create your own Acumatica ARM Report will be:
- Define Column Set
- Define Row Set
- Define Unit set (optional)
- Create Report Definition
Defining the Column Set
The Acumatica ARM Report documentation (found within the Acumatica product) recommends creating the report set before the column set. However, we recommend starting with the column set. It’s a lot easier to get started with a column set. You will see immediate results in formatting your report title and column headers, and typically it’s quicker to set up a column set. That way, you can claim a small victory to build momentum for the detailed work of building, summarizing, and formatting your rows in the row set exercise.
To build your custom balance sheet, you’ll modify the existing balance sheet definition. You’ll create copies of existing row sets and column sets and modify those to create the report definition.
First, create a copy of an existing column set.
Navigate to the Column Sets screen (Screen ID CS2060P2) and select the balance sheet associated with the vanilla balance sheet. Click “Copy Column Set” and enter a code for the new column set code.
Important! It’s important to keep a consistent naming convention. Acumatica recommends: ” Develop naming conventions so that you can easily identify report definitions, row sets, and columns sets that are used together in the same reports. If you have multiple column sets that are used with the same row set in different report definitions, you can name the column sets with a prefix that helps you identify the row set with which the column set is used.”
Now you may edit your new column set. For our purposes, we’ll create a balance sheet that shows last month’s values compared to this month’s values.
- Follow the existing formatting but edit the formatting to make the titles larger. For example, change the height value of the first row (with the formula =Report.GetDefUI(‘RowCompanyBAccount.AcctName’) ) from 18 to 25.
- Change the subtitle from ‘Balance Sheet’ to =’Balance Sheet – Monthly Comparison
- Insert new columns to indicate “This Month” and “Last Month”
- Here’s how you’ll insert the new columns and dynamically insert the variables to pull the “This Month” and “Last Month” variables into the columns:
Draft your Row Set
Now, follow the same logic to create a copy of the baked-in row set. Later you will customize it, but right now we just want to define it.
Define the row set and save it. Now you are prepared to create a new report definition. The Report Definition will use the column set we customized and the row set that we just created. Here’s how:
Now you should test the report definition to ensure that you see the new columns we created: “This Month” and “Last Month.”
On the Report Definitions screen (Screen ID CS2060PL), click your new report, populate the parameters, and run the report.
Complete Your Row Set
Now you have a way to make changes to your row set and test incrementally to confirm that your formulas and formatting are working properly. Open the row set you created in the step above and begin to make the edits that you want.
Tips for creating a proper row set:
- Mind your column numbering and allow enough space to add and edit rows. Do this by using increments of 100. See below the “Code” column allows plenty of breathing room between row 1 (0100 – Assets) and the row below it (0200 – Current Assets).
- You may insert line breaks and empty rows for formatting. See example row 0310 which is has a Type of “Caption”, with a height of 16. This will be a null row.
- You may insert dynamic sums. In this example we see row 1200, Type “GL” with a formula in the value of =sum(‘0400’, ‘1000’). This, like Excel, will sum rows 0400-1000.
Row sets, depending on their complexity, can be time-consuming. However, if you follow a few guidelines you will be able to iterate through your work.
- When adding new rows to the bottom of the report, make sure to increment the column code by at least 100. (Eg if the last line is 0700, create the next row with a code of 0800.) This way, if you unexpectedly need to enter additional rows, line breaks, or sums after line 0700, you have “breathing room.” You could create that code with a value of 0710.
- While you are modifying the report, save changes regularly. For example, save the row set after you have added each new row.
- Use account classes to aggregate the data of some accounts instead of using complicated masks.While you are modifying the report, save each portion of your changes to the row set or the column set regularly. For example, save the row set after you have added each new row.
You may always refer to the existing vanilla reports to understand formulas, masks and sums. Additional Acumatica ARM Report documentation is found in the Acumatica help files, accessed by clicking the “?” in the upper right-hand corner of your screen.
How did your Acumatica ARM Report building go? Let us know if we can help!