971-270-0003 info@singlecoilinc.com

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:

AX 2012 Contoso Customer

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'
Regular AX Financial Dimension Result

AX 2012 financial Dimensions per row

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:

Financial Dimensions Row

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

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>