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
- 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.
- 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:
https://gist.github.com/smarenich/9be6968ccd50545c50256222e1221624
Have a nice reporting!
How to use screen id of Pivot Table in my feature group implementation…This screen id is changing based on pivot table created on the database ..
Please suggest.
Regards,
Gaurav Katiyar
You can change ScreenID of the Pivot Table at the Site Map screen. Than you can include it in the Customization Project.
Hi Sergey,
I am using Acumatica 2020 R1. In the customization project, there is no option to add Pivot Tables.
How can we add Pivot Tables in our customization project ?
Please help.
Hi Gaurav, yes, you are right. there is no Pivot tables in the customization project.
The easiest way for you will be use of Import/Export XML – from the screen Pivot Tables SM208010
Thank You..Sergey
Hi Sergey,
Thanks for your reply.
Please confirm is there any other way to replace this manual work (Import/Export XML).
Like Auto import for this xml.
Hi Sergey,
Is this a correct way to follow the same (Import/Export XML from Pivot Tables screen) for Production or Live deployment ?
Hi Gaurav,
I’m not sure there is other way to bring it. I’ll check with platform team and update you if i find a better way.
Import Export of Xml is good way for production as well.
Sergey, is it possible to conditionally format the results (i.e. color?) If so are you able to show an example of how this would be done?
Hi Paul, Starting from 2019R1 you can do conditional coloring in Generic Inquires. Please check the latest version.