Use a query to create a report dataset

5 minutes

Queries enable you to retrieve records from one or more tables or data sources and then combine the data into rows and columns in a single dataset. Queries can also perform calculations on data, such as finding the sum or the average of all values in a column of the dataset.

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 OnPreDataItem and OnAfterGetRecord triggers

Here’s an example of a query object that fetches several fields from the Item table:

al-languageCopy
query 70100 "Items"
{
    Caption = 'Items.Query';
    QueryType = Normal;

    elements
    {
        dataitem(Item; Item)
        {
            column(No; "No.") { }
            column(Description; Description) { }
            column("Type"; "Type") { }
            column(UnitPrice; "Unit Price") { }
            column(UnitCost; "Unit Cost") { }
            column(Inventory; Inventory) { }
        }
    }
}

The following example shows how you can use this query as the data source for a report dataset.

al-languageCopy
report 70100 Items
{
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    Caption = 'Items based on Query';

    dataset
    {
        dataitem(Integer;Integer)
        {
            column(ItemNo; Items.No)
            {}
            column(Description; Items.Description)
            {}        
            column(Type; Items.Type)
            {}
            column(UnitCost; Items.UnitCost)
            {}
            column(UnitPrice; Items.UnitPrice)
            {}
            column(Inventory; Items.Inventory)
            {}

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

            trigger OnAfterGetRecord()
            begin
                if not Items.Read() then
                    CurrReport.Break();
            end;
        }
    }
    var
        Items: Query Items;
}

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.

Continue

Feedback