D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

General Ledger Dimension Combination Table for External Reporting

  • 1.  General Ledger Dimension Combination Table for External Reporting

    TOP CONTRIBUTOR
    Posted Jul 19, 2019 03:39 PM
    Edited by Steve Latta Jul 19, 2019 04:32 PM
    ​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
    ------------------------------
    Conference-AXUG_200x200


  • 2.  RE: General Ledger Dimension Combination Table for External Reporting

    Posted Jul 22, 2019 10:18 AM
    Edited by Bart Kolodziej Jul 22, 2019 11:23 AM
    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
    ------------------------------

    Conference-AXUG_200x200


  • 3.  RE: General Ledger Dimension Combination Table for External Reporting

    TOP CONTRIBUTOR
    Posted Jul 22, 2019 01:47 PM
    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
    ------------------------------

    Conference-AXUG_200x200


  • 4.  RE: General Ledger Dimension Combination Table for External Reporting

    TOP CONTRIBUTOR
    Posted Jul 23, 2019 09:07 AM
    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
    ------------------------------

    Conference-AXUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role