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!