0

I want to get the department from the default dimension in AX 2012.

In the LedgerJournalTrans->lines->FinancialDimension I can fill the department but in the database for AX the default dimension is saved.

I'm working on SSRS reports d=so I want to get the department in SQL query.

Can anyone help me how to get the department and explain simply the relations

Thank you in advance and best regards,

Bernard Vander Beken
  • 4,848
  • 5
  • 54
  • 76
khaled el omar
  • 149
  • 1
  • 4
  • 13

3 Answers3

2

You can use this in SQL:

select DisplayValue from  DefaultDimensionView 
    where DefaultDimension = 1234567890
      and Name ='Department'

In X++:

select DisplayValue from defaultDimensionView 
    where defaultDimensionView.DefaultDimension == this.DefaultDimension
       && defaultDimensionView.Name == 'Department';
Jan B. Kjeldsen
  • 17,817
  • 5
  • 32
  • 50
Agus Riyadi
  • 106
  • 5
  • thank you and which view contain Offsetledgerdimension? – khaled el omar Feb 20 '14 at 10:03
  • @klaas explanation is the answer for the ledger dimension, but if you are not familiar with the table structure here is the code : select DISPLAYVALUE from DIMENSIONATTRIBUTELEVELVALUEALLVIEW B JOIN DIMENSIONATTRIBUTE A on A.RECID = B.DIMENSIONATTRIBUTE where B.VALUECOMBINATIONRECID = 987654321 and A.NAME = 'Department' – Agus Riyadi Feb 21 '14 at 11:51
  • You are correct, my answer is for ledger dimension, not default dimension. I would refrain from hardcoding the name for "dimension' though and create a parameter. – Klaas Deforche Feb 21 '14 at 13:15
0

Edit: as pointed out this is for ledger dimension and not default dimension, but I'll leave it for info:

Check out the DimensionAttributeLevelValueAllView view. It contains all the data you need.

  • Filter on the DimensionAttributeValueGroup field using the ledgerdimension recid from ledgerjournaltrans.
  • The DisplayValue contains the value of the dimension
  • The DimensionAttribute field refers to the dimension, for example the department, and it links to the DimensionAttribute table. This is setup so you'll have to store the recid for the department somewhere and join with this setup field. Otherwise there is no way to 'know' which of those dimensions the department dimension is.
Klaas Deforche
  • 1,151
  • 5
  • 9
0

see: How to set a single dimension value in AX 2012?

has a good whitepaper mentioned

Community
  • 1
  • 1
Chromableed Studios
  • 455
  • 3
  • 16