Tables Trial Balance Report uses to calc Revenue?

I’ve been asked to create a BAQ that returns Revenue totals by fiscal year and period such that the numbers should match trial balance report. I’m hoping someone can point me in the right direction of how to achieve this(what tables/fields).

I found the table GLPeriodBal, give me GL accounts(BalanceAcct), FiscalYear, FiscalPeriod, and BalanceAmt. If I sum BalanceAmt for the GL codes I know are related to Revenue category the numbers are correct.

I found COASegValues table has GL accounts(SegmentCode) and corresponding Category(e.g. Revenue), so I have everything I think I need but can’t get the COASegValues table to join with the GLPeriodBal table via SegmentCode as it’s not trulely the GL code(Segment value used to construct the GL Account.). Could someone assist with how to join these tables?

image

vs

image

getTrialBalance.baq (51.7 KB)

1 Like