June, 2012

  • Microsoft Dynamics NAV Team Blog

    Filtering on Dimension Values

    • 5 Comments

    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

Page 1 of 1 (1 items)