9.3 Query Objects: Joins, Aggregation & Query as Report Data Source

9.3 Query Objects: Joins, Aggregation & Query as Report Data Source

In Microsoft Dynamics 365 Business Central, Query objects (query) provide a declarative mechanism for retrieving, joining, filtering, and aggregating data across multiple relational database tables. Unlike iterative AL record loops (FindSet / Next) and FlowField evaluations (CalcFields), which execute multiple sequential round-trips to SQL Server, query objects compile into a single, highly-optimized SQL SELECT statement executed entirely within the database engine.

For the MB-820 certification exam, developers must master query structure, join mechanics (SqlJoinType), aggregate functions, date grouping methods, programmatic consumption, API queries, and using queries as report data sources.

1. Query Object Anatomy in AL

A query object definition consists of top-level properties and an elements hierarchy containing dataitem, column, and filter elements.

query 50100 "Customer Sales Analysis"
{
    QueryType = Normal;
    Caption = 'Customer Sales Analysis';
    OrderBy = descending(TotalSalesLCY);
    TopNumberOfRows = 50;
    DataAccessIntent = ReadOnly;

    elements
    {
        dataitem(Customer; Customer)
        {
            column(CustomerNo; "No.") { }
            column(CustomerName; Name) { }
            column(CustomerPostingGroup; "Customer Posting Group") { }

            filter(BalanceFilter; "Balance (LCY)")
            {
                ColumnFilter = BalanceFilter = filter(> 0);
            }

            dataitem(CustLedgerEntry; "Cust. Ledger Entry")
            {
                DataItemLink = "Customer No." = Customer."No.";
                SqlJoinType = InnerJoin;
                DataItemTableFilter = "Document Type" = filter(Invoice | "Credit Memo");

                column(TotalSalesLCY; "Sales (LCY)")
                {
                    Method = Sum;
                }
                column(TransactionCount; "Entry No.")
                {
                    Method = Count;
                }
                column(PostingYear; "Posting Date")
                {
                    MethodType = Date;
                    DateMethod = Year;
                }
                column(PostingMonth; "Posting Date")
                {
                    MethodType = Date;
                    DateMethod = Month;
                }
            }
        }
    }
}

Top-Level Query Properties

2. SQL Join Types (SqlJoinType)

When nesting child dataitems beneath a parent dataitem, the SqlJoinType property determines how the database engine merges rows between tables:

DataItemLink vs. DataItemTableFilter

• DataItemLink: Defines the relational foreign key equality joining the child table to the parent table (e.g., DataItemLink = "Customer No." = Customer."No.";). Compiles into the SQL ON clause.
• DataItemTableFilter: Defines static pre-filters applied to the dataitem table before join evaluation (e.g., DataItemTableFilter = "Document Type" = const(Invoice);). Compiles into the SQL WHERE clause.

3. Aggregations, Grouping & Date Methods

AL queries provide native server-side aggregation, replacing manual accumulator variables and nested loops in AL code.

Aggregate Methods (Method)

When a column specifies a Method property, SQL Server calculates the aggregate over the grouped dataset:

• Sum: Calculates the mathematical sum of numeric values (Decimal, Integer, BigInteger).
• Avg: Computes the arithmetic average across rows in the group.
• Min / Max: Retrieves the minimum or maximum value within the group.
• Count: Counts the number of non-null records matching the group.

The Automatic GROUP BY Rule: Any column in an AL query that does not declare an aggregate Method is automatically included in the SQL GROUP BY clause. If you include CustomerNo and CustomerName as standard columns and TotalSalesLCY with Method = Sum, SQL Server groups results uniquely by (CustomerNo, CustomerName).

Date Grouping (MethodType = Date)

Queries support grouping date/time fields into calendar periods directly in the database engine:

• DateMethod = Year: Groups transactions by calendar year (e.g., 2025, 2026).
• DateMethod = Month: Groups transactions by month number (1 through 12).
• DateMethod = Day: Groups transactions by day of the month (1 through 31).

column(PostingYear; "Posting Date")
{
    MethodType = Date;
    DateMethod = Year;
}
column(PostingMonth; "Posting Date")
{
    MethodType = Date;
    DateMethod = Month;
}

4. Query Consumption in AL Code, APIs & Reports

Programmatic Query Execution Lifecycle

To execute a query in AL, declare a query variable and follow the standard execution sequence: SetRange() / SetFilter(), Open(), Read(), and Close().

codeunit 50110 "Sales Query Processor"
{
    procedure ExportTopCustomers()
    var
        CustSalesQuery: Query "Customer Sales Analysis";
    begin
        // 1. Apply runtime filters prior to opening cursor
        CustSalesQuery.SetRange(CustomerPostingGroup, 'DOMESTIC');
        CustSalesQuery.SetFilter(TotalSalesLCY, '> %1', 50000);
        CustSalesQuery.TopNumberOfRows(10);

        // 2. Open the SQL query cursor
        if CustSalesQuery.Open() then begin
            // 3. Iterate through aggregated result rows
            while CustSalesQuery.Read() do begin
                Message('Customer: %1, Sales: %2, Year: %3', 
                    CustSalesQuery.CustomerName, 
                    CustSalesQuery.TotalSalesLCY, 
                    CustSalesQuery.PostingYear);
            end;
            // 4. Close the cursor to release SQL resources
            CustSalesQuery.Close();
        end;
    end;
}

File Streaming Methods

AL queries provide built-in file export methods that stream output directly to external files:

• CustSalesQuery.SaveAsCsv('C:\Exports\Sales.csv');
• CustSalesQuery.SaveAsXml('C:\Exports\Sales.xml');

Using a Query as a Report DataItem Source

In modern Business Central reporting, developers can use a query as the direct data source for a report dataitem. This technique leverages the server-side aggregation and joining power of queries to accelerate report dataset generation, drastically reducing report execution time:

report 50120 "Top Customer Sales Report"
{
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    DefaultRenderingLayout = StandardRDLC;

    dataset
    {
        dataitem(SalesAnalysis; "Customer Sales Analysis")
        {
            column(CustNo; CustomerNo) { }
            column(CustName; CustomerName) { }
            column(SalesTotal; TotalSalesLCY) { }
            column(TxCount; TransactionCount) { }
            column(Year; PostingYear) { }
            column(Month; PostingMonth) { }
        }
    }
}

API Queries (QueryType = API)

When building analytical REST endpoints for Power BI or third-party platforms, setting QueryType = API exposes the query as an OData v4 / REST endpoint with server-side aggregation:

query 50125 "Sales Analytics API"
{
    QueryType = API;
    APIPublisher = 'partner';
    APIGroup = 'analytics';
    APIVersion = 'v1.0';
    EntityName = 'salesAnalysis';
    EntitySetName = 'salesAnalyses';
    DataAccessIntent = ReadOnly;

    elements
    {
        dataitem(Customer; Customer)
        {
            column(id; SystemId) { }
            column(number; "No.") { }
            column(displayName; Name) { }
            
            dataitem(CustLedger; "Cust. Ledger Entry")
            {
                DataItemLink = "Customer No." = Customer."No.";
                SqlJoinType = InnerJoin;
                
                column(totalSales; "Sales (LCY)")
                {
                    Method = Sum;
                }
            }
        }
    }
}