Cash Flow Chart Example

This code example demonstrates how to use the Business Data Chart control add-in to create a chart that displays a cash flow forecast as shown in the following figure:

The cash flow forecast chart combines the current bank balances with the outstanding receivables and payables, and displays the data for the next six days, including the current day. The chart displays data for four measures: Accounts Receivable, Accounts Payable, Forecasted Balance, and Credit Limit in Banks. The data for the measures is displayed using the following chart types: stacked column, line, and step line.

The chart is interactive. When you choose a data point, a list appears that shows overdue customer ledger entries, vendor ledger entries, or bank accounts.

Cash Flow Chart Design

The chart is designed around enhanced performance, testability, and code reuse. The chart design separates the business logic and data handling from the chart rendition on the page. This improves performance because you can run the business logic on the computer running the Microsoft Dynamics NAV Server instead of the Microsoft Dynamics NAV Windows client and limit the data calls between the client and server. The cash flow chart uses Microsoft .NET Interoperability to call Microsoft .NET Framework types from Microsoft Dynamics NAV objects to build the chart and populate it with data.

The following figure illustrates the Microsoft Dynamics NAV objects that are used to create the cash flow chart.

Page 50000 Demo Cash Flow Chart contains the Business Data Chart control add-in that renders the chart user-interface on the page. The Business Data control add-in is defined by the Microsoft.Dynamics.Nav.Client.BusinessChart assembly that is available by default in the Microsoft Dynamics NAV installation.

Table 485 Business Chart Buffer temporarily holds the data for building the cash flow chart, such as the chart type, measures, and labels. The data is retrieved by page 50000 and passed to the Business Data Chart control add-in. Table 485 is a default table that is available with the Microsoft Dynamics NAV demo database. The table uses Microsoft .NET Framework interoperability to reference types in the System.Data assembly for building a data table that contains the chart data. You can use this table with other charts that are built on the Business Data control add-in.

Codeunit 50000 Demo Cash Flow Chart Mgt. contains the business logic for the cash flow chart and generates the data that is displayed in the chart.

Creating the Page for Displaying the Chart

The following code creates a CardPart type page that includes the Business Data Chart control add-in and the C/AL for displaying data. To create the page, copy the code to a text file, and then, in the Microsoft Dynamics NAV development environment, import the text file. After you create the page, you can use it in the Microsoft Dynamics NAV Windows client. For example, you can add it in a part on the Role Center page.

