Quick post here to document something that I’ll probably want to refer back to later. Why document it on my hard drive when I can document publicly right?
You can use the Analytical Report Manager (ARM) in Acumatica to create financial reports. Why it isn’t just called Financial Report Writer is beyond me, that would probably be a much better name. Anyways, it works just like other financial report writer applications like FRx in that you define Rows, Columns, and Unit Sets (optional) which come together to form a report that you can run.
For this post, I want to show some screenshots in case you want to have a report with a dynamic number of columns.
They key is defining all possible columns, but using the Visible Formula to suppress the ones that you don’t want to see, depending on what the user selected.
Acumatica Financial Report with Dynamic Number of Columns
Let’s say you want a report that can do something like this:
How?
Step 1: Request the Start Period from the user
Step 2: Request the End Period from the user
Step 3: Edit the Column Set (this is where the “magic” happens)
Step 4: Within the Column Set, create a separate column for each period (January through December, 12 columns total) and use the following formula in the Visible Formula row (making sure to change the 1s, 2s, 3s, etc. for each period).
=IIf(Left(@StartPeriod,2)<=1 And Left(@EndPeriod,2)>=1, true, false)
Step 5: Hardcode the month into the Data Source for each column to only pull data for that specific month. By following the screenshot below, you are only hardcoding the month, but not the year.
Wrap It Up
That’s basically it. Then of course you’ll want to spend some time to make the report look pretty.
Note: This method won’t work across multiple years. It will only work if the periods you are reporting on all fall within the same year.