Displaying AX 2012 Customer Financial Dimensions

A frequent customer requirement is to display AX 2012 Financial Dimensions for different entities in row and also column format.  Reasons are typically due to some form of reporting or integration into third party systems.

One specific area that has become complex in AX 2012 is the new Financial dimension data model.  The new data model does not lend itself to easily extract all dimensions per an entity (customer, vendor, etc.) in a clean format without some more advanced sql.  Out of the box, these values are stored per name per row, this post will demonstrate how to show the Customer dimensions in a column format per customer account number.  With some minor modifications, the sql can be modified to accommodate other entities as well.

The SQL PIVOT command comes in very handy for this type of data manipulation.

The customer we are using is ‘US-013′ from the AX 2012 Demo Data Contoso company and has been updated as follows:

If we want to show a Customer and it’s financial dimension – a simple query will show us each dimension with it’s value on a separate row.

SELECT CUSTTABLE.ACCOUNTNUM, DEFAULTDIMENSIONVIEW.NAME, DEFAULTDIMENSIONVIEW.DISPLAYVALUE
FROM DEFAULTDIMENSIONVIEW
INNER JOIN CUSTTABLE
ON DEFAULTDIMENSIONVIEW.DEFAULTDIMENSION = CUSTTABLE.DEFAULTDIMENSION
where CUSTTABLE.DATAAREAID = 'usmf' and CUSTTABLE.ACCOUNTNUM = 'US-013'

with results looking something like this:

However if we have requirements where the financial dimensions are to be show in column format we need to rewrite the query as show below

select AccountNum, BusinessUnit,CostCenter,Department,ItemGroup from
 (
 select CUSTTABLE.AccountNum, Name,CUSTTABLE.DefaultDimension, CUSTTABLE.dataareaid, DimensionAttributeValueSetItem.DisplayValue from CUSTTABLE
 join DimensionAttributeValueSet on DimensionAttributeValueSet.RecId = CUSTTABLE.DefaultDimension
 join DimensionAttributeValueSetItem on DimensionAttributeValueSetItem.DIMENSIONATTRIBUTEVALUESET = DimensionAttributeValueSet.RecID
 join DimensionAttributeValue on DimensionAttributeValue.RECID = DimensionAttributeValueSetItem.DIMENSIONATTRIBUTEVALUE
 join DimensionAttribute on DIMENSIONATTRIBUTE.RecId = DimensionAttributeValue.DIMENSIONATTRIBUTE
 where CUSTTABLE.DATAAREAID = 'usmf' and CUSTTABLE.ACCOUNTNUM = 'US-013' ) d
PIVOT
(
 MAX(DisplayValue)
 FOR Name
 IN (BusinessUnit,CostCenter,Department,ItemGroup)
)
AS PivotTable

with results looking like this:

That’s it for today’s post. Happy AX report writing!