In all reports created in this chapter, we took data directly from a table and mapped table fields to report UI elements. But this is not always possible; complicated analysis reports often require aggregated data that cannot be simply extracted from a table, but requires preprocessing.

In the next example, we will create a report presenting a list of customers with quarterly payment amounts calculated for each customer. This information is not stored anywhere in the database; amounts have to be calculated and stored in a temporary table.

Create a report, 50502 Quarterly Payments. The following table lists the global variables required in the report. Declare these variables in C/AL Globals:

TempCustomer and TempCustomerPayment are temporary records; set Temporary to Yes in both variables' properties. The amount is an array. For this variable, access the properties and change the value of the Dimensions property to 4. Four elements of the array will be filled with payment amounts—one per quarter.

If you export the object after declaring all variables, the list of global variables should look like this:

VAR
  TempCustomer : TEMPORARY Record 18;
  TempCustomerPayment : TEMPORARY Record 50502;
  StartingDate : Date;
  EndingDate : Date;
  EntryNo : Integer;
  Amount : ARRAY [4] OF Decimal;

Create the report dataset structure as per this table:

The root data item in this dataset is a virtual table, Integer; it is not to be found anywhere in the database. It's simply a list of integer numbers presented in the form of a Record object to be used as an iterator. You can declare a variable of this type, apply filters, and find records as for a normal record. Here, we will use the Integer virtual table to iterate over the records in temporary tables. We need this separate iterator, because temporary tables cannot be used as data items in reports.

Write the following code in the report OnPreDataItem  trigger :

OnPreDataItem=BEGIN
  CollectCustomerPaymentsBuffer;
  Integer.SETRANGE(Number,1,TempCustomer.COUNT);
  IF TempCustomer.FINDSET THEN
    FindCustomerPayments(TempCustomer."No.");
END;

The first line of the code snippet calls the CollectCustomerPaymentsBuffer function (its source code follows) where the two temporary tables are filled with precalculated customer payments. In the next line, the Integer table is filtered to limit the number of iterations on the root data item. The number of iterations must exactly match the number of records in the TempCustomer buffer table. With this filter applied, code in the Integer - OnAfterGetRecord trigger will be executed as many times as defined by the COUNT function.

Now, let's move on to the OnAfterGetRecord trigger and add the following code:

OnAfterGetRecord=BEGIN
  IF Integer.Number > 1 THEN
    TempCustomer.NEXT;
  FindCustomerPayments(TempCustomer."No.");
END;

When we use a normal table as a report data item, we don't have to take care when reading the next record in a table; the NAV server does it for us. In the case of a temporary table, synchronization of the active record is the developer's responsibility. This trigger reads the next record from the TempCustomer table, then calls the FindCustomerPayments  function  to sync records in TempCustomerPayment with the active customer record.

Now, let's declare the main function that calculates aggregated amounts per quarter for each customer. The code of the function is in the next code block:

LOCAL PROCEDURE CollectCustomerPaymentsBuffer();
VAR
  Customer : Record 18;
  CustomerPayment : Record 50502;
  Date : Record 2000000007;
  PeriodNo : Integer;
BEGIN
  IF Customer.FINDSET THEN
    REPEAT
      CustomerPayment.SETRANGE("Customer No.",Customer."No.");
      CustomerPayment.SETRANGE("Payment Date",StartingDate,EndingDate);
      IF NOT CustomerPayment.ISEMPTY THEN BEGIN
        TempCustomer := Customer;
        TempCustomer.INSERT;

        PeriodNo := 0;
        Date.SETRANGE("Period Type",Date."Period Type"::Quarter);
        Date.SETRANGE("Period Start",StartingDate,EndingDate);
        Date.FINDSET;
        REPEAT
          PeriodNo += 1;
          EntryNo += 1;
          CustomerPayment.SETRANGE("Payment Date",Date."Period 
          Start",Date."Period End");
          CustomerPayment.CALCSUMS(Amount);
          TempCustomerPayment."Entry No." := EntryNo;
          TempCustomerPayment."Customer No." := Customer."No.";
          TempCustomerPayment."Payment Date" := Date."Period Start";
          TempCustomerPayment.Amount := CustomerPayment.Amount;
          TempCustomerPayment.INSERT;
        UNTIL Date.NEXT = 0;
      END;
    UNTIL Customer.NEXT = 0;
END;

Here, we use another virtual table: Date. Like the Integer table, the date is maintained by the system and isn't stored in the database. It contains date periods that can be grouped by applying filters on the table. We use it to find starting and ending dates of four quarters. First, we set a filter on the period type we want to find: Date.SETRANGE("Period Type",Date."Period Type"::Quarter). The next filter is set on the period starting date; we only want to find periods in the year requested by the user: Date.SETRANGE
("Period Start",StartingDate,EndingDate). And finally, Date.FINDSET selects all quarters in the filtered date period.

The next function, FindCustomerPaymentDates, retrieves aggregated amounts from the buffer table; this function is called from the OnAfterGetRecord trigger to synchronize customer records and related payment amounts:

LOCAL PROCEDURE FindCustomerPayments(CustomerNo : Code[20]);
VAR
  Date : Record 2000000007;
  I : Integer;
BEGIN
  Date.SETRANGE("Period Type",Date."Period Type"::Quarter);
  Date.SETRANGE("Period Start",StartingDate,EndingDate);
  Date.FINDSET;
  FOR I := 1 TO ARRAYLEN(Amount) DO BEGIN
    TempCustomerPayment.SETRANGE("Customer No.",CustomerNo);
    TempCustomerPayment.SETRANGE("Payment Date",Date."Period Start");
    IF TempCustomerPayment.FINDFIRST THEN
      Amount[I] := TempCustomerPayment.Amount;

    Date.NEXT;
  END;
END;

One more thing we need to do on the C/AL side is add the starting and ending dates of the reporting period to the report request page. The global variables StartingDate and EndingDate are declared for this purpose; now, let's initialize these variables and prompt the user to enter the required dates.

Create a function in the report object as per the following code block:

LOCAL PROCEDURE UpdateReportDates(PivotDate : Date);
BEGIN
  StartingDate := CALCDATE('<-CY>',PivotDate);
  EndingDate := CALCDATE('<CY>',PivotDate);
END;

In this function, we initialize starting and ending dates to the first and the last day of the year, respectively.

On opening the request page, we will initialize the dates to span the current year, enabling the user to change the date filter. Open the request page designer (View | Request Page), and from here, switch to the C/AL code editor. In the OnOpenPage trigger, add the following code:

OnOpenPage=BEGIN
  UpdateReportDates(WORKDATE);
END;

In the StartingDateControl trigger —OnValidate, add the following code:

OnValidate=BEGIN
  UpdateReportDates(StartingDate);
END;

Also, a similar line should be present in the EndingDateControl trigger—OnValidate, but with a slight change: the parameter passed to the function will be EndingDate, so the function call is UpdateReportDates(EndingDate).

With all the preparation completed, switch to the report layout designer and create the layout as shown in the screenshot:

Now, you can run the report and review the payments received from customers, aggregated by quarter.