Steps to create a custom report of AX – account structure combination

Step 1: Determine the Financial Dimensions by looking at the Account Structure
Go to General Ledger à Setup à Chart of Accounts à Configure Account Structures
Here is a real good one because it has all kinds of rules. Notice that there are 5 dimensions already included: MainAccount, BusinessUnit, Department, CostCenter, and ItemGroup

Now, let’s add some complexity here. In some cases, a dimension can be filled in and in other cases, it won’t be filled in. Plus, the dimension can have varying string length making it look to be a very difficult SQL statements as the string functions don’t readily quite deal with this situation. And we still haven’t found the tables that we need.

Step 2: Find the Recid of the Chart of Accounts that you want to financial data on
It could be one Chart of Accounts or Multiple Chart of Accounts(it is your report so you judge that) but the first thing you need is the number that the system uses to identify this. You get this from a special table called DimensionHierarchy. Run the query in the screenshot below on your system:

Good, so now we know that the RecID is 22565421207 which will identify our chart of accounts in other tables as you will soon see.

Step 3: Create the Temp Tables in your SQL Statement (Note copy and paste block will be included below, screenshots here)
The key with producing SQL is to try to not overload AX by doing everything in RAM. You don’t want to mess with stuff like check processing. Also, you need to do some data manipulation and temp tables are an excellent way to simplify that mechanism. So, here is the key. You need to create two temp tables using the syntax that I’m posting here. Now, one more thing. Leave the first 4 columns the same, but after the word “Ordinal”, list your dimensions like here:

You understood how to get the chart of Accounts and you all setup two helper temp tables,we will populate those helper temp tables by using the Dynamics AX financial schema.

Step 4: Use your SQL and get the dimensions in a non-flattened out manner, insert that into the Temp Table
Financial data has a fancy word called dimensions. You can think of these as descriptive terms that group your data. Financial data is often described with multiple dimensions. We put rules how that data is described with our Account structure, which explains the order of dimensions and which dimensions appear. Go back above and you will see that main account appears before business unit which appears before Department which appears before Cost Center which appears before item group. We can use all 5 of these terms to describe a transaction or we can use just two of them (Green arrows mean optional). Classical interesting challenge for a report writer because you have the non-uniform string headache to deal with it. First what we have to do is get the values Ordered and sorted. AX provides a value called DimensionAttributeValueGroup which describes all values that go into one unique combination of dimensions. We also need to deduplicate the data and sort it for easier consumption. So, we will use a Row_Number() function here and a group by statement. If you highlight just the query, notice what it yields.

This actually makes a lot of sense. Ordinal defines the place where a value is listed. Display value is the actual value of a dimension. One combination of values used in the system is known as the DimensionattributeValueGroup. Every transaction in the General Ledger will link to one unique DimensionAttributeGroup which contains a set of dimension values and their orders. Isn’t this fun? It really starts to make sense.

Step 5: Use your SQL and get the dimensions in a non-flattened out manner, insert that into the Temp Table
When using SQL in AX, this is one of the most revered tricks that I can find. It concerns the art of flattening data, and you will have to use this in all sorts of places within Dynamics AX, other than just financial data if you want to use SQL. You have 2 challenges. You need to make the dimension data appear on the same rows, they need to also be separate columns for your report filtering, and you have to deal with the fact that some dimensions can be blank in some cases while filled in other cases, so you don’t have a uniform string.. Ouch challenging.. The secret to solving this is use an old-time trick where you ‘fool’ SQL by using the Group by clause but by using the MAX statement. That makes SQL Group each dimension value on the same row as it’s unique DimensionAttributeValueGroup. Thus, DimensionAttributeValueGroup will only appear one time.

You should end up with this report