Some time ago, one of our clients asked for having the financial dimension values in different fields in the customer transactions form, with the purpose of filtering by these values. That, initially may sound simple, but it is a little more complex than I thought on that moment. On that post we are going to discuss about a possible solution for this problem which does not involve table fields creation.

Initial Consideration:

All we are going to see is related to financial dimensions, from DimensionAttributeValueSet, table, but everything could be applied to the ledger dimension, from DimensionAttributeValueCombination table, as well.

Problem:

As you all may know, working with dimensions in D365FO is a little less intuitive than in older AX versions. The problem is based in the fact that there is not a table which contains the RecId of the different dimensions combinations (DefaultDimension) and the different dimension values as fields in that table… ¿isn’t it?

The answer is no. Actually we have them in the DimensionAttributeValueSet, which RecId is the famous DefaultDimension used in every table with financial dimensions. On that table we have all we need, one field with the dimension value for each one of them. So, What is the problem?

Those fields are not in the XMLs to which we have Access from VS, but they are generated directly in SQL during the DB synchronization.

Show dimensions in a form without adding table fields Axazure

Thus, if we try to access to those fields, using their names, for example dimensionAttributeValueSet.DepartamentoValue, build would fail, because compiler won’t find the field in the DimensionAttributeValueSet table. Further, if we add the table as a data source to a form, view etc. fields do not appear also. However, there is a standard data entity, DimensionSetEntity (DimensionCombinationEntity for LedgerDimension), in which we have every dimension values as data entity fields (and a data entity is pretty the “same” as a view ).

¿How does the standard entity to get the dimension combination in fields?

The answer is, using computed columns.

Solution:

The DimensionSetEntity feed using computed columns, which change the SQL query directly, the different values from DimensionAttributeValueSet. That is possible because in SQL the fields with dimension values exist in that table, so, using computed columns you can get access from a view or data entity to the fields created during synchronization, and the name of those fields are set in the DimensionAttribute table. For that reason, we tried to reproduce the same process that the DimensionSetEntity does, but in a new view. And maybe you could be asking yourselves, if it is already created, Why not to use directly the standard entity? That is worth a question, because we always suggest the use of standar objects and processes, but you cannot add a data entity in a form as data source, and neither you can add it to a View. So the creation of a new view (AXZDimensionSetView) was our best choice.
The final result was as simple as it follows:

1 Create the AXZDimensionSetView view, with the DimensionAttributeValueSet as data source and add the RecID (that it is the DefaultDimension) as view field.

2 Create the methods that we will use to obtain the values of the different dimensions.
We will create as many methods as the number of dimensions to show, differently than standard does with DimensionSetEntity. That way we avoid to create generic methods, it is a completely unneeded complexity that does not give value in our case, because, if someday some new dimensions would be added, even with a generic method, we will have to develop to add the new fields. In the standard case, it makes sense because the fields are added with an add-in which create them automatically as every client has different dimensions, but we can save time and effort with no real cost doing it particularly for every dimension.

What we do with the code below is only to add the dimension field name, which is setup in the DimensionAttribute table to the view query. That way, we can see that after data base synchronization… :

Correct, the fields ClienteValue, ProveedorValue… have been added to the view query from DimensionAttributeValueSet. So, we achieve what we wanted, to have access from VS to those fields, using a view, Easy peasy!

3 Create string computed columns and associate in the view method property the appropriate method.

4 Add a new relation (of type normal) to the table CustTrans (or the table you need), pointing our new view.

5 And, that’s it! We add the view as data source to the CustTrans in outer join mode and we will be able to show the diferent dimension values in the grid as fields, so we can filter them.