Would like to have a Column Set that only adds the months up to the “TO” period in the Parameter. Was using a copy of the Rolling 12 column set but it includes totals for months past the parameter selection. For example if we run it from 01-2023 to 03-2023 we want it to only include amounts for periods 01 to 03 and nothing beyond that.
Solved
Column Set For 12 MOS which only adds months in parameter Range
Best answer by tommaricle43
I think I figured out a work around. I created 12 total columns each with the correct formula for periods 1-12 like B then B+ C etc. I used the visibility formula to only allow the correct Total by month selected in the Statements Parameter. Seemed to work well but was a lot of work.
1 person likes this
+19Hello
I tried to solve the same problem for a client last month. Our developer helped me create a lengthy “If” statement to capture all the beginning/ending periods that a User might choose to print. (If we prompt for Start/End period…. user might choose 1 to 3, of 1 to 11 or 6 to 9 etc.)
When pasting a proper If statement into the Total column, the system Validated our If statement, and then truncated it. When trying to print the report, we saw errors. We were not able to solve the problem adequately - believe we forced the date range to always begin with period 1 and then quoted Velixo to our client.
I think that Acumatica Report Manager reports (ARM - financial reports built in the Preferences screen f Acumatica) do meet the needs of most clients, perhaps 90%. Clients with more complex needs enjoy Velixo, an ISV that adds Acumatica functions into Excel, enabling Excel to report directly from the Acumatica tenants. Velixo is the next step up from Acumatica Financial Report Writer screens in the Finance module.
Laura
Laura ~ Klear Systems, Inc. Irvine, CA. ~ Click my logo to Follow me ~
I think I figured out a work around. I created 12 total columns each with the correct formula for periods 1-12 like B then B+ C etc. I used the visibility formula to only allow the correct Total by month selected in the Statements Parameter. Seemed to work well but was a lot of work.
Hi tommaricle43 - please can you share the visibility formula? I have also been battling with this issue. The formula works perfectly for 12 months but not if I want to t[run the report as at 11-2024 for example.
Hello Community - is there anybody out there who might have a solution to this issue?
To repeat - “Would like to have a Column Set that only adds the months up to the “TO” period in the Parameter. Was using a copy of the Rolling 12 column set but it includes totals for months past the parameter selection. For example if we run it from 01-2023 to 03-2023 we want it to only include amounts for periods 01 to 03 and nothing beyond that. “
Many thanks
I am working on a project report with the similar requirement if you could please share your workaround with some little more detail 🙏🏽.
1 person likes this
I have an ARM report that users set the FROM and TO periods, hides the columns for periods outside the range selected, and totals only the visible columns.
The visibility formula for each period column is =IIf(Left(@StartPeriod,2)<=N And Left(@EndPeriod,2)>=N, true, false), where N is the number of the period (i.e. 1-12).
The Total column is GL type, not CALC, with the data source Amount Type set to Turnover (no periods). This seems to eliminate the multiple total columns as it’s only taking the turnover for the period(s) the user specified.
A couple caveats: This only works for a range of periods within the same fiscal year, and it works with full periods, not dates.
Hopefully that makes sense. Cheers!
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.