Development Tips

Filtering on Dimension Values

Microsoft Dynamics NAV supports unlimited dimensions and unlimited dimension values. You can create as many as you want, and you can use those all across the application.

However two of these dimensions have got special treatment by setting them up them as global dimensions.

Why special? Answer could be because the global dimensions values are stored directly on the records they belong to. All other dimension values are stored in a separate table.

This means that you can filter on these two dimensions. In many places in the standard application, these Global Dimension are used on Filter fields that can be used to filter FlowFields.

However, to get any data on any of the other dimensions, you would have to rely on Analysis Views to retrieve the information.

In Microsoft Dynamics NAV 2013 onwards version, the dimensions functionality has been heavily redesigned.

Instead of storing all individual dimension values for each record in separate tables, each unique combination of dimensions and values gets an ID, and this dimension set ID is stored directly on the record that those values belong to.

With this change, all information about dimensions and their values are directly stored on the record.

Since all the required information is stored on the record, though somewhat indirectly, it will now be possible to filter on any dimension and any dimension value. As it turns out, it is, and it’s not that hard to do.

As mentioned, the records contain dimension set IDs, which are integers that represent the combination of dimension values for a specific record. The biggest problem is to convert a typical filter on a dimension into a filter of dimension set IDs.

Fortunately, we already have a few functions in Microsoft Dynamics NAV that can provide that information. With these functions in mind, we can build a logic where you can input any combination of dimensions and dimension values in the form of filters, and you can then calculate the corresponding set of dimension set IDs. With all of these IDs, we can build one long filter string and use it to filter on the dimension set ID field. This enables, with relative ease, direct filtering on Dimension Values.

In Codeunit 408 – DimensionManagement we have functions which can help us getting our task done:

  • GetDimSetIDsForFilter

DimFilter-1

  • GetNextDimSetFilterChunk

DimFilter-2

In Codeunit 46 – SelectionFilterManagement we have functions which can help us getting our task done:

  • GetSelectionFilterForDimensionValue

DimFilter-3

Using these functions we can build our logic to retrieve the required records from tables filtering on Dim Set ID field.

Let’s see the pseudocode of logic to get our work done.

This logic applies to Dimension table. With all values for which filter need to be applied.
DimFilter-4

Before executing above piece of code we have to prepare a Dimension Value record with [Dimension Code + Code (filter)] values with all the required filters we wish to apply filter for.

We can also find out which records do not have a value for one or more dimensions, for this we will add [Dimension Code] but for [Code] we will not provide the value/filter or blank.

Make sure when you define variables of Dimension & Dimension Value don’t forget to set Temporary property to Yes.