Dynamics 365 Business Central: Creating Report with temporary table

Hi, Readers.
Today I would like to talk about how to create Report with temporary table in Business Central.
For experienced developers, this is not a very difficult question. But recently I saw some questions about this issue on the Dynamics 365 BC forum, so in this post I would like to discuss it briefly.
For exmaple:
Designing a RDLC Custom Report in Business Central – Dynamics 365 Business Central Forum Community Forum

Error message when running the report:

The report couldn’t be generated, because it was empty. Adjust your filters and try again.

As you might know, in Business Central 2020 release wave 2 (BC17), Microsoft brought us a new feature, TableType Property = Temporary.

We have one of the most thorough options other than using a temporary record variable and setting the SourceTableTemporary property on a page to true. It’s great.

Of course, the temporary table can also be used as a DataItem in Report. But please note that there is no data in the table.

So how should we deal with it? Very simple, just insert the data before running the report. Generally we use OnPreReport (Report) Trigger (runs before a report is run).

More details about Report Triggers and Runtime Operations:

Let’s look at a simple example.

First I created a temporary table.

Source Code:

table 50111 "ZY Cust. Ledger Entry"
{
    Caption = 'ZY Cust. Ledger Entry';
    DrillDownPageID = "Customer Ledger Entries";
    LookupPageID = "Customer Ledger Entries";
    TableType = Temporary;

    fields
    {
        field(1; "Entry No."; Integer)
        {
            Caption = 'Entry No.';
        }
        field(3; "Customer No."; Code[20])
        {
            Caption = 'Customer No.';
            TableRelation = Customer;
        }
        field(4; "Posting Date"; Date)
        {
            Caption = 'Posting Date';
        }
        field(5; "Document Type"; Enum "Gen. Journal Document Type")
        {
            Caption = 'Document Type';
        }
        field(6; "Document No."; Code[20])
        {
            Caption = 'Document No.';
        }
        field(7; Description; Text[100])
        {
            Caption = 'Description';
        }
        field(8; "Customer Name"; Text[100])
        {
            Caption = 'Customer Name';
        }
        field(11; "Currency Code"; Code[10])
        {
            Caption = 'Currency Code';
            TableRelation = Currency;
        }
        field(13; Amount; Decimal)
        {
            AutoFormatExpression = "Currency Code";
            AutoFormatType = 1;
            Caption = 'Amount';
            Editable = false;
        }
        field(14; "Remaining Amount"; Decimal)
        {
            AutoFormatExpression = "Currency Code";
            AutoFormatType = 1;
            Caption = 'Remaining Amount';
            Editable = false;
        }
    }

    keys
    {
        key(Key1; "Entry No.")
        {
            Clustered = true;
        }
    }

    fieldgroups
    {
        fieldgroup(DropDown; "Entry No.", Description, "Customer No.", "Posting Date", "Document Type", "Document No.")
        {
        }
    }
}

Then use this temporary table to create a report.

Insert table data in OnPreReport (Report) Trigger. You can process the data here, such as modifying the data format, adding new calculation logic, changing positive and negative numbers, etc.

Source Code:

report 50112 ZYCustLedgerEntryReport
{
    Caption = 'ZY Cust Ledger Entry Report';
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    DefaultRenderingLayout = MyLayout;

    dataset
    {
        dataitem("ZY Cust. Ledger Entry"; "ZY Cust. Ledger Entry")
        {
            column(Entry_No_; "Entry No.")
            {
            }
            column(Customer_No_; "Customer No.")
            {
            }
            column(Customer_Name; "Customer Name")
            {
            }
            column(Posting_Date; "Posting Date")
            {
            }
            column(Document_Type; "Document Type")
            {
            }
            column(Document_No_; "Document No.")
            {
            }
            column(Description; Description)
            {
            }
            column(Amount; Amount)
            {
            }
            column(Remaining_Amount; "Remaining Amount")
            {
            }
        }
    }

    rendering
    {
        layout(MyLayout)
        {
            Type = Word;
            LayoutFile = 'mylayout.docx';
        }
    }

    trigger OnPreReport()
    var
        CustLedgerEntry: Record "Cust. Ledger Entry";
    begin
        "ZY Cust. Ledger Entry".Reset();
        "ZY Cust. Ledger Entry".DeleteAll();
        CustLedgerEntry.Reset();
        if CustLedgerEntry.FindSet() then
            repeat
                "ZY Cust. Ledger Entry".Init();
                "ZY Cust. Ledger Entry"."Entry No." := CustLedgerEntry."Entry No.";
                "ZY Cust. Ledger Entry"."Customer No." := CustLedgerEntry."Customer No.";
                "ZY Cust. Ledger Entry"."Customer Name" := CustLedgerEntry."Customer Name";
                "ZY Cust. Ledger Entry"."Posting Date" := CustLedgerEntry."Posting Date";
                "ZY Cust. Ledger Entry"."Document Type" := CustLedgerEntry."Document Type";
                "ZY Cust. Ledger Entry"."Document No." := CustLedgerEntry."Document No.";
                "ZY Cust. Ledger Entry".Description := CustLedgerEntry.Description;
                "ZY Cust. Ledger Entry".Amount := CustLedgerEntry.Amount;
                "ZY Cust. Ledger Entry"."Remaining Amount" := CustLedgerEntry."Remaining Amount";
                "ZY Cust. Ledger Entry".Insert();
            until CustLedgerEntry.Next() = 0;
    end;
}

PS: Please note that the source code is for reference only, you can improve it according to your own needs

This way there will not be any problems when running.

And, this trigger runs after the request page is run. So the added filter is also valid.

Very simple, isn’t it?
This method is generally used for some very complex reports. When it is very difficult to use DataItem to combine data, you can create a temporary table containing all the fields you need, and then extract and combine data in the code. In this case, generally only one DataItem and one Trigger are needed. This is just another way of thinking about developing reports. I hope can give you some new hints.

END

Hope this will help.

Thanks for reading.

ZHU