Personalized Community is here!
Quickly customize your community to find the content you seek.
Latest TechTalk Videos
Have questions on moving to the cloud? Visit the Dynamics 365 Migration Community today! Microsoft’s extensive network of Dynamics AX and Dynamics CRM experts can help.
2021 Release Wave 2Discover the latest updates and new features releasing from October 2021 through March 2022.
2021 release wave 2 plan
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Community | FastTrack Program | Finance and Operations TechTalks | Customer Engagement TechTalks | Upcoming TechTalks | All TechTalks
Everyone who is working with financial dimensions in AX would like to see them in separate columns in Excel and today we will look how to achieve this for all Data Entities.
It’s also awesome because we have wizard that will do all the job for us!
In the Visual Studio under Dynamics AX > Addins select “Add financial dimensions for OData…”
Now we need to enter dimensions we want to expose. As you can see from a help text this dimensions should be setup in AX on Data Entities tab under General ledger > Chart of accounts > Dimensions > Financial dimension configuration for integrating applications.
Also we need to specify a model for new objects. Please note that the model should have reference to the Dimensions model, however you can add reference afterwards.
After clicking “Apply”, VS will show new project creation dialog. New project will be created. It will contain two Data Entity Extensions:
Please note that you cannot rename them because internal code works only with entity extensions that have name = Data Entity Name + “DimensionIntegration”.
Both entities has identical changes, the only difference is that one show default dimensions and another one ledger dimensions.
Let’s looks at DimensionSetEntity extension:
It has 3 new fields, one for each financial dimensions we specified in the wizard.
This fields are computed columns, each of them use same data method “getValueColumnForDimensionAttributeSql”.
It is quite interesting and has some smarties inside but hardcoded values as well.
That all we need to do. After compilation and synchronization we can open Data Entity in Excel and will see new fields in data connector designer.
How does it work? That the most exciting part. All the magic is inside DimensionAttributeValueSet table. If we look at it in AOT – nothing is really changed since AX 2012, but if we go to SQL management studio we will see bunch of new columns:
For each financial dimensions we have 2 columns: one contains value and another RecId.
Each computed column in Dimensions entity select value from this table.
So, if you are doing BI with 3rd party tools, now you can use DimensionAttributeValueSet and DimensionAttributeValueCombination tables to query all financial dimensions directly.
Business Applications communities