Docs  /  Excel Report Builder  /  Information for Developers

 Temporary Data Codeunits

2025/03/26 • 8 min. to read

In this topic

• Structure
• Example 1
• Add to Data Sheet
• Results and behavior of temporary data codeunits
• Example 2
• Related information

Temporary Data CodeunitWith the help of temporary data codeunits, evaluations can be extended with functions that temporarily calculate certain data of the evaluation during execution. This makes it possible, for example, to evaluate data that is normally not available or not available in this form. Temporary Data Codeunits can be provided or added via custom programming. These codeunits can then be added to a Datasheet via the Add, More Options... action. Edit Evaluations, Data Sheets, Data Sheet

Structure

Temporary data codeunits consist of the codeunit for the calculation and a table of TableType = Temporary for the result. In order for the Excel Report Builder to recognize the codeunit as a temporary data codeunit, it must be integrated via an EventSubscriber. The EventSubscriber can also be used to specify help texts, which are later displayed in an evaluation when the codeunit is selected.

Example 1

A total is to be calculated for all countries within the filter. It should be possible to specify the country/region code as a filter. As a result, the total of Sales (LCY) and the number of posted invoices should be returned as a single record.

Implementation

The table for the result is structured as follows:

table 50000 "NVX Country Statistics"
{
    DataClassification = SystemMetadata;
    Caption = 'NVX Country Statistics';
    TableType = Temporary;

    fields
    {
        field(1; "Country/Region Filter"; Code[10])
        {
            DataClassification = SystemMetadata;
            Caption = 'Country/Region Filter';
            TableRelation = "Country/Region".Code;
        }
        field(10; "Sales (LCY)"; Decimal)
        {
            DataClassification = SystemMetadata;
            Caption = 'Sales (LCY)';
        }
        field(11; "No. of Pstd. Invoices"; Integer)
        {
            DataClassification = SystemMetadata;
            Caption = 'No. of Pstd. Invoices';
        }
    }

    keys
    {
        key(Key1; "Country/Region Filter")
        {
        }
    }
}

The codeunit for the calculation is structured as follows:

