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.
Hi Tim,
Thank you for the tips. I have also used FRx before and find this quite similar in nature? However, it was quite a while back since i used FRx. Am just wondering if you have any samples where we have grouping of subaccounts in the column level by period. Meaning to say , I break down my subaccounts (say by department). Then, the department is group to print at the specific columns only by period. Do you have any examples of that? Thanks in advance.
Hi Ong,
Do you mean something like this?
http://imgur.com/a/CTzXC
I was so glad to see this example, it’s exactly what I was looking to do and couldn’t find instructions in any of the guides, so thank you!
One question – when I select a range of months i.e. Jan – Feb I’d like to see a YTD number that only includes those 2 months. How would I update the formula for YTD to only sum the range of months chosen?
Hi Karen,
I think FRx had a feature that would only total the printed columns if I’m not mistaken. But this isn’t as feature rich as FRx was unfortunately. The only thing I can think of would be to create 12 additional total columns and control which one prints with the
Visible Formula
.