Study guide
9.3 Query Objects: Joins, Aggregation & Query as Report Data Source
Microsoft Dynamics 365 Business Central Developer MB-820
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Business Central Architecture & Platform Foundation
1.1 Business Central Architecture & Extensions Model 1.2 Customization Lifecycle: Cloud (SaaS) vs. On-Premises 1.3 Base Application, System Application & AppSource
2 Chapter 2: Development Environment & Configuration
2.1: Development Environment Setup: VS Code, AL: Go! Projects & Sandboxes 2.2: AL Configuration Files: app.json & launch.json Parameters 2.3: Multi-Root Workspaces & Project Dependencies
3 Chapter 3: Extension Lifecycle: Debugging, Deployment, Multilanguage & Maintenance
3.1 Debugging & Snapshot Debugging in Visual Studio Code 3.2 Extension Deployment & Schema Synchronization Modes 3.3 Multilanguage Development with XLIFF & Translation Files 3.4 Extension Maintenance, Deprecation & Breaking Changes
4 Chapter 4: Tables, Table Extensions & Data Modeling
4.1: Building Tables, Fields, Keys & Data Types 4.2: Table Extensions & Table Modifications 4.3: FlowFields, FlowFilters & Table Relations 4.4: Enums, Enum Extensions & Extensible Types
5 Chapter 5: Pages, Page Extensions & Role Centers
5.1: Page Types, Anatomy & Control Structure 5.2: Building Card, List & Document Pages 5.3: Page Extensions & Page Customizations 5.4: Role Centers, Headlines & Cue Data Modeling
6 Chapter 6: Reports & Document Generation
6.1 Report Architecture, DataItems & Dataset Design 6.2 Report Layouts: RDLC, Word, Excel & Multilanguage 6.3 Document Reports & Request Page Programming 6.4 Processing-Only Reports & Report Substitutions
7 Chapter 7: XMLports & Data Exchange
7.1 XMLport Architecture, Formats & Properties 7.2 XMLport Node Hierarchy, Triggers & Stream Manipulation
8 Chapter 8: Codeunits, Events, Triggers & Interfaces
8.1 Codeunit Types, Execution & SingleInstance Codeunits 8.2 Events, Publishers, Subscribers & Event Isolation 8.3 Interfaces, Polymorphism & Dynamic Codeunit Invocation 8.4 Installation & Upgrade Codeunits, Data Transfer & Archive
9 Chapter 9: Entitlements, Permissions & Queries
9.1 Permission Sets & Permission Set Extensions 9.2 Inherent Permissions, Inherent Entitlements & Security Scoping 9.3 Query Objects: Joins, Aggregation & Query as Report Data Source
10 Chapter 10: UI Customization, User Assistance & Onboarding
10.1 Profile Objects & Custom Page Views in AL 10.2 User Assistance: ToolTips, Teaching Tips & In-App Tours 10.3 Assisted Setup, Guided Experience & Onboarding Checklists
11 Chapter 11: Business Central Design Patterns & Data Process Model
11.1 Functional Table Types: Master Data, Supplemental & Setup Patterns 11.2 Document Standards: Header, Line, History & Journal Architectures 11.3 Data Process Model & Posting Routine Architecture
12 Chapter 12: AL Language Fundamentals, Data Types & Variables
12.1 AL Syntax, Variable Scope, Primitive & Complex Data Types 12.2 Expressions, Control Flow Statements & Built-in Functions 12.3 Access Modifiers (Public, Internal, Local) & Code Scoping
13 Chapter 13: Data Manipulation, Streams & Error Handling
13.1 Record Operations: Get, FindSet, ModifyAll & Filter Functions 13.2 Streams, File Management & TempBlob 13.3 Error Handling: ErrorInfo, TryFunctions & Collectible Errors
14 Chapter 14: Testing, Test Toolkit & Automation
14.1 Testing Architecture & the Test Toolkit 14.2 Authoring Test Codeunits & UI Handler Functions
15 Chapter 15: Telemetry, Performance Optimization & Analyzers
15.1 Telemetry Configuration & Azure Application Insights 15.2 Custom Telemetry Signals with Session.LogMessage & KQL 15.3 Performance Profiler, SetLoadFields & Query Optimization 15.4 AL Analyzers: CodeCop, AppSourceCop, UICop & PTECop
16 Chapter 16: REST Web Services & JSON in AL
16.1 HttpClient, HttpRequestMessage & REST API Consumption 16.2 JSON Processing: JsonObject, JsonArray, JsonValue & JsonToken
17 Chapter 17: API Pages, OData, Read Scale-Out & OAuth
17.1 API Pages (PageType=API), OData Web Services & REST Endpoints 17.2 Bound and Unbound OData Actions with [ServiceEnabled] 17.3 Read Scale-Out Architecture & DataAccessIntent Configuration 17.4 Microsoft Entra ID App Registrations & OAuth 2.0 Integration
9.3 Query Objects: Joins, Aggregation & Query as Report Data Source
Key Takeaways Explain This Topic AI
Query objects (query) in AL execute single, highly-optimized Transact-SQL statements against the database engine, drastically outperforming nested AL record loops for analytical datasets.
SqlJoinType supports InnerJoin, LeftOuterJoin, RightOuterJoin, FullOuterJoin, and CrossJoin to define relational dataset joining between parent and child dataitems.
Aggregate functions (Sum, Avg, Min, Max, Count) compute server-side summaries; any column without an aggregation method automatically forms part of the SQL GROUP BY clause.
MethodType = Date with DateMethod properties (Year, Month, Day) enables native date-based grouping and time-series aggregation directly inside SQL Server.
Queries can be consumed programmatically via Open(), Read(), and Close(), exposed as REST/OData endpoints via QueryType = API, or used as report dataset dataitems with DataAccessIntent = ReadOnly.
Last updated: August 2026
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
Property Type / Options Description & Runtime Behavior
QueryType Normal , API Normal (default) is used for AL procedural consumption, reports, and charts. API exposes the query as an OData v4 / REST API endpoint using API metadata ( APIPublisher , APIGroup , APIVersion , EntityName , EntitySetName ).
OrderBy Sorting expression Defines the default sorting sequence across query columns ( ascending or descending ), compiling into a SQL ORDER BY clause.
TopNumberOfRows Integer Restricts the maximum number of rows returned by the query, compiling into a SQL TOP (N) clause for high-speed dashboard widgets and top-N reporting.
DataAccessIntent ReadOnly , ReadWrite When set to ReadOnly , directs the SQL query to a read-only database replica in Azure SQL Database (Read Scale-Out), preventing lock contention on primary transactional nodes.
Loading diagram... SQL Join Types Relational Mechanics in AL Queries
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:
SqlJoinType SQL Equivalent Relational Behavior & Output
InnerJoin INNER JOIN Returns only rows where the DataItemLink join condition finds matching records in both the parent and child tables. If a parent record has zero child records, the parent is omitted entirely.
LeftOuterJoin LEFT OUTER JOIN Returns all rows from the parent dataitem (left table), regardless of whether matching child records exist. If no matching child record is found, child columns return NULL / blank. This is the default join type in AL.
RightOuterJoin RIGHT OUTER JOIN Returns all rows from the child dataitem (right table), matching parent rows where possible. If a child record has no matching parent, parent columns return NULL .
FullOuterJoin FULL OUTER JOIN Returns all rows when there is a match in either the parent or child table. Unmatched columns from either side contain NULL .
CrossJoin CROSS JOIN Produces a Cartesian product combining every row from the parent table with every row from the child table. DataItemLink must not be specified.
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;
- }
- }
- }
- }
- }