Hello Epicor Community!
I’m trying to get the final column of the Trail Balance report, called FiscalPerCB. Looking at the SQL statement, it says I should find it on the GLTBDtl table, but when I look for it in the BAQ form, I can’t find it. Where does this sit so I can add FiscalPerCB to a dashboard.
Thank you!
Might need to leverage GLPeriod Bal - this thread might help explain
I’m pretty sure that the Trial Balance is a Financial Report that creates the tables on the fly. I do not believe you will be able to use that table in a BAQ and would have to recreate the logic of the Trial Balance to get it in a BAQ.
I never got back to it to confirm… but I seem to recall @JasonMcD said there was a period 0 that held the opening balance and if you were to include that into your running sum, you could calculate the closing balance each month on the fly.
I somewhat did a memory dump after that, but yes I think that’s the idea.
Confirming:
In the table GLPeriodBal - there is an entry for period 0 for each account and it contains the opening balance. If you use an inner join to join it to FiscalPer, it will not return those rows. That was my undoing. I was using the FiscalPer End Date to filter to a relative date range and it was filtering out those rows with 0.
Do you understand how to create a running sum? If so, this should be all you need.
Generally, I loathe and ignore most of the built-in PDF reports in Epicor, and I make a BAQ/dashboard in their place.
But Trial Balance was always sufficient for me, so I never bothered to make a BAQ for it. I made a dashboard for GLJrnDtl, but not for the periods.
Only reason I dug into it at all was because of trying to understand Retained Earnings. Spoiler alert: there are no supporting transactions for Retained Earnings - it’s just EpiMagic.
