I finally got tired of my demo Controller dashboard saying I have 0 Cash On-Hand. Several hours and GI edits later, I recognize that I may have opened a larger can of worms than I realized.
Pulling the correct accounts, branches, ledger, are no problem. But it seems as though I need to determine the most recent period in which each account had activity to pull balances(?)
Do any of you have a clean method of pulling and/or calculating account balances for one point in time (in a GI)?
Thanks in advance!
During Acumatica's most recent Reporting and BI webinar, I posed this question and a solution was routed back to me offline.
Set up a recurring transaction for $0 to post 1x/month for each account in question. As a result, there is a GL entry for every period, even if there was no "legitimate" activity within that period. Now your GI can find the data desired for this widget without trying to find the most recent period with activity for each account.
So simple! And yet I probably would not have thought of it.
For handling the "no transactions" situation, take a look at the Trial Balance Summary (GL632000) report and how they join the GLHistoryByPeriod and GLHistory tables together: