Getting Dimensions with Customer Transactions in SQL Query

(0) Share

Report

Posted on by Community Member

How can i get the dimensions using SQL Query without any AX Class. I am running a query on CustTrans in AX2012 but i am unable to get the appropriate dimensions in the transaction.

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

Community Member on at

Like (1)

Report

for this you need to understand the Financial dimension data model of dynamics AX 2012,

as per my understanding

CustTrans is linked to DimensionAttributeValueSet using DefaultDimension

DimensionAttributeValueSet is linked to DimensionAttributeValueSetItem table usning RecId

DimensionAttributeValueSetItem table is having field called dispalyValue which will give you the financial Dimension value

please validate

1 people found this reply helpful.

Was this reply helpful? Yes No
MikeG2001 on at

Like (2)

Report

I found the best method was to use the standard AX view DEFAULTDIMENSIONVIEW. This can be found using the DefaultDimension from the transaction and the DefaultDimension in this view. The Name in the view has the Financial dimension name and Display Value is the field you are after.

Regards,

Mike

Was this reply helpful? Yes No
Ronny 80 on at

Like (0)

Report

select

c.defaultdimension,

dav.*

c.*

from

custtable c

inner join DEFAULTDIMENSIONVIEW dav

on dav.defaultdimension=c.defaultdimension

where

c.dataareaid=@DataAreaID

Was this reply helpful? Yes No