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. You can give two of these dimensions special treatment by setting them up as global dimensions. What is special about the global dimensions is that their 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, we have placed Global Dimension 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, 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, we have taken an important step: to store all information about dimensions and their values directly 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. This blog entry describes some suggested patterns for using filters on dimension set IDs.

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 page 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.

So here’s what that page could look like (as text representation):

OBJECT Page 50000 Dimension Set ID Filter
{
  OBJECT-PROPERTIES
  {
    Date=;
    Time=;
    Version List=;
  }
  PROPERTIES
  {
    SourceTable=Table348;
    PageType=List;
    SourceTableTemporary=Yes;
    OnAfterGetRecord=BEGIN
                       SetDimensionValueFilter
                     END;

    OnNewRecord=BEGIN
                  DimensionValueFilter := ''
                END;

    OnDeleteRecord=BEGIN
                     TempDimensionValue.SETRANGE("Dimension Code",Code);
                     TempDimensionValue.DELETEALL;
                     DELETE;
                     EXIT(FALSE)
                   END;

  }
  CONTROLS
  {
    { 1   ;    ;Container ;
                ContainerType=ContentArea }

    { 4   ;1   ;Group     ;
                GroupType=Repeater }

    { 2   ;2   ;Field     ;
                SourceExpr=Code;
                TableRelation=Dimension.Code }

    { 3   ;2   ;Field     ;
                CaptionML=ENU=Dimension Value Filter;
                SourceExpr=DimensionValueFilter;
                OnValidate=BEGIN
                             InsertDimensionValues(DimensionValueFilter)
                           END;

                OnLookup=VAR
                           DimensionValue@1000 : Record 349;
                         BEGIN
                           DimensionValue.LookUpDimFilter(Code,Text);
                           EXIT(TRUE)
                         END;
                          }

  }
  CODE
  {
    VAR
      TempDimensionValue@1001 : TEMPORARY Record 349;
      DimensionValueFilter@1000 : Text;

    LOCAL PROCEDURE GetFilterString@28() Filter : Text;
    VAR
      DimensionMgt@1000 : Codeunit 408;
      SelectionFilterManagement@1001 : Codeunit 46;
      NextFilterChunk@1002 : Text;
    BEGIN
      IF FINDSET THEN
        REPEAT
          TempDimensionValue.SETRANGE("Dimension Code",Code);
          DimensionMgt.GetDimSetIDsForFilter(Code,
            SelectionFilterManagement.GetSelectionFilterForDimensionValue(TempDimensionValue))
        UNTIL NEXT = 0;
      NextFilterChunk := DimensionMgt.GetNextDimSetFilterChunk(1024);
      WHILE NextFilterChunk <> '' DO BEGIN
        Filter += NextFilterChunk;
        NextFilterChunk := DimensionMgt.GetNextDimSetFilterChunk(1024)
      END
    END;

    LOCAL PROCEDURE InsertDimensionValues@1(NewFilter@1000 : Text);
    VAR
      DimensionValue@1001 : Record 349;
    BEGIN
      TempDimensionValue.SETRANGE("Dimension Code",Code);
      TempDimensionValue.DELETEALL;
      DimensionValue.SETRANGE("Dimension Code",Code);
      DimensionValue.SETFILTER(Code,NewFilter);
      IF DimensionValue.FINDSET THEN BEGIN
        TempDimensionValue."Dimension Code" := DimensionValue."Dimension Code";
        REPEAT
          TempDimensionValue.Code := DimensionValue.Code;
          TempDimensionValue.INSERT
        UNTIL DimensionValue.NEXT = 0
      END
    END;

    LOCAL PROCEDURE SetDimensionValueFilter@2();
    VAR
      SelectionFilterManagement@1000 : Codeunit 46;
    BEGIN
      TempDimensionValue.SETRANGE("Dimension Code",Code);
      DimensionValueFilter :=
        SelectionFilterManagement.GetSelectionFilterForDimensionValue(TempDimensionValue);
      TempDimensionValue.SETRANGE("Dimension Code")
    END;

    PROCEDURE LookupFilter@6() : Text;
    VAR
      DimSetIDFilterPage@1001 : Page 50000;
    BEGIN
      DimSetIDFilterPage.SetTempDimTables(Rec,TempDimensionValue);
      DimSetIDFilterPage.EDITABLE(TRUE);
      DimSetIDFilterPage.RUNMODAL;
      DimSetIDFilterPage.GetTempDimTables(Rec,TempDimensionValue);
      EXIT(GetFilterString)
    END;

    PROCEDURE GetTempDimTables@8(VAR NewDimension@1000 : Record 348;VAR NewDimensionValue@1001 : Record 349);
    BEGIN
      NewDimension.COPY(Rec,TRUE);
      NewDimensionValue.COPY(TempDimensionValue,TRUE)
    END;

    PROCEDURE SetTempDimTables@3(VAR NewDimension@1000 : Record 348;VAR NewDimensionValue@1001 : Record 349);
    BEGIN
      COPY(NewDimension,TRUE);
      TempDimensionValue.COPY(NewDimensionValue,TRUE)
    END;

    BEGIN
    END.
  }
}

