Balance Sheet Data in AX - What Table(s)?

Finance and Operations/AX Developer Forum
1

What tables hold balance sheet data in AX? I need to extract balance sheet data for a particular accounting period.

Thanks,

Letron

2

“What tables hold balance sheet data in AX? I need to extract balance sheet data for a particular accounting period.”

To Clarify:

This is for end of period reporting. I see a table named LedgerClosingSheet. There is nothing in it now, but that could be because we haven’t actually done a close in AX yet. We are in preimplementation phase.

Thanks,

Letron

3

Why are you not using the standard functionality and creating a financial statement referencing the balance sheet accounts? Not sure why you are doing this externally, can you explain?

4

Sure,

We have a 3rd Party multi-site financial consolidation tool that data is loaded into for all sites corporate wide. Therefore all sites need to extract ledger and balance sheet data so that it can be transformed (mapped) and loaded into this consolidation tool. We have written extract routines for our legacy system to extract this data to text files, then we use dts packages to pickup the text files and load them into SQL Server tables so that our transformation and mapping routines can run. With the implementation of AX, I am hoping that we can get away from the text files and go database to database. I have a couple of ways in mind to do this with the Biz Connector being one of them. I’m just not sure of the table(s) to get a balance sheet summary by account.

Thanks,

Letron

5

I am no developer, but the ledger transactions are held in the LedgerTrans table. You would need to reference the LedgerTable to reference account type balance. I would recommend though that you talk to your partner, someone on the development side should easily be able to point you in the right direction.

6

Adam,

Thanks. Right now I’m in a toss up between LedgerTrans and LedgerJournalTrans for my ledger data. They both seem to have the fields that I need; however, LedgerJournalTrans has AmountCurDebit, and AmountCurCredit. Is the sum of these two fields related to the AmountCur field in LedgerTrans?

Also what is the purpose of LedgerClosingSheet, LedgerClosingTable, LedgerClosingTrans. I need to pull period ending balance sheet data and the LedgerClosingSheet looks pretty promising.

Thanks,

Letron

7

Not sure how you would extract a BS from the LedgerJournalTrans as this shows a Vendor payment, not the GL account it hit.

Closing Sheet and Closing Table hold the journal header and lines from the closing sheet, I would guess the closing trans will be populated from the posting of the closing sheet, but you would need to test this.