Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Steve Latta]

?Hello all,

I’m trying to create some PowerBI reports based on the AX 2012 GeneralJournalAccountEntry table. There is a DisplayValue field which shows the full ledger account, including all dimensions, to which the transaction was posted. Unfortunately, we have multiple account structures composed two to four of these dimensions:

Main Account
Business Unit
Site
Cost Center

When I view this table in Atlas, I’m able to select each of these dimensions as if they were data fields. However, when I look at the raw table data, there is simply a field called “LEDGERDIMENSION” that clearly is a RecId from another table. The problem is, I cannot figure out what table/tables I need to link to it in order to report on each dimension separately.

Any ideas? I’m looking for a solution that with work within a SQL query, or even bringing in and linking individual tables within PowerBI. All the solutions I’ve seen online are DAX code.

——————————
Steve Latta
Accountant
Ortec, Inc.
Easley SC
——————————
?

[Bart Kolodziej]

Steve,

There’s an AX ERD diagram available which may help you out, but since the document has been retired by Microsoft, you’ll need to follow the instructions on “ how to create your own ERDs”:  AX ERD

Also, have you looked at the field LEDGERACCOUNT in [GENERALJOURNALACCOUNTENTRY] table? 

——————————
Bart Kolodziej
Midland Paper, Packaging + Supplies
Wheeling IL
——————————
——————————————-

[Steve Latta]

Hi Bart,

It’s not as easy as I’d hoped, but I think I’ve puzzled it out:

GeneralJournalAccountEntry.LedgerDimension = DimensionAttributeLevelValueAllView.ValueCombinationRecId
DimensionAttributeLevelValueAllView.DimensionAttribute = DimensionAttribute.RecId

So, essentially, the first link will return as many results as you have dimension levels in the hierarchy that account belongs in – one result for each level.  The second link gives you the ability to pull the dimension level name from the DimensionAttribute table, so you can structure reports based on the dimensions individually.

——————————
Steve Latta
Accountant
Ortec, Inc.
Easley SC
——————————
——————————————-

[Alex Meyer]

Steve,

If you still need the AX ERD to help, I have it hosted on my website at: https://alexdmeyer.com/ax2012erd/

——————————
Alex Meyer
Director of Dynamics AX/365 for Finance & Operations Development
Fastpath
Des Moines, IA
——————————
——————————————-