Let’s look at some of the elements on this page in more detail:

  • You’ll notice that this page uses SourceTableTemporary=Yes
  • The OnAfterGetRecord has some code to handle the deletion of records on this page. As noted above, we are dealing with temporary records, and in this case we do not want to run the OnDelete trigger from the Dimension table so we need to handle this manually.
  • The page has 3 public functions. The only one we’ll be using to call this page is the function LookupFilter, which will return a string representing the Dimension Set ID filter. I have used a little trick to make it easy to implement the calling of this page in a one-liner by having the RUNMODAL in the LookupFilter function. To allow the values entered on the page to be saved across runs, I added the functions GetTempDimTables and SetTempDimTables.
  • Instead of storing the filter string of values for each dimension, I store the dimension values in a temporary table. This makes it easier to call the functions in Codeunit 408 DimensionManagement that we need to get the dimension set IDs. The local function InsertDimensionValues converts the dimension values filter into Dimension Value records in the temporary table.

The next step is to implement an action on a page such as General Ledger Entries. All we have to do is to add a single action and a variable. Make the variable a global if you’d like to save the values when the action is re-run , or make it a local to make the page ‘forget’ what was entered before.

Here’s the Global Variable and the action that I added to the General Ledger Entries page (just below Action 50 GLDimensionOverview):

      DimSetIDFilterPage@1001 : Page 50000;

      { 3       ;2   ;Action    ;
                      Ellipsis=Yes;
                      CaptionML=ENU=Set Dimension Filter;
                      Image=Filter;
                      OnAction=BEGIN
                                 SETFILTER("Dimension Set ID",DimSetIDFilterPage.LookupFilter)
                               END;
      }

Now that we have the page and the action, we can run it and see what it looks like and how the filtering will work:
In page 20 General Ledger Entries, in the Entry group, choose Set Dimension Filter.

The new page that I added opens, and you can use the lookup on the Code column and the Dimension Value Filter field to select the values you want filtered as shown in the following screenshot:

When you choose the OK button, the General Ledger Entries page will be filtered by the corresponding dimension set IDs that will be shown on the page as illustrated by the following screenshot:

This was a fairly simple example to show how you can use dimension set IDs. But of course we can make the filter more complex. For example, if you want to know which records have the combination of AREA=30, BUSINESSGROUP=OFFICE and SALESPERSON=JR, you could set up a filter as shown in the following screenshot:

We can also find out which records do not have a value for one or more dimensions.
For example, the following screenshot illustrates a filter to show records with AREA 30 or 40 that do not have a value for PROJECT and SALESPERSON:

You can enter any filter using all of the operators you already know, such as .., <>, & and |.

This seems nice so far, but I would also want to be able to apply these filters to a page like Chart of Accounts and have the amounts reflect the applied filters. This change requires a new field and a small change to the FlowFields on table 15 G/L Account.

The new field will be a FlowFilter field:

    { 50000;   ;Dimension Set ID Filter;Integer    ;FieldClass=FlowFilter }

Additionally, change the CalcFormula for each FlowField that you want filtered such as the following example for field 32 Net Change:

CalcFormula=Sum("G/L Entry".Amount WHERE (G/L Account No.=FIELD(No.),
                                          G/L Account No.=FIELD(FILTER(Totaling)),
                                          Business Unit Code=FIELD(Business Unit Filter),
                                          Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),
                                          Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),
                                          Posting Date=FIELD(Date Filter),
                                          Dimension Set ID=FIELD(Dimension Set ID Filter)));

The action on page 16 Chart of Accounts is very similar to the one on page 20 General Ledger Entries that we created earlier. We just need to change the field we apply the filter to, so change the code to the following:

      DimSetIDFilterPage@1003 : Page 50000;

      { 5       ;3   ;Action    ;
                      Ellipsis=Yes;
                      CaptionML=ENU=Set Dimension Filter;
                      Image=Filter;
                      OnAction=BEGIN
                                 SETFILTER("Dimension Set ID Filter",DimSetIDFilterPage.LookupFilter)
                               END;
      }

That was it! Now you can filter directly on any dimension and any combination of dimensions on the chart of accounts and have the amounts be filtered. When you drill down on an amount, the filter will be carried over so you can see exactly which records make up the sum.

There are many more pages in the standard application for Microsoft Dynamics NAV where you can add actions such the two described above. So please add these filters wherever you find it useful.

Feel free to share your thoughts and comments on the feature and the code!

-Gert Robyns

