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
Answer
Nominal Trial Balance GI with subccounts
Best answer by RoyceLithgo
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.
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.
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
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.
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
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.
Hi I thought I had it working but when they rolled into the next year it didn't work correctly. Recommending velixo now
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.