AX 2012 Account structure Report
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.
Pages
- axsolution.com
- D365 Opening Balance Upload entity( JV Upload)
- Dynamics AX 2009 & Ax2012 Keyboard Shortcuts
- Error Running Trial Balance Report at AX2012
- How to make Site Mandatory at Sales order or Purchase Order at AX 2009 and Ax2012.
- Inventory Closing and Adjustment in AX (AXAPTA)
- Product Variants are not displayed at Purchase order in AX 2012
- Start Date of Project cannot be updated since earlier hour forecasts exist. AX2012
- Sure Step Go Live Check List for AX
- The Transaction Combination Activate item Relation table is not Activated,AX2012
- Upload Account structure in D365 through Data Management
- Workflow in AX2012
- AX 2012 Account structure Report
- Fixed Assets Creation in AX 2012
- How to Add A New Field in AX Form : AX 2009 & AX 2012
- Configuring Advanced Bank Reconciliation Bank Statement Importing
- Encumbrance & Pre- Encumbrance in AX2012
- Price Discount In AX 2009 & AX 2012
Categories
Posts per category
AX Blog
- Creation of credit Note on same Sales order which is Imvoiced, in D365
- Hours exist for project XXXXXXX posted to voucher XX/HRS/0000002 on 9/28/2016 that is not included in WIP, in AX 2012 R3
- Prepayment must be created in a journal where amount is including of sales tax, A2012
- The transaction combination Activate item relation Table is not activated, In Ax2012 R3
- Wrong tax Calculation in AX2012 R3
- Voucher DM/PINV/16/000001 is already used as of date 5/10/2016. in Ax2012
- The estimate date must be the last day of the period. Periods are maintained in the Periods form. In Ax2012
- AX2012, Worker dropdown list is black at Hour Journal Lines
- HotFix for AX2012 for project Module.
- Changing the Decimal Places to More than 2, in AX2012
AX Error
- Hours exist for project XXXXXXX posted to voucher XX/HRS/0000002 on 9/28/2016 that is not included in WIP, in AX 2012 R3
- Prepayment must be created in a journal where amount is including of sales tax, A2012
- The transaction combination Activate item relation Table is not activated, In Ax2012 R3
- Wrong tax Calculation in AX2012 R3
- Voucher DM/PINV/16/000001 is already used as of date 5/10/2016. in Ax2012
- The estimate date must be the last day of the period. Periods are maintained in the Periods form. In Ax2012
- An exchange rate cannot be found for exchange rate type Average between currencies USD and AED on exchange date . in AX 2012
- ERROR :Enter an Activity number. The require Activity number for Expense forecast is marked in Project. In AX2012
- AX2012, Worker dropdown list is black at Hour Journal Lines
- No Account Exists for account Type Cost for the project, AX2012
AX Training Manual
D365 Post
- Sub ledger to Sub ledger Transactions in one voucher Noumber
- Transfer Funds from one bank to another bank : D365
- Creation of credit Note on same Sales order which is Imvoiced, in D365
- D365 Entity for Inventory balance Upload
- How to Upload Opening Balance with Finaancial Dimension having – in value
- D365 Opening balance Entity : (JV Upload)
- Upload Account Structure in D365 Through Data management
- Uploading Item Posting profile in D365 through Data management
- Create a business unit value in D365
- difference in Item Posting profile naming from AX2012 to D365
Download
Management Reporter
- Management Reporter (MR2012) Exchange Currency Rate Issue, AX2012
- Management Reporter for Microsoft Dynamics ERP (MR2012) known issues,AX2012
Posting Definition
- Hours exist for project XXXXXXX posted to voucher XX/HRS/0000002 on 9/28/2016 that is not included in WIP, in AX 2012 R3
- The transaction combination Activate item relation Table is not activated, In Ax2012 R3
- Posting Defination Issue : Voucher Do not balance,AX2012
Uncategorized
- Voucher not specified As of “05/8/2019″ while posting Invoice Journal In AX 2012/ D365
- Creation of credit Note on same Sales order which is Imvoiced, in D365
- Upload Account Structure in D365 Through Data management
- Create a business unit value in D365
- Azure VPC Connection
- Workflow – work item Subject in ax2012
- Online Microsoft Dynamics Learning Portal
- Encumbrance & Pre- Encumbrance in AX2012