Balance Sheet accounts closing balances By Month in General Inquiry report

  • 17 June 2022
  • 1 reply

I’ve been trying to create a GI report listing all the Balance Sheet account code closing balances at each month end across multiple years. The aim is to export that via API to Power BI in a kind of clean CSV state i.e. no grouping, just rows and columns.

So I want one row for each account code and each month end date. My first attempt generated 3,600 rows so it’s not that large a set of data.

My attempt is 95% right but it’s missing key values and on reflection I think I’m using the wrong set of tables (or in the wrong way) but the documentation isn’t good enough to work that out.

The reason it is wrong is when I compare the output to the Balance Sheet Financial Statement report I get some differences, but I can’t see why they are different. I have realised that the report doesn’t give me balances at a month end if there are no transactions in the month. I can fix this through a Power Query custom function but sadly I am still getting some errors. 

My model is as follows:

  • tables: GL.Account, GL.FinPeriods.MasterFinPeriod, GL.GLHistory, GL.Ledger
  • relations:
    • GLHistory Inner FinPeriod: FinPeriodID = FinPeriodID
    • GLHistory Inner Account: accountID = accountID
    • GLHistory Inner Ledger: ledgerID = ledgerID
  • conditions:
    • GLHistory.BalanceType = Actual
  • grouping:
    • GLHistory.FinPeriodID
    • Account.AccountCD

Can anyone point me in the right direction? Thanks!

1 reply

Hi. I’m struggling with the same issue. I’ve tried to add the GLHistoryByPeriod, but without any success. Have you found a solution yet?


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