Find department value from a dimension field in AX 2012

If you are in a hurry, this is the quick transact-sql statement.

Get department value of a dimension :

select DISPLAYVALUE from  DEFAULTDIMENSIONVIEW where DEFAULTDIMENSION = <DefaultDimesion> and name  ='Department';

Get department value of a dimension in a segmented entry:

select DISPLAYVALUE from DIMENSIONATTRIBUTELEVELVALUEALLVIEW  join DIMENSIONATTRIBUTE  on DIMENSIONATTRIBUTE.RECID = DIMENSIONATTRIBUTELEVELVALUEALLVIEW.DIMENSIONATTRIBUTE where DIMENSIONATTRIBUTELEVELVALUEALLVIEW.VALUECOMBINATIONRECID = <LedgerDimension> and DIMENSIONATTRIBUTE.NAME = 'Department'

Replace DefaultDimension and LedgerDimension with the value of field with the same name in your table in question.

The explanation..

In AX 2009, we used to have an easy way to find the value of a financial dimension. For example we can find customer’s department by just CustTable.Dimension[1], CustTable.Dimension[2] for Cost center, and so on. This works similarly when refers to ledger account. For example we could get department in a general journal transaction by looking at LedgerJournalTrans.Dimension[1].

Move to AX 2012 things getting more flexible, at the cost of being more complicated. AX 2012 distinguishes between financial dimension that is attached to an entity such as customer, and financial dimension that is attached to ledger account. When refers to entity, the dimension is stored as a field named DefaultDimension. When refers to ledger account, the dimension is stored in the field named LedgerDimension. Special for ledger account in table such as LedgerJournalTrans, this field store both the ledger account (main account) as well as financial dimension, and this is displayed as segmented entry within AX form.

But note that LedgerDimension field can also store a single value. For example in LedgerJournalTrans.LedgerAccount where AccountType is other than Ledger, the LedgerDimension may refers to Customer, Vendor, Bank etc. which will display a single value instead of segmented entry.

If understanding these fields name may be confusing, here is table to make it easier :

Field name	What is it?
DefaultDimension	A foreign key field that represent financial dimension.
LedgerDimension	A foreign key field that represent one of this: -          Main account along with financial dimension into single value, displayed as segemented entry in AX client form. -          Single entity for main account -          Single entity for other than main account such as Customer, Vendor, Bank, Project, and Fixed Asset.

There has been numerous blogs and white paper that explains how to work with financial dimension using classes in X++. Unfortunately as the product is getting bigger, it is understandable that organizations assign task to specific people. For example those who work with reporting side of AX may have little exposure to classes in AX and would rather a direct usable sql statement to get the job done.

So, the sql statement on the top is the direct sql needed. However note that in AX, a financial dimension name is not a label but data. Depends on customer specific the installation, ‘Department’ may not exist as financial dimension name. One work with direct sql statement may take this into account and put it as a variable or parameter that can be changed when someone in finance department like to change it to something else.