Tuesday, 19 September 2017

Consolidation Reports using Pivot Tables

Hi Everyone,

You most probably know that if you need to prepare consolidated reports in Acumatica you should check ARM Reports (Analytical Reports Management)

However there is one more option available with last release of Acumatica
  1. Create a Generic Inquiry that extracts Account/Subaccount balances for specific period from GL. Here I have a separate article that describes way to extract GL data.
  2. Dynamic pivot table that will split data by ledgers as columns and Accounts/Subaccounts as rows.
The difference here from my previous post is that here we should change GI to return much more data - for all accounts/subaccounts, multiple ledgers and periods. We need provide more data specially for pivot table that would aggregate selected it based on our rules.
Do not forget that you need to specify list of ledgers for consolidation explicitly, I have done it with conditions on GI, but you can do that using reusable filters as well.

Here is the GI result. See that it returns balances for every account, ledger period:

Here you can find result a pivot table:

For GI I have used similar way to standard Trial Balance Detailed report. Tim Rodman has greatly described it in his blog. The only one specific difference is that my GI can also show empty lines, where not transactions were posted to specific account/ledger/period.

GI Source:

Have a nice reporting!

No comments: