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.
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.
