Hi,
I am trying to create a Pivot table in excel using the LedgerCube from Ledger budget, which is part of the standard cubes in AX2012.
Ledger Budget holds the 2 measure fields - BudTransactionCube.TransactionCurrencyAmount and AccountingCurrencyAmount, which is ok,
but I need to filter on the LedgerDimension (recId from DimensionAttributeValueCombination) - to filter on a special combination.
Now, as far as I can see the LedgerDimension should be included in the cube as it has the AnalysisUsage: Attribute - but in Excel
building the Pivot table - I cant see the field.
See attachment.
Any suggestions
Thanks
Jesper GeneralLedgerCube.docx (138 KB)
Hi I am facing the same issue , could someone please help me on this . Since its an older version its pretty hard to find much resources related it .
Hi Jesper,
Thanks for sharing your insights. I just went through the issue you mentioned and the attachment mentioned for reference.
This is by design in AX 2012.
While LedgerDimension exists in the model and is marked with AnalysisUsage = Attribute, it isn’t exposed as a usable dimension in the standard Ledger/Budget cubes. Excel only shows dimensions explicitly modeled in the cube, and the full DimensionAttributeValueCombination recId is not surfaced. Standard cubes expose individual financial dimensions rather than the full ledger combination.
Practically, you can filter using individual dimensions, extend or create a custom cube to expose LedgerDimension, or handle this via custom reporting. So this behavior is expected, not a configuration issue.
We’ve worked through this same scenario earlier on AX 2012 and can help further, including if this connects to a migration or upgrade. If useful, we can have a quick chat and get things sorted out.
Hi @Athira_Reghuraman
We’ve handled a similar scenario earlier on AX 2012, so happy to help if you’re still looking into this.
We’ve worked through the same cube limitations before and can also assist if this connects to an upgrade or migration. If useful, we can have a quick chat and see how best to approach it.
Email here
Hi Jesper,
When creating a Pivot Table in Excel using the LedgerCube from Microsoft Dynamics AX 2012, the budget amount measures such as AccountingCurrencyAmount and TransactionCurrencyAmount are available by default. These measures represent the budget values and can be placed in the Values area of the Pivot Table to display budget totals. Initially, the values appear as a grand total because no account or dimension filters have been applied.
In AX, every budget entry is always associated with a Main Account, which forms the core component of the LedgerDimension. While the LedgerDimension itself is a technical identifier and is not exposed in Excel, the Main Account is available as a business-friendly field within the cube. By adding the Main Account to the Rows or Filters area of the Pivot Table, budget values can be grouped or filtered by account, effectively covering the main account portion of the LedgerDimension.
The remaining part of the LedgerDimension is formed by financial dimensions such as Cost Center, Department, Business Unit, Project, Purpose, or Location, depending on the customer’s configuration. These financial dimensions are individually available in the LedgerCube and can be added to the Filters area of the Pivot Table. Each dimension represents a component of the full account structure used in AX.
Once the required financial dimensions are added, specific values can be selected for each filter. For example, selecting a particular Main Account together with specific Cost Center, Department, and Business Unit values restricts the Pivot Table results to a single, precise account and dimension combination. Although the LedgerDimension field itself is not visible in Excel, the filtered result is functionally equivalent to filtering by LedgerDimension.
This behavior is by design in Microsoft Dynamics AX. LedgerDimension is a system-generated technical field, whereas the cube exposes only meaningful business dimensions for reporting. By filtering on the Main Account and relevant financial dimensions together, the LedgerDimension is effectively reconstructed in a supported and user-friendly manner. This is the recommended Microsoft approach and is commonly used for budget, actual, and forecast reporting in AX 2012.