Solved

Nominal Trial Balance GI with subccounts

  • 18 July 2022
  • 6 replies
  • 164 views

Userlevel 1
Badge

Trying to get a GI that shows the requested period trial balance with subaccounts.     Managed to devise one but it only shows the nominal account/subaccount if there has been movement in the selected period.   Has anyone managed to create one successfully that could share the details

icon

Best answer by RoyceLithgo 21 July 2022, 00:38

View original

6 replies

Userlevel 5
Badge +2

Just find a report doing what you want and have a look at the Schema - eg. Trial Balance Detailed.

You’ll need to use GLHistoryByPeriod and GLHistory. But the way its done is a little tricky. GLHistory needs to be joined twice to GLHistoryByPeriod - one for current period activity and one for last activity period (ie. no activity in current period). When there’s no current period activity, you need to take the last activity period balance.

Userlevel 1
Badge

Thanks for your response I am trying to build the GI based on the Trial Balanced Detailed report.    Nearly there with it but still having a few issues with it not reporting the YTD figures on the lines

Userlevel 5
Badge +2

if you look at that report, the significant relations are GLHistoryByPeriod → GLHistory (alias AHCurrent) and the same join a 2nd time alias AHLast. AHCurrent only finds a row if there was activity in the specific period. AHLast will be the row with the Last activity (prior to current period). 

Take a look at the formula for Beginning Balance in groupHeaderSection2.

 

 

Userlevel 1
Badge

Thanks for all the replies I have managed to create a GI based on the Trial Balance Detailed report and got it working correctly

Hi @juneterris87

I am trying to create a similar GI, for the purposes of a dashboard. I would really love to see your GI to see your joins if you were willing to share this.
 

Userlevel 1
Badge

Hi I thought I had it working but when they rolled into the next year it didn't work correctly.   Recommending velixo now

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved