In the final part of our series, our hard work has paid off. You will be able to generate a general ledger report that is fast, and that will allow for our users to query dimensions however they like. We want our users to really see the power of AX and nothing does that like giving them access to data in a form that can be analyzed.

Step 6: Translate enum values into real words.

Now, one of my client’s requirements was a little interesting. They wanted to see the type of posting in the General Ledger. That isn’t all that unusual but SQL shows numbers, not words. So, what we have to do is look up the word meanings in AX.

By the end of this post, you will be able to do this:

See here is how the posting type appears which is common in AX. If you ever see values like this, it usually means that there is word translation somewhere (aka enums)

Now, what you do is simply open up the Development interface by clicking Ctrl + D if you have a license for it. Go to the table in the AOT, and look at the properties for the column:

And here are just a few of the values. We can see the numbers in sql matching the enums by looking at the properties.

Now, it is actually pretty to easy to add these. I’ll add a few here and pay attention to the SQL Statement below to see how we handle them.

Step 7: And it is time for the SQL that gives us Dynamics AX General Ledger Data

And congrats.. If you have followed these tutorials, you’ve learned a number of my tricks and how to really usher in financial reporting in Dynamics AX. Even today, past many implementations, I still get a good feeling inside when I take that company trying to utilize AX to it’s fullest and show them the data in the system and what can be done with it. I gave away some of my most coveted methods of extracting data for those of you out there who absolutely love reporting. AX is full of so much good data, and there is nothing wrong with having to harvest it in a non-typical form. The key is that you make it work for you. Have a great week and thanks for reading my post.

Note: In the last month, I have worked on over 8 implementations. Things are a little hectic, so if I am slow to respond to emails, please don’t take it personal.

Appendix for copy and paste:

Begin

CREATE
TABLE
#TempLedger

(

DIMENSIONATTRIBUTEVALUEGROUP
bigint,

MainAccount
nvarchar(100),

BusinessUnit
nvarchar(100),

Department
nvarchar(100),

CostCenter
nvarchar(100),

ItemGroup
nvarchar(100)

)

End

Begin

Create
Table
#DimensionValues

(

DIMENSIONATTRIBUTEVALUEGROUP
bigint,

DISPLAYVALUE
nvarchar(500),

Ordinal
int,

RowNumber
int

)

End

Begin

Insert
into
#DimensionValues
(DIMENSIONATTRIBUTEVALUEGROUP, DisplayValue, Ordinal, RowNumber)

(

Select
DALV.DIMENSIONATTRIBUTEVALUEGROUP ,DALV.DISPLAYVALUE, DALV.ORDINAL,
ROW_NUMBER()

Over (Partition
By
DALV.DimensionAttributevaluegroup
Order
by
DALV.Ordinal
asc) as
‘For Sorting’

from
DimensionAttributeValueCombination
DAVC

inner
join
DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION
DAVGC

on
DAVC.RecID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION

inner
join
DIMENSIONATTRIBUTEVALUEGROUP
DAVG

on
DAVG.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION

inner
join
DIMENSIONATTRIBUTELEVELVALUE
DALV

on
DAVG.RECID = DALV.DIMENSIONATTRIBUTEVALUEGROUP

where
DAVG.DIMENSIONHIERARCHY = 22565421207

Group
by
DALV.DIMENSIONATTRIBUTEVALUEGROUP, DALV.DISPLAYVALUE, DALV.ORDINAL

–Company filters or partition filters could also go in this where clause

)

End

Begin

Insert
into
#TempLedger
(DIMENSIONATTRIBUTEVALUEGROUP, MainAccount, BusinessUnit, Department, CostCenter, ItemGroup)

Select
DIMENSIONATTRIBUTEVALUEGROUP,

MAX(Case
ORDINAL
when 1 then
DisplayValue
ELSE
”
END )AS
‘MainAccount’,

MAX(Case
ORDINAL
When 2 then
DISPLAYVALUE
ELSE
”
END) AS
‘BusinessUnit’,

MAX(Case
ORDINAL
when 3 then
DISPLAYVALUE
ELSE
”
END) AS
‘Department’,

MAX(Case
Ordinal
When 4 then
DISPLAYVALUE
ELSE
”
END) AS
‘CostCenter’,

MAX(Case
Ordinal
When 5 then
DISPLAYVALUE
ELSE
”
END) AS
‘ItemGroup’

from
#DimensionValues

Group
by
DIMENSIONATTRIBUTEVALUEGROUP

End

Begin

SELECT
DAVC.DISPLAYVALUE,

MA.MAINACCOUNTID
as
‘Main Account’,

MA.NAME
as
‘Name’,GJE.ACCOUNTINGDATE
as
‘Date’,

GJE.JOURNALNUMBER ,

GJE.SUBLEDGERVOUCHER
as
‘Voucher’,

case
GJAE.PostingType
when 31 then
‘Customer Balance’
when 32 then
‘Customer Revenue’
when 41 then
‘Vendor Balance’
when 51 then
‘Sales Order Revenue’

when 71 then
‘Purchase Expenditure for Product’
when 82 then
‘Product Receipt’
when 83 then
‘Purchase Expenditure, Un-Invoiced’

when 84 then
‘Purchase Inventory Receipt’

when 203 then
‘Purchase Accrual’
when 221 then
‘Estimated Indirect Absorption’

when 222 then
‘Production Lot Size Variance’

when 223 then
‘Production quantity Variance’

Else
‘Code not in Report’
End
as
‘Posting Profile’,

GJAE.TRANSACTIONCURRENCYCODE
AS
‘Currency’,

Case
when
GJAE.ISCREDIT = 0 then GJAE.ACCOUNTINGCURRENCYAMOUNT
Else 0 End
as
‘Debit’,

Case
when
GJAE.ISCREDIT = 1 then GJAE.ACCOUNTINGCURRENCYAMOUNT
Else 0 End
as
‘Credit’,

TV.MainAccount, TV.BusinessUnit, TV.Department, TV.CostCenter, TV.ItemGroup

from
GENERALJOURNALENTRY
GJE

inner
join
GeneralJournalAccountEntry
GJAE

on
GJE.Recid = GJAE.GENERALJOURNALENTRY

inner
join
DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION
DAVGC

on
GJAE.LEDGERDIMENSION = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION

inner
join
DIMENSIONATTRIBUTEVALUECOMBINATION
DAVC

on
DAVC.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION and DAVC.ACCOUNTSTRUCTURE = 22565421207

inner
join
MAINACCOUNT
MA

on
MA.RECID = DAVC.MAINACCOUNT

inner
join
#TempLedger
TV

on
TV.DIMENSIONATTRIBUTEVALUEGROUP = DAVGC.DIMENSIONATTRIBUTEVALUEGROUP

End

Begin

drop
table
#DimensionValues

drop
table
#TempLedger

End