codeunit 50000 "NVX Country Statistics"
{
    TableNo = "NVX Country Statistics";

    trigger OnRun()
    var
        NVXCountryStatistics: Record "NVX Country Statistics";
        Customer: Record Customer;
    begin
        if not Rec.IsTemporary() then
            exit;

        //In this example, the total is determined via the customers.
        //Any country/region filters that have been set must therefore be transferred from the temporary table to the customers.
        //The filter on the temporary table is then removed in this example, as the field is only used for filtering the customers.
        Customer.SetCurrentKey("Country/Region Code");
        Rec.FilterGroup(0);
        Customer.FilterGroup(0);
        Rec.CopyFilter("Country/Region Filter", Customer."Country/Region Code");
        Rec.SetRange("Country/Region Filter");
        Rec.FilterGroup(2);
        Customer.FilterGroup(2);
        Rec.CopyFilter("Country/Region Filter", Customer."Country/Region Code");
        Rec.SetRange("Country/Region Filter");
        //set relation filter
        //The Excel Report Builder transfers filters from relations via filter group 11
        Rec.FilterGroup(11);
        Customer.FilterGroup(11);
        Rec.CopyFilter("Country/Region Filter", Customer."Country/Region Code");
        Rec.SetRange("Country/Region Filter");
        Rec.FilterGroup(0);
        Customer.FilterGroup(0);

        NVXCountryStatistics.Copy(Rec);
        
        Clear(Rec);
        Rec."Country/Region Filter" := '';
        Rec.Insert();
        if Customer.FindSet() then
            repeat
                Customer.CalcFields("Sales (LCY)", "No. of Pstd. Invoices");
                Rec."Sales (LCY)" += Customer."Sales (LCY)";
                Rec."No. of Pstd. Invoices" += Customer."No. of Pstd. Invoices";
                Rec.Modify();
            until Customer.Next() = 0;

        Rec.Copy(NVXCountryStatistics);
    end;

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"NCE Temp. Data Codeunit Mgt.", 'OnAddAllowedTempDataCodeunits', '', false, false)]
    local procedure NCETempDataCodeunitMgt_OnAddAllowedTempDataCodeunits(var TempNCETempDataCodeunit: Record "NCE Temp. Data Codeunit" temporary)
    var
        NVXCountryStatistics: Record "NVX Country Statistics";
        CountryRegion: Record "Country/Region";
        RecField: Record Field;
        CodeunitID: Integer;
        TableID: Integer;
        TableSettingsName: Text;
        HelpTxt: Label 'This Codeunit calculates the total for all countries within the filter.';
        FiltersAndRelationsHelpTxt: Label '\\Filters and Relations:\The filter in the ''%1'' field determines which Countries/Regions should be included in the evaluation.';
        ResultsHelpTxt: Label '\\Results:\''%1'', ''%2''';
    begin
        CodeunitID := Codeunit::"NVX Country Statistics";
        TableID := Database::"NVX Country Statistics";
        TableSettingsName := NVXCountryStatistics.TableCaption();

        TempNCETempDataCodeunit.Add(CodeunitID, TableID, TableSettingsName,
            HelpTxt +
            StrSubstNo(FiltersAndRelationsHelpTxt, NVXCountryStatistics.FieldCaption("Country/Region Filter")) +
            StrSubstNo(ResultsHelpTxt, NVXCountryStatistics.FieldCaption("Sales (LCY)"), NVXCountryStatistics.FieldCaption("No. of Pstd. Invoices")));

        TempNCETempDataCodeunit.AddTableFilter(CodeunitID, NVXCountryStatistics.FieldNo("Country/Region Filter"));
        TempNCETempDataCodeunit.AddTableFilter(CodeunitID, Database::"Country/Region", NVXCountryStatistics.FieldNo("Country/Region Filter"), '', CountryRegion.FieldNo(Code));

        Clear(RecField);
        RecField.SetRange(TableNo, TableID);
        RecField.SetFilter("No.", '<%1', 2000000000);
        RecField.SetRange(Enabled, true);
        if RecField.FindSet() then
            repeat
                if (RecField."No." <> NVXCountryStatistics.FieldNo("Country/Region Filter")) then
                    TempNCETempDataCodeunit.AddDataSheetField(CodeunitID, RecField."No.");
            until RecField.Next() = 0;
    end;
}

• TempNCETempDataCodeunit.Add is used to integrate the codeunit into the Excel Report Builder. Further information about the purpose and use of the current code unit is also provided here.
• TempNCETempDataCodeunit.AddTableFilter is used to specify the filters and possible relations that are to be proposed later when adding to the data sheet.
• TempNCETempDataCodeunit.AddDataSheetField is used to specify the fields that are to be proposed later when adding to the data sheet.

Add to Data Sheet

The codeunit can be added to a data sheet via the Add, More Options... action after it has been published. Fields and relations that were specified via AddDataSheetField and AddTableFilter are automatically suggested: The following result is displayed in Excel after executing the evaluation: The temporary data code unit can also be subordinated to another table: The following result is displayed in Excel after executing the evaluation:

Results and behavior of temporary data codeunits

Depending on how the codeunit is programmed, the results also depend on how and where it is later added to a data sheet. Example 1 only ever determines the total Sales (LCY) of certain countries and returns as a single record.

• If the codeunit is called without a filter, it determines the total of Sales (LCY).
• If the codeunit is called with filter, it determines the total of Sales (LCY) of all countries within the filter.
• If the codeunit is subordinated to the Country/Region table, it determines the value of Sales (LCY) for the country.

However, a different behavior would also be possible, which Example 2 illustrates. Here for a call without/with filter, the value of Sales (LCY) is determined for each individual country within the filter and several records are returned accordingly.

Example 2

A total is to be calculated for each country within the filter. It should be possible to specify the country/region code as a filter. As a result, the total of Sales (LCY) and the number of posted invoices should be returned as a single record for each country/region code.

Implementation

The table for the result is structured as in example 1. The codeunit for the calculation is structured similarly to example 1. All changes to example 1 are highlighted:

codeunit 50001 "NVX Country Statistics 2"
{
    TableNo = "NVX Country Statistics";

    trigger OnRun()
    var
        NVXCountryStatistics: Record "NVX Country Statistics";
        Customer: Record Customer;
    begin
        if not Rec.IsTemporary() then
            exit;

        //In this example, the total is determined via the customers.
        //Any country/region filters that have been set must therefore be transferred from the temporary table to the customers.
        //The filter on the temporary table is then removed in this example, as the field is only used for filtering the customers.
        Customer.SetCurrentKey("Country/Region Code");
        Rec.FilterGroup(0);
        Customer.FilterGroup(0);
        Rec.CopyFilter("Country/Region Filter", Customer."Country/Region Code");
        Rec.SetRange("Country/Region Filter");
        Rec.FilterGroup(2);
        Customer.FilterGroup(2);
        Rec.CopyFilter("Country/Region Filter", Customer."Country/Region Code");
        Rec.SetRange("Country/Region Filter");
        //set relation filter
        //The Excel Report Builder transfers filters from relations via filter group 11
        Rec.FilterGroup(11);
        Customer.FilterGroup(11);
        Rec.CopyFilter("Country/Region Filter", Customer."Country/Region Code");
        Rec.SetRange("Country/Region Filter");
        Rec.FilterGroup(0);
        Customer.FilterGroup(0);

        NVXCountryStatistics.Copy(Rec);

        Clear(Rec);
        if Customer.FindSet() then
            repeat
                if not Rec.Get(Customer."Country/Region Code") then begin
                    Clear(Rec);
                    Rec."Country/Region Filter" := Customer."Country/Region Code";
                    Rec.Insert();
                end;
                Customer.CalcFields("Sales (LCY)", "No. of Pstd. Invoices");
                Rec."Sales (LCY)" += Customer."Sales (LCY)";
                Rec."No. of Pstd. Invoices" += Customer."No. of Pstd. Invoices";
                Rec.Modify();
            until Customer.Next() = 0;

        Rec.Copy(NVXCountryStatistics);
    end;

    [EventSubscriber(ObjectType::Codeunit, Codeunit::"NCE Temp. Data Codeunit Mgt.", 'OnAddAllowedTempDataCodeunits', '', false, false)]
    local procedure NCETempDataCodeunitMgt_OnAddAllowedTempDataCodeunits(var TempNCETempDataCodeunit: Record "NCE Temp. Data Codeunit" temporary)
    var
        NVXCountryStatistics: Record "NVX Country Statistics";
        CountryRegion: Record "Country/Region";
        RecField: Record Field;
        CodeunitID: Integer;
        TableID: Integer;
        TableSettingsName: Text;
        HelpTxt: Label 'This Codeunit calculates the total for each country within the filter.';
        FiltersAndRelationsHelpTxt: Label '\\Filters and Relations:\The filter in the ''%1'' field determines which Countries/Regions should be included in the evaluation.';
        ResultsHelpTxt: Label '\\Results:\''%1'', ''%2'', ''%3''';
    begin
        CodeunitID := Codeunit::"NVX Country Statistics 2";
        TableID := Database::"NVX Country Statistics";
        TableSettingsName := NVXCountryStatistics.TableCaption();

        TempNCETempDataCodeunit.Add(CodeunitID, TableID, TableSettingsName,
            HelpTxt +
            StrSubstNo(FiltersAndRelationsHelpTxt, NVXCountryStatistics.FieldCaption("Country/Region Filter")) +
            StrSubstNo(ResultsHelpTxt, NVXCountryStatistics.FieldCaption("Country/Region Filter"), NVXCountryStatistics.FieldCaption("Sales (LCY)"), NVXCountryStatistics.FieldCaption("No. of Pstd. Invoices")));

        TempNCETempDataCodeunit.AddTableFilter(CodeunitID, NVXCountryStatistics.FieldNo("Country/Region Filter"));
        TempNCETempDataCodeunit.AddTableFilter(CodeunitID, Database::"Country/Region", NVXCountryStatistics.FieldNo("Country/Region Filter"), '', CountryRegion.FieldNo(Code));

        Clear(RecField);
        RecField.SetRange(TableNo, TableID);
        RecField.SetFilter("No.", '<%1', 2000000000);
        RecField.SetRange(Enabled, true);
        if RecField.FindSet() then
            repeat
                TempNCETempDataCodeunit.AddDataSheetField(CodeunitID, RecField."No.");
            until RecField.Next() = 0;
    end;
}

If the codeunit is added to a data sheet after it has been published, fields and relations are automatically suggested, as in example 1: The following result is displayed in Excel after executing the evaluation:

Related information

• Edit Evaluations, Data Sheets, Data Sheet
  Via Data Sheets, sheets with data from tables can be added to the Excel workbook...