AUGForums.com

An Acumatica User Group

  • Forums
  • Podcast
  • Blog
  • Rolodex
  • Login
  • Start Here
  • Courses
  • Register

Acumatica Financial Report with Dynamic Number of Period Columns

January 10, 2017 by Tim Rodman

Create a Financial Report in Acumatica with a dynamic number of period columns. This example assumes that your columns are separate months in the same year.

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:

Acumatica Financial Report with Dynamic Number of Columns

Acumatica Financial Report with Dynamic Number of Columns

Acumatica Financial Report with Dynamic Number of Columns

Acumatica Financial Report with Dynamic Number of Columns

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)

Acumatica Financial Report with Dynamic Number of Columns

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)

Acumatica Financial Report with Dynamic Number of Columns

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.

Acumatica Financial Report with Dynamic Number of Columns

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.

Filed Under: Acumatica Reporting Tagged With: Acumatica, Acumatica Analytical Report Manager, Acumatica Blog, Acumatica Financial Reports, Acumatica Learning, Acumatica Training, Dynamic Columns, Dynamic Periods, Financial Reports

By using this website, you agree to our Terms of Use (click here)

Online Members

Recent Blog Posts

  • EP 37: Jody Lorincz, IT Manager at MHR Brands, sharing his Acumatica Customer Story (Podcast) January 13, 2021
  • EP 36: What Motivates ERP Veteran Nicole Ronchetti To Participate in Acu-Connect (Podcast) January 8, 2021
  • Acumatica and Scanco WMS – A Cautionary Tale for ISVs January 6, 2021
  • EP 35: Ideas for AUGForums.com Live with Ryan Brown (Podcast) December 12, 2020
  • EP 34: Talking Acumatica eCommerce and the Next Normal for Distribution, Wholesale, and Manufacturing with Ted Stenstrom (Podcast) December 11, 2020
Acumatica Learning Resources

Recent Forum Posts

  • Acumatica Payroll PTO Banks adjustment

    Has anyone figured out how you would make a manual entr...

    By MSylvia, 4 hours ago

  • Best Practices/Suggested Processes for Before and After Acumatica Version Upgrade

    We have a process in Sage 100 where we will run various...

    By CLove26, 5 hours ago

  • Denominated Currency Accounts, Foreign Currency and Project Accounting

    I'm noting this here for some future reference. I've s...

    By megan.friesen, 5 hours ago

  • RE: Importing Acumatica User Roles / User Security Permissions from Excel

    I tried a simple test in 2020R1 and 2020R2 and it doesn...

    By AhmedBahar, 7 hours ago

  • Combining Bill Retainage Invoices to one AR Invoice in Construction

    Hi was wondering if anyone has come up with a good work...

    By Mike Baio, 11 hours ago

  • RE: Generic Inquiry RESULTS GRID Column Order

    On the Results Grid tab you can drag and drop the items...

    By Michael Roszkowski, 13 hours ago

  • RE: Acumatica 2020 R2 OwnerID Field

    Thank you, Tim. Just faced this problem in a customized...

    By Anna Borisova, 1 day ago

  • Import Azure AD Groups to User Roles Screen

    Hi AUG, I'm cross posting this from the community as I ...

    By Michael.Barker, 1 day ago

  • Link EPApproval to APInvoice

    I am trying to write a GI that will link an Invoice to ...

    By Michael Roszkowski, 1 day ago

Recent Tweets

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2021 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.