OBJECT Page 50000 Demo Cash Flow Chart
{
  OBJECT-PROPERTIES
  {
    Date=18-04-12;
    Time=15:57:04;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  CONTROLS
  {
    { 1   ;    ;Container ;
                Name=Content;
                ContainerType=ContentArea }
    { 2   ;1   ;Field     ;
                Name=Chart;
                
ControlAddIn=[Microsoft.Dynamics.Nav.Client.BusinessChart;PublicKeyToken=31bf3856ad364e35] }
  }
  CODE
  {
    VAR
      BusinessChartBuffer@1000 : Record 485;
      DemoCashFlowChartMgt@1001 : Codeunit 50000;
    LOCAL PROCEDURE UpdateChart@2();
    BEGIN
      DemoCashFlowChartMgt.GenerateData(BusinessChartBuffer);
      BusinessChartBuffer.Update(CurrPage.Chart);
    END;
    EVENT Chart@-2::DataPointClicked@12(point@1000 : DotNet "'Microsoft.Dynamics.Nav.Client.BusinessChart.Model, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.Client.BusinessChart.BusinessChartDataPoint");
    BEGIN
      BusinessChartBuffer.SetDrillDownIndexes(point);
      DemoCashFlowChartMgt.OnDataPointClicked(BusinessChartBuffer);
    END;
    EVENT Chart@-2::DataPointDoubleClicked@13(point@1000 : DotNet "'Microsoft.Dynamics.Nav.Client.BusinessChart.Model, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.Client.BusinessChart.BusinessChartDataPoint"); 
    BEGIN
    END;
    EVENT Chart@-2::AddInReady@14();
    BEGIN
      UpdateChart;
    END;
    BEGIN
    END.
  }
}

Understanding the Page Code

The following list explains some of the page’s C/AL code:

• The page includes a single field that has its ControlAddin Property set to the Microsoft.Dynamics.Nav.Client.BusinessChart assembly, which defines the Business Data Chart control add-in.

• The Update function populates the chart with data from the Business Chart Buffer table. The Chart – AddinReady trigger calls the Update function when the Business Chart control add-in is loaded and ready on the page. Depending on your scenario, you could also call the Update function on the OnAfterGetRecord trigger, OnFind trigger, or on an action.

• The business logic has been isolated in codeunit 50000 Demo Cash Flow Chart Mgt. The page includes the Business Chart Buffer record variable that references table 485 Business Chart Buffer. The Business Chart Buffer record variable holds the chart data and updates the control add-in when it is passed into the function Update. Note that we pass on CurrPage.Chart, for example, the chart add-in to the function. The Microsoft.Dynamics.Nav.Client.BusinessChartData object is defined by a DotNet variable. The DotNet variable for the BusinessChartData object is configured to instantiate on the computer running the Microsoft Dynamics NAV Server. This populates the object with data and properties without having the server calling the client. After the chart data has been generated, it is delivered to the Business Data chart control add-in in a single call. The data that is transferred between the Microsoft Dynamics NAV Server and the Microsoft Dynamics NAV Windows client is automatically serialized. For more information, see Serializing .NET Framework Types.

• The OnDataPointClicked trigger is called when you choose a data point or column in the cash flow chart. Depending on the data point or column that you choose, a list page appears that shows overdue customer ledger entries, vendor ledger entries or bank accounts. To improve code testability, the drill-down logic for the OnDataPointClicked trigger is called from codeunit 50000 Demo Cash Flow Chart Mgt. This construction allows you to test the data generation and drill-down from code by calling functions on the codeunit.

Creating the Codeunit for Handling Data

The following code creates codeunit 50000 Demo Cash Flow Chart Mgt. To create the codeunit, copy the code to a text file, and then, in the development environment, import the text file.

OBJECT Codeunit 50000 Demo Cash Flow Chart Mgt.
{
  OBJECT-PROPERTIES
  {
    Date=18-04-12;
    Time=16:11:09;
    Modified=Yes;
    Version List=Charts;
  }
  PROPERTIES
  {
    OnRun=BEGIN
          END;
  }
  CODE
  {
    VAR
      TextCust@1000 : TextConst 'ENU=Accounts Receivable';
      TextVend@1001 : TextConst 'ENU=Accounts Payable';
      TextBank@1002 : TextConst 'ENU=Bank Balances';
      TextTotal@1003 : TextConst 'ENU=Forecasted Balance';
      TextCredit@1004 : TextConst 'ENU=Credit Limit in Banks';
      TextDate@1005 : TextConst 'ENU=Date';
    PROCEDURE OnInitPage@6(VAR BusChartBuf@1000 : Record 485);
    BEGIN
    END;
    PROCEDURE GenerateData@3(VAR BusChartBuf@1000 : Record 485);
    VAR
      i@1007 : Integer;
      BankBalance@1006 : Decimal;
      CustNetChange@1005 : Decimal;
      VendNetChange@1004 : Decimal;
      TotalBalance@1003 : Decimal;
      BalanceDate@1002 : Date;
      BankCreditLimit@1001 : Decimal;
    BEGIN
      WITH BusChartBuf DO BEGIN
        Initialize;
        AddMeasure(TextCust,1,"Data Type"::Decimal,"Chart Type"::StackedColumn);
        AddMeasure(TextVend,2,"Data Type"::Decimal,"Chart Type"::StackedColumn);
        AddMeasure(TextTotal,3,"Data Type"::Decimal,"Chart Type"::Line);
        AddMeasure(TextCredit,4,"Data Type"::Decimal,"Chart Type"::StepLine);
        SetXAxis(TextDate,"Data Type"::DateTime);
        BalanceDate := WORKDATE - 1; // demo. Should be TODAY.
        CalcBankBalance(BalanceDate,TotalBalance,BankCreditLimit);
        FOR i := 1 TO 6  DO BEGIN  //  Generate 6 columns
          CustNetChange := CalcCustNetChange(BalanceDate,BalanceDate);
          VendNetChange := CalcVendNetChange(BalanceDate,BalanceDate);
          TotalBalance := TotalBalance + CustNetChange + VendNetChange;
          AddPeriodColumn(BalanceDate);  // X-Axis value
          SetValueByIndex(1 - 1,i - 1,CustNetChange); // zero indexed.
          SetValueByIndex(2 - 1,i - 1,VendNetChange);
          SetValueByIndex(3 - 1,i - 1,TotalBalance);
          SetValueByIndex(4 - 1,i - 1,BankCreditLimit);
          BalanceDate := BalanceDate + 1;
        END;
      END;
    END;
    PROCEDURE OnDataPointClicked@5(VAR BusChartBuf@1000 : Record 485);
    VAR
      VendLedgEntry@1001 : Record 25;
      DrillDownDate@1000000001 : Date;
    BEGIN
      DrillDownDate := WORKDATE + BusChartBuf."Drill-Down X Index" + 1; // ref. first balance date in GenerateData function
      CASE BusChartBuf."Drill-Down Measure Index" + 1 OF
        1 : // Customer
          DrillDownCust(DrillDownDate);
        2 : // Vendor
          DrillDownVend(DrillDownDate);
        4 : // Bank Credit limits
          DrillDownBank;
      END;
    END;
    LOCAL PROCEDURE CalcCustNetChange@1(FromDate@1000 : Date;ToDate@1001 : Date) : Decimal;
    VAR
      CustLedgEntry@1002 : Record 21;
      TotalRemainingAmount@1003 : Decimal;
    BEGIN
      WITH CustLedgEntry DO BEGIN
        SETCURRENTKEY(Open,"Due Date");
        SETRANGE(Open,TRUE);
        SETRANGE("Due Date",FromDate,ToDate);
        IF FIND('-') THEN
          REPEAT
            CALCFIELDS("Remaining Amt. (LCY)");
            TotalRemainingAmount := TotalRemainingAmount + "Remaining Amt. (LCY)";
          UNTIL NEXT = 0;
      END;
      EXIT(TotalRemainingAmount);
    END;
    LOCAL PROCEDURE CalcVendNetChange@4(FromDate@1000 : Date;ToDate@1001 : Date) : Decimal;
    VAR
      VendLedgEntry@1002 : Record 25;
      TotalRemainingAmount@1003 : Decimal;
    BEGIN
      WITH VendLedgEntry DO BEGIN
        SETCURRENTKEY(Open,"Due Date");
        SETRANGE(Open,TRUE);
        SETRANGE("Due Date",FromDate,ToDate);
        IF FIND('-') THEN
          REPEAT
            CALCFIELDS("Remaining Amt. (LCY)");
            TotalRemainingAmount := TotalRemainingAmount + "Remaining Amt. (LCY)";
          UNTIL NEXT = 0;
      END;
      EXIT(TotalRemainingAmount);
    END;
    LOCAL PROCEDURE CalcBankBalance@7(EndDate@1000 : Date;VAR TotalBalance@1003 : Decimal;VAR BankCreditLimit@1002 : Decimal);
    VAR
      BankAcc@1001 : Record 270;
    BEGIN
      WITH BankAcc DO BEGIN
        SETFILTER("Date Filter",'..%1',EndDate);
        IF FIND('-') THEN
          REPEAT
            CALCFIELDS("Balance at Date (LCY)");
            TotalBalance := TotalBalance + "Balance at Date (LCY)";
            BankCreditLimit := BankCreditLimit + BankAcc."Min. Balance";
          UNTIL NEXT = 0;
      END;
    END;
    LOCAL PROCEDURE DrillDownCust@1000000002(DrillDownDate@1000000000 : Date);
    VAR
      CustLedgEntry@1000000001 : Record 21;
    BEGIN
      CustLedgEntry.SETRANGE(Open,TRUE);
      CustLedgEntry.SETRANGE("Due Date",DrillDownDate,DrillDownDate);
      PAGE.RUNMODAL(PAGE::"Customer Ledger Entries",CustLedgEntry);
    END;
    LOCAL PROCEDURE DrillDownVend@1000000004(DrillDownDate@1000000000 : Date);
    VAR
      VendLedgEntry@1000000001 : Record 25;
    BEGIN
      VendLedgEntry.SETRANGE(Open,TRUE);
      VendLedgEntry.SETRANGE("Due Date",DrillDownDate,DrillDownDate);
      PAGE.RUNMODAL(PAGE::"Vendor Ledger Entries",VendLedgEntry);
    END;
    LOCAL PROCEDURE DrillDownBank@1000000005();
    BEGIN
      PAGE.RUNMODAL(PAGE::"Bank Account List");
    END;
    BEGIN
    END.
  }
}

Understanding the GenerateData Function

Codeunit 50000 accesses data in the Cust. Ledger Entry table, the Vender Ledger Entry table, and the Bank Account table. The GenerateData function generates the data for the cash flow chart. The following figure shows the C/AL code for the GenerateData function and how it maps to the cash flow chart user interface.

The C/AL code sets the characteristics of the cash flow chart, including measure labels, chart types, and more. The functions that are called in GenerateData function are defined in table 485 Business Chart Buffer. The following table describes the functions of the Business Chart Buffer table that you can use for defining the chart.

Understanding the Drill-down Logic of the OnDataPointClicked Function

When you choose a data point or column in the cash flow chart, a list page appears that shows due customer ledger entries, vendor ledger entries, or bank accounts. To accomplish this, the OnDataPointClicked event trigger on page 50000 Demo Cash Flow Chart is passed to the point object as a parameter. The point can then be passed on to the SetDrillDownIndexes(point) function that updates the Drill-Down X Index and Drill-Down Measure Index fields on the record. You use the Drill-Down X Index and Drill-Down Measure Index fields to retrieve the x-axis value and measure value for filtering the list that you want to display. The following figure shows the C/AL code on the OnDataPointClicked function.

The OnDataPointClicked function calls the DrillDownCust function, DrillDownVend function, and DrillDownBank function, which are shown in the following figure.

See Also

Concepts

Displaying Charts Using the Chart Control Add-in