It’s a request that I’ve heard a million times in my AX implementation experience. Wouldn’t it be great if you just had the SQL for being able to extract Dynamics AX data so that you could use whatever reporting tool that you like for viewing Dynamics AX reports? You could use SSRS Report Builder, Crystal Reports, Microstrategy, Atlas or anything else that you want to pull the data – after all, SQL is universal. This was a request that I just had from a client. After over a year of having Dynamics AX, my client was really struggling with being able to report on AX data. That isn’t unusual from my experience. Dynamics AX, like many ERP systems, has a very complicated schema, and some queries can be very complicated. When I spoke to the IT manager at the company, he told me that they had an extremely strong SQL development shop with many external applications where they would like to run AX reports. He then presented me with 12 high level business cases, such as to be able to see all financial data by dimension with pure SQL and be able to pass parameters to these native SSRS reports, see all sales and integrate warehouse data, ect while not using any of the Dynamics AX built-in reporting mechanisms (even my dear Management Reporter was excluded). Tricky.. Tricky.. Tricky…

But what can I say, we all love a good reporting challenge. After I finished, my client was really happy, and he told me that they had been unable to find how to query financial dat. Upon hearing this, I realized that this would make an excellent subject for a blog post. After all, everyone loves financial reporting. So, let’s use the Dynamics AX 2012R2 CU7 Contoso machine to show how to query financial data an produce it for reports or data loads or powerpivot or anything else with just plain old SQL.

[Disclaimer: hell no, this is not supported at all in any official documentation. But many things that I have to do on implementations require an out of the box, non-documented approach to meeting a company’s requirements. This is just one of them. Use at your own risk. Also, please realize that I post this here in a simplified fashion where I did not add a few small things (querying by company or a specific chart of accounts) because if you understand these tables, you can easily get that data from specific columns. And be sure to parameterize your SQL statements, even though I didn’t do so here]

You can copy and paste all the code in Part 3 of this series

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:

Okay, so in Part 1, you got everything ready. You understood how to get the chart of Accounts and you all setup two helper temp tables. Pay attention to the strategy as this will come to pay major dividends. In part 2, we will populate those helper temp tables by using the Dynamics AX financial schema. See you soon.