How-to: Create a One-Page Matrix by Example #2

Using the step-by-step recipe, and basing on our first example, I have build another example One-Page for the following requirements:

Customer Spending by Period:

Display the total amount for each period (type, i.e. day, week, month, quarter, year, accounting period) what each of our customers has in order.

In this case the design of our matrix would be:

• matrix cells displaying the total amount
• horizontal dimension being the periods
• vertical dimension being our customers

For this matrix I Will be using the same extensions to the Customer and Sales Line tables as in our first example, but as we need to be able to calculate the amount in the matrix cells for specific periods we need some small modifications to the Sales Line table:

Key	SumIndexFields
Sell-to Customer No.,Type,No.,Shipment Date	Amount

Customer Spending by Period Matrix

This One-Page matrix consists of two pages:

• matrix main page: 62106 – Customer by Period Spending
• matrix sub page: 62108 – Cust. Spending by Period Matrix

As in the previous example the changes have been marked related to the steps as defined by my step-by-step recipe.

Note

In code below square brackets 6, 8 and i are displayed as and and [I].

PAG 62106

OBJECT Page 62106 Customer Spending by Period

{

  OBJECT-PROPERTIES

  {

    Date=26-04-12;

    Time=12:00:00;

    Version List=Advanced Matrix Box;

  }

  PROPERTIES

  {

    CaptionML=ENU=Customer Spending by Period;

    SaveValues=Yes;

    InsertAllowed=No;

    DeleteAllowed=No;

    ModifyAllowed=No;

    SourceTable=Table18;

    PageType=Card;

    OnOpenPage=BEGIN

                 //STEP_4-BEGIN

                 SetColumns(MATRIX_SetWanted::Initial);

                 //STEP_7-BEGIN

                 UpdateMatrixSubform

                 //STEP_7-END

                 //STEP_4-END

               END;

 

    ActionList=ACTIONS

    {

      { 1900000004;0 ;ActionContainer;

                      ActionContainerType=ActionItems }

      { 31      ;1   ;Action    ;

                      CaptionML=ENU=Previous Set;

                      ToolTipML=ENU=Previous Set;

                      Description=STEP_8;

                      Promoted=Yes;

                      PromotedIsBig=Yes;

                      Image=PreviousSet;

                      PromotedCategory=Process;

                      OnAction=BEGIN

                                 //STEP_8-BEGIN

                                 SetColumns(MATRIX_SetWanted::Previous);

                                 UpdateMatrixSubform

                                 //STEP_8-END

                               END;

                                }

      { 102     ;1   ;Action    ;

                      CaptionML=ENU=Previous Column;

                      ToolTipML=ENU=Previous;

                      Description=STEP_8;

                      Promoted=Yes;

                      PromotedIsBig=Yes;

                      Image=PreviousRecord;

                      PromotedCategory=Process;

                      OnAction=BEGIN

                                 //STEP_8-BEGIN

                                 SetColumns(MATRIX_SetWanted::PreviousColumn);

                                 UpdateMatrixSubform

                                 //STEP_8-END

                               END;

                                }

      { 103     ;1   ;Action    ;

                      CaptionML=ENU=Next Column;

                      ToolTipML=ENU=Next;

                      Description=STEP_8;

                      Promoted=Yes;

                      PromotedIsBig=Yes;

                      Image=NextRecord;

                      PromotedCategory=Process;

                      OnAction=BEGIN

                                 //STEP_8-BEGIN

                                 SetColumns(MATRIX_SetWanted::NextColumn);

                                 UpdateMatrixSubform

                                 //STEP_8-END

                               END;

                                }

      { 32      ;1   ;Action    ;

                      CaptionML=ENU=Next Set;

                      ToolTipML=ENU=Next Set;

                      Description=STEP_8;

                      Promoted=Yes;

                      PromotedIsBig=Yes;

                      Image=NextSet;

                      PromotedCategory=Process;

                      OnAction=BEGIN

                                 //STEP_8-BEGIN

                                 SetColumns(MATRIX_SetWanted::Next);

                                 UpdateMatrixSubform

                                 //STEP_8-END

                               END;

                                }

    }

  }

  CONTROLS

  {

    { 1900000001;0;Container;

                ContainerType=ContentArea }

 

    { 1   ;1   ;Group     ;

                CaptionML=ENU=Matrix Options }

 

    { 6   ;2   ;Field     ;

                CaptionML=ENU=View by;

                OptionCaptionML=ENU=Day,Week,Month,Quarter,Year,Accounting Period;

                Description=STEP_9;

                SourceExpr=PeriodType;

                OnValidate=BEGIN

                             //STEP_9-BEGIN

                             SetColumns(MATRIX_SetWanted::Initial);

                             UpdateMatrixSubform;

                             //STEP_9-END

                           END;

                            }

 

    { 9   ;2   ;Field     ;

                CaptionML=ENU=View as;

                OptionCaptionML=ENU=Net Change,Balance at Date;

                Description=STEP_9;

                SourceExpr=QtyType;

                OnValidate=BEGIN

                             //STEP_9-BEGIN

                             UpdateMatrixSubform;

                             //STEP_9-END

                           END;

                            }

 

    { 33  ;1   ;Part      ;

                Name=MatrixForm;

                Description=STEP_2;

                PagePartID=Page62108 }

 

  }

  CODE

  {

    VAR

      MatrixRecord@1004 : Record 2000000007;

      MatrixRecords@1000 : ARRAY [12] OF Record 2000000007;

      PeriodType@1001 : ‘Day,Week,Month,Quarter,Year,Accounting Period’;

      QtyType@1002 : ‘Net Change,Balance at Date’;

      MATRIX_ColumnCaptions@1010 : ARRAY [12] OF Text[1024];

      MATRIX_ColumnSet@1007 : Text[1024];

      MATRIX_SetWanted@1005 : ‘Initial,Previous,Same,Next,PreviousColumn,NextColumn’;

      MATRIX_PKFirstRecInCurrSet@1006 : Text[100];

      MATRIX_CurrSetLength@1008 : Integer;

      WshShell@1100409000 : Automation “{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{72C24DD5-D70A-438B-8A42-98424B88AFB8}:’Windows Script Host Object Model’.WshShell”;

 

    PROCEDURE SetColumns@11(SetWanted@1001 : ‘Initial,Previous,Same,Next,PreviousColumn,NextColumn’);

    VAR

      MatrixMgt@1000 : Codeunit 9200;

      DateFilter@1002 : Text[30];

    BEGIN

      //STEP_4-BEGIN

      MatrixMgt.GeneratePeriodMatrixData(SetWanted,12,FALSE,PeriodType,”,

        MATRIX_PKFirstRecInCurrSet,MATRIX_ColumnCaptions,MATRIX_ColumnSet,MATRIX_CurrSetLength,MatrixRecords);

      //STEP_4-END

    END;

 

    PROCEDURE UpdateMatrixSubform@7();

    BEGIN

      //STEP_7-BEGIN

      CurrPage.MatrixForm.FORM.Load(PeriodType,QtyType,MATRIX_ColumnCaptions,MatrixRecords,MATRIX_CurrSetLength);

      //STEP_9-BEGIN

      UpdateThePage

      //STEP_9-END

      //STEP_7-END

    END;

 

    PROCEDURE UpdateThePage@10();

    BEGIN

      //STEP_9-BEGIN

      IF ISCLEAR(WshShell) THEN

        CREATE(WshShell, TRUE, TRUE);

      WshShell.SendKeys(‘{F5}’);

      //STEP_9-END

    END;

 

    BEGIN

    {

      <changelog>

        <add id=”STEP_2“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 2 – One-Page Matrix Step-by-Step</add>

        <change id=”STEP_4“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 4 – One-Page Matrix Step-by-Step</change>

        <change id=”STEP_7“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 7 – One-Page Matrix Step-by-Step</change>

        <change id=”STEP_8“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 8 – One-Page Matrix Step-by-Step</change>

        <change id=”STEP_9“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 9 – One-Page Matrix Step-by-Step</change>

      </changelog>

    }

    END.

  }

}

  

PAG 62108

OBJECT Page 62108 Cust Spending by Period Matrix

{

  OBJECT-PROPERTIES

  {

    Date=26-04-12;

    Time=12:00:00;

    Version List=Advanced Matrix Box;

  }

  PROPERTIES

  {

    Editable=No;

    CaptionML=ENU=Customer Spending by Period Matrix;

    SourceTable=Table18;

    PageType=ListPart;

    OnAfterGetRecord=VAR

                       MATRIX_CurrentColumnOrdinal@1043 : Integer;

                       MATRIX_Steps@1044 : Integer;

                     BEGIN

                       //STEP_6-BEGIN

                       MATRIX_CurrentColumnOrdinal := 0;

                       WHILE MATRIX_CurrentColumnOrdinal < MATRIX_NoOfMatrixColumns DO BEGIN

                         MATRIX_CurrentColumnOrdinal := MATRIX_CurrentColumnOrdinal + 1;

                         MATRIX_OnAfterGetRecord(MATRIX_CurrentColumnOrdinal);

                       END;

                       //STEP_6-END

                     END;

 

  }

  CONTROLS

  {

    { 1100409000;0;Container;

                ContainerType=ContentArea }

 

    { 1   ;1   ;Group     ;

                GroupType=Repeater;

                FreezeColumnID=Name }

 

    { 6   ;2   ;Field     ;

                Description=STEP_1;

                SourceExpr=”No.” }

 

    { 4   ;2   ;Field     ;

                Description=STEP_1;

                SourceExpr=Name }

 

    { 1011;2   ;Field     ;

                Name=Field1;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[1];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[1];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(1);

                            END;

                             }

 

    { 1012;2   ;Field     ;

                Name=Field2;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[2];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[2];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(2);

                            END;

                             }

 

    { 1013;2   ;Field     ;

                Name=Field3;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[3];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[3];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(3);

                            END;

                             }

 

    { 1014;2   ;Field     ;

                Name=Field4;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[4];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[4];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(4);

                            END;

                             }

 

    { 1015;2   ;Field     ;

                Name=Field5;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[5];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[5];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(5);

                            END;

                             }

 

    { 1016;2   ;Field     ;

                Name=Field6;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[6];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[6];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(6);

                            END;

                             }

 

    { 1017;2   ;Field     ;

                Name=Field7;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[7];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[7];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(7);

                            END;

                             }

 

    { 1018;2   ;Field     ;

                Name=Field8;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[8];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[8];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(8);

                            END;

                             }

 

    { 1019;2   ;Field     ;

                Name=Field9;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[9];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[9];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(9);

                            END;

                             }

 

    { 1020;2   ;Field     ;

                Name=Field10;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[10];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[10];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(10);

                            END;

                             }

 

    { 1021;2   ;Field     ;

                Name=Field11;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[11];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[11];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(11);

                            END;

                             }

 

    { 1022;2   ;Field     ;

                Name=Field12;

                BlankNumbers=BlankZero;

                Description=STEP_3|STEP_9;

                SourceExpr=MATRIX_CellData[12];

                AutoFormatType=1;

                CaptionClass=’3,’ + MATRIX_ColumnCaption[12];

                OnDrillDown=BEGIN

                              MatrixOnDrillDown(12);

                            END;

                             }

 

  }

  CODE

  {

    VAR

      SalesLine@1100409000 : Record 37;

      PeriodType@1080 : ‘Day,Week,Month,Quarter,Year,Accounting Period’;

      QtyType@1081 : ‘Net Change,Balance at Date’;

      MatrixRecords@1000 : ARRAY [12] OF Record 2000000007;

      MATRIX_NoOfMatrixColumns@1084 : Integer;

      MATRIX_CellData@1085 : ARRAY [12] OF Decimal;

      MATRIX_ColumnCaption@1001 : ARRAY [12] OF Text[1024];

 

    LOCAL PROCEDURE SetDateFilter@1086(ColumnID@1000 : Integer);

    BEGIN

      //STEP_6-BEGIN

      IF QtyType = QtyType::”Net Change” THEN

        IF MatrixRecords[ColumnID].”Period Start” = MatrixRecords[ColumnID].”Period End” THEN

          SETRANGE(“Date Filter”,MatrixRecords[ColumnID].”Period Start”)

        ELSE

          SETRANGE(“Date Filter”,MatrixRecords[ColumnID].”Period Start”,MatrixRecords[ColumnID].”Period End”)

      ELSE

        SETRANGE(“Date Filter”,0D,MatrixRecords[ColumnID].”Period End”);

      //STEP_6-END

    END;

 

    LOCAL PROCEDURE MATRIX_OnAfterGetRecord@1091(ColumnID@1000 : Integer);

    BEGIN

      //STEP_6-BEGIN

      SetDateFilter(ColumnID);

      CALCFIELDS(“Total Amount”);

      IF “Total Amount” <> 0 THEN

        MATRIX_CellData[ColumnID] := “Total Amount”

      ELSE

        MATRIX_CellData[ColumnID] := 0;

      //STEP_6-END

    END;

 

    PROCEDURE Load@3(PeriodType1@1003 : ‘Day,Week,Month,Quarter,Year,Accounting Period’;QtyType1@1000 : ‘Net Change,Balance at Date’;MatrixColumns1@1001 : ARRAY [12] OF Text[1024];VAR MatrixRecords1@1002 : ARRAY [12] OF Record 2000000007;NoOfMatrixColumns1@1004 : Integer);

    VAR

      i@1005 : Integer;

    BEGIN

      //STEP_5-BEGIN

      PeriodType := PeriodType1;

      QtyType := QtyType1;

      COPYARRAY(MATRIX_ColumnCaption,MatrixColumns1,1);

      FOR i := 1 TO ARRAYLEN (MatrixRecords) DO

        MatrixRecords[i].COPY(MatrixRecords1[i]);

      MATRIX_NoOfMatrixColumns := NoOfMatrixColumns1;

      //STEP_5-END

    END;

 

    PROCEDURE MatrixOnDrillDown@4(ColumnID@1000 : Integer);

    BEGIN

      //STEP_9-BEGIN

      SetDateFilter(ColumnID);

      SalesLine.SETCURRENTKEY(“Sell-to Customer No.”,Type,”No.”,”Shipment Date”);

      SalesLine.SETRANGE(“Sell-to Customer No.”,”No.”);

      SalesLine.SETFILTER(“Shipment Date”,GETFILTER(“Date Filter”));

      FORM.RUN(0,SalesLine);

      //STEP_9-END

    END;

 

    BEGIN

    {

      <changelog>

        <add id=”STEP_1“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 1 – One-Page Matrix Step-by-Step</add>

        <change id=”STEP_3“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 3 – One-Page Matrix Step-by-Step</change>

        <change id=”STEP_5“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 5 – One-Page Matrix Step-by-Step</change>

        <change id=”STEP_6“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 6 – One-Page Matrix Step-by-Step</change>

        <change id=”STEP_9“

          dev=”lvanvugt”

          date=”2012-04-26″

          source=”http://dynamicsuser.net/blogs/vanvugt/archive/2012/04/20/how-to-create-a-one-page-matrix.aspx”

          releaseversion=”Advanced Matrix Box”>Step 9 – One-Page Matrix Step-by-Step</change>

      </changelog>

    }

    END.

  }

}