Comments (10)

  1. Natalie K. says:

    Thank you very much for providing this!

  2. Jan Sch. says:

    Really Cool stuff but why isnt it in the W1 release application?

  3. Rob Hansen says:

    Nifty approach…too bad it isn't out of the box though.  🙂

    Are there still limitations on the length of the filter string that can be applied?  I know some limitations are gone with the move to unicode, but is the filter string length unlimited?  If not, this approach may hit the limit for companies with a large number of dimensions, as filtering for a single dimension value could result in a large number of dimension set IDs that match the filter.

  4. Thomas Kragh says:

    This is a really great feature, which could be great to have included in the final release. Also it could be great to have the Dimension key Set ID included in other ledger entry keys in their corresponding keygroups (Item(dim),vend(dim),cust(dim)…).

    Nice work.  

  5. Gert Robyns (MSFT) says:

    Thanks for your comments!

    The limit on the length of a filter used to be 1024 characters. This limit will be lifted in the release version of NAV 2013. There will still be limits but for practical purposes you could consider the length theoretically unlimited. The limits will be different depending on the construction/complexity of the filter and the elements making up the filter. SQL Server has a limit on the number of parameters it can handle in a query and NAV is also bound by that limit.

    I also have to point out a bug in the code in the post. In function GetFilterString two new lines should be added:

    NextFilterChunk := DimensionMgt.GetNextDimSetFilterChunk(1024);

    WHILE NextFilterChunk <> '' DO BEGIN

     // bugfix

     IF Filter <> '' THEN

       Filter += '|';

     // bugfix

     Filter += NextFilterChunk;

     NextFilterChunk := DimensionMgt.GetNextDimSetFilterChunk(1024)

    END

  6. Dmitri says:

    Gert,
    It looks that to allow business analysts to perform the kind of analysis they want to do I need to have a dimension with ca 0.5m dimension values that will increase by 0.3m values each year. What difficulties I might run into and why? Does NAV support dimensions with such large and growing number of dimension values? Where I can read more about this?

  7. J-P says:

    I found that if you have existing multiple existing dimension values but not from the same set, the behavior is off.

    1. J-P says:

      By the way, thank you very much for this.

    2. J-P says:

      When running it for a dimension code/value that doesn’t exist or match any, it will return a huge dimension entry filter string. I was expecting to not show any since it didn’t find a matching combination of code/value.

  8. Ewald V. says:

    Hi,

    Thank you for this, helped a lot.
    The current solution ignores the OR operator because it sees the entries in the temp table as consecutive records.
    Did a small adjustment by using a different buffer table.

    OBJECT Table 50004 Dimension Value Filter Buffer
    {
    OBJECT-PROPERTIES
    {
    Date=03/05/17;
    Time=12:56:23;
    Modified=Yes;
    Version List=;
    }
    PROPERTIES
    {
    }
    FIELDS
    {
    { 1 ; ;Dimension Code ;Code20 ;TableRelation=Dimension }
    { 2 ; ;Value ;Code250 }
    }
    KEYS
    {
    { ;Dimension Code ;Clustered=Yes }
    }
    FIELDGROUPS
    {
    }
    CODE
    {

    BEGIN
    END.
    }
    }

    OBJECT Page 50005 Dimension Set ID Filter
    {
    OBJECT-PROPERTIES
    {
    Date=03/05/17;
    Time=13:04:26;
    Modified=Yes;
    Version List=;
    }
    PROPERTIES
    {
    SourceTable=Table50004;
    PageType=List;
    SourceTableTemporary=Yes;
    }
    CONTROLS
    {
    { 1 ; ;Container ;
    ContainerType=ContentArea }

    { 4 ;1 ;Group ;
    GroupType=Repeater }

    { 2 ;2 ;Field ;
    SourceExpr=”Dimension Code”;
    TableRelation=Dimension.Code }

    { 3 ;2 ;Field ;
    CaptionML=ENU=Dimension Value Filter;
    SourceExpr=Value;
    OnLookup=VAR
    DimensionValue@1000 : Record 349;
    BEGIN
    EXIT(DimensionValue.LookUpDimFilter(“Dimension Code”,Text));
    END;
    }

    }
    CODE
    {
    VAR
    TempDimensionValueFilterBuffer@72000 : TEMPORARY Record 50004;
    DimensionValue@72001 : Record 349;

    PROCEDURE GetFilterString@28() Filter : Text;
    VAR
    DimensionMgt@1000 : Codeunit 408;
    SelectionFilterManagement@1001 : Codeunit 46;
    NextFilterChunk@1002 : Text;
    BEGIN
    IF FINDSET THEN
    REPEAT
    TempDimensionValueFilterBuffer.SETRANGE(“Dimension Code”, “Dimension Code”);
    DimensionMgt.GetDimSetIDsForFilter(“Dimension Code”, Value);
    UNTIL NEXT = 0;
    NextFilterChunk := DimensionMgt.GetNextDimSetFilterChunk(1024);
    WHILE NextFilterChunk ” DO BEGIN
    IF Filter ” THEN
    Filter += ‘|’;
    Filter += NextFilterChunk;
    NextFilterChunk := DimensionMgt.GetNextDimSetFilterChunk(1024)
    END
    END;

    PROCEDURE LookupFilter@6() : Text;
    VAR
    DimSetIDFilterPage@1001 : Page 50005;
    BEGIN
    DimSetIDFilterPage.EDITABLE(TRUE);
    DimSetIDFilterPage.RUNMODAL;
    EXIT(DimSetIDFilterPage.GetFilterString())
    END;

    BEGIN
    {
    }
    END.
    }
    }

Skip to main content