Dynamics 365 Business Central: How to use a query to create a report dataset (Creating Report with Query)

Hi, Readers.
Today I would like to talk about how to use a query to create a report dataset in Business Central.
We have discussed Dynamics 365 Business Central: Creating Report with temporary table before.

That’s not very difficult, just assign the value when the report is run. I was recently asked if it is possible to create reports using Query?
Business Central query objects enable you to retrieve records from one or more tables and then combine the data into rows and columns in a single dataset. For example,

A data item is a table and cannot be set directly to a query.

Table ‘ZY Purchase Order Query’ is missing AL AL0185

So how to deal with it? In fact, Microsoft has provided two good examples in MS Learn, Use a query to create a report dataset and Example: Using a query to define a report dataset.
Let’s see more details.

Instead of building the report dataset directly from tables, you can also use a query object. To achieve this, you must do the following:

• Add a global variable that points to the query object

• Use an Integer in the data item definition

• Add the fields in the query (global variable) to the dataitem.

• Add OnPreDataItem and OnAfterGetRecord triggers

This is a fixed pattern.

            trigger OnPreDataItem()
            begin
                ZYPurchaseOrderQuery.Open();
            end;

            trigger OnAfterGetRecord()
            begin
                if not ZYPurchaseOrderQuery.Read() then
                    CurrReport.Break();
            end;

To test this report, from the Business Central client, you can export report results as raw data to a Microsoft Excel file. The file contains all columns of the dataset, but without the layout applied. Use the file to help validate that the report returns the expected data, and to ensure that the report layout controls match the dataset value types. To export a report, run the report and select the Send to > Microsoft Excel Document (data only) on the request page.

More details: Dynamics 365 Business Central: Save report dataset to Excel from the request page (Report Inspector)

Next let’s create a simple layout.

Test:

Very simple, give it a try!!!😁

Source code: Github (Please note that the source code is for reference only, you can improve it according to your own needs)
Report:

report 50112 "ZY Purchase Order Report"
{
    Caption = 'ZY Purchase Order Report';
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    DefaultRenderingLayout = MyLayout;
    dataset
    {
        dataitem(Integer; Integer)
        {
            column(Buy_from_Vendor_No_; ZYPurchaseOrderQuery.Buy_from_Vendor_No_)
            {
            }
            column(Buy_from_Vendor_Name; ZYPurchaseOrderQuery.Buy_from_Vendor_Name)
            {
            }
            column(Order_Date; ZYPurchaseOrderQuery.Order_Date)
            {
            }
            column(Amount_Including_VAT; ZYPurchaseOrderQuery.Amount_Including_VAT)
            {
            }
            column(No_; ZYPurchaseOrderQuery.No_)
            {
            }
            column(Description; ZYPurchaseOrderQuery.Description)
            {
            }
            column(Quantity; ZYPurchaseOrderQuery.Quantity)
            {
            }
            column(Amount; ZYPurchaseOrderQuery.Amount)
            {
            }
            column(Inventory; ZYPurchaseOrderQuery.Inventory)
            {
            }

            trigger OnPreDataItem()
            begin
                ZYPurchaseOrderQuery.Open();
            end;

            trigger OnAfterGetRecord()
            begin
                if not ZYPurchaseOrderQuery.Read() then
                    CurrReport.Break();
            end;
        }
    }
    rendering
    {
        layout(MyLayout)
        {
            Type = Word;
            LayoutFile = 'ZYPurchaseOrderReport.docx';
        }
    }

    var
        ZYPurchaseOrderQuery: Query "ZY Purchase Order Query";
}

Query:

query 50100 "ZY Purchase Order Query"
{
    Caption = 'ZY Purchase Order Query';
    OrderBy = Descending(Buy_from_Vendor_No_);
    QueryCategory = 'Vendor List', 'Purchase Order List';
    elements
    {
        dataitem(Purchase_Header; "Purchase Header")
        {
            column(Buy_from_Vendor_No_; "Buy-from Vendor No.")
            {
            }
            column(Buy_from_Vendor_Name; "Buy-from Vendor Name")
            {
            }
            column(Order_Date; "Order Date")
            {
            }
            column(Amount_Including_VAT; "Amount Including VAT")
            {
            }
            dataitem(Purchase_Line; "Purchase Line")
            {
                DataItemLink = "Document Type" = Purchase_Header."Document Type",
                "Document No." = Purchase_Header."No.";
                column(No_; "No.")
                {
                }
                column(Description; Description)
                {
                }
                column(Quantity; Quantity)
                {
                }
                column(Amount; Amount)
                {
                }
                dataitem(Item; Item)
                {
                    DataItemLink = "No." = Purchase_Line."No.";
                    column(Inventory; Inventory)
                    {
                    }
                }
            }
        }
    }
}

PS:
1. Dynamics 365 Business Central: API query type (Develop a custom API using Query)

2. Business Central 2024 wave 2 (BC25): Tooltips on query columns (ToolTip Property & ToolTipML Property)

3. Business Central 2024 wave 2 (BC25): Support for SaveAsJson on the Query object

4. Business Central 2023 wave 2 (BC23): Analyze, group, and pivot data on queries using multiple tabs (In-client Analysis mode in Query)

5. Dynamics 365 Business Central: QueryCategory property – Smartlist? (Execute Queries from Pages)

END

Hope this will help.

Thanks for reading.

ZHU