6.1 Report Architecture, DataItems & Dataset Design

6.1 Report Architecture, DataItems & Dataset Design

In Microsoft Dynamics 365 Business Central, reports serve two distinct enterprise functions: presenting formatted business documents (such as sales invoices, purchase orders, and picking lists) and executing batch data modifications without a visual layout. For developers preparing for the MB-820 certification exam, understanding the AL report data model—specifically how the Navision Server Tier (NST) constructs, filters, and flattens hierarchical datasets—is a foundational competency in AL development.

1. Report Object Anatomy in AL

A report in AL is defined using the report object keyword. It encapsulates top-level report properties, a single dataset block, an optional requestpage block, an optional rendering block (or legacy layout properties), and report-level triggers.

report 50100 "Sales Order Summary"
{
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    Caption = 'Sales Order Summary';
    DefaultRenderingLayout = RDLC_Layout;
    DataAccessIntent = ReadOnly;
    AllowScheduling = true;
    PreviewMode = PrintLayout;

    dataset
    {
        // Hierarchical dataitems and columns defined here
    }

    requestpage
    {
        // User interaction controls and options
    }

    rendering
    {
        // Layout targets (RDLC, Word, Excel)
    }
}

Critical Report Properties

2. DataItems and Relational Hierarchy

The dataset block defines the data source hierarchy. Each dataitem represents a table cursor that the NST iterates over at runtime. A dataset can contain multiple root DataItems executed sequentially, or deeply nested DataItems establishing parent-child master-detail relationships.

dataset
{
    dataitem(Header; "Sales Header")
    {
        DataItemTableView = sorting("Document Type", "No.") where("Document Type" = const(Order));
        RequestFilterFields = "No.", "Sell-to Customer No.", "Posting Date";
        PrintOnlyIfDetail = true;
        
        column(OrderNo; "No.") { IncludeCaption = true; }
        column(SellToCustNo; "Sell-to Customer No.") { IncludeCaption = true; }
        column(SellToCustName; "Sell-to Customer Name") { }
        column(PostingDate; "Posting Date") { IncludeCaption = true; }

        dataitem(Line; "Sales Line")
        {
            DataItemLink = "Document Type" = field("Document Type"),
                           "Document No." = field("No.");
            DataItemTableView = sorting("Document Type", "Document No.", "Line No.");
            CalcFields = "Reserved Quantity";

            column(LineNo; "Line No.") { }
            column(ItemNo; "No.") { IncludeCaption = true; }
            column(Description; Description) { IncludeCaption = true; }
            column(Quantity; Quantity) { IncludeCaption = true; }
            column(UnitPrice; "Unit Price") { IncludeCaption = true; }
            column(LineAmount; "Line Amount") { IncludeCaption = true; }
            column(ReservedQuantity; "Reserved Quantity") { }
        }
    }
}

Essential DataItem Properties

Exam Watchout — PrintOnlyIfDetail Mechanics: If PrintOnlyIfDetail = false (default), a Sales Header with no Sales Line records will still generate a row in the flattened dataset with header values populated and all line values set to NULL/blank. If PrintOnlyIfDetail = true, the header is completely suppressed if no matching lines are found.

3. Dataset Flattening & Rectangular Data Architecture

A critical architectural concept tested on the MB-820 exam is that all reporting layout engines (RDLC, Microsoft Word, Microsoft Excel) consume data as a single flat, rectangular two-dimensional table (DataSet_Result), regardless of how deeply nested the AL dataset hierarchy is.

How the NST Flattens Data

When the report runs, the NST executes a nested loop:

1. The outer dataitem (Header) fetches Record 1.
2. The inner dataitem (Line) applies DataItemLink and fetches its matching records one by one.
3. For every single inner record retrieved, a full row is emitted to the dataset containing both the parent column values and the child column values.
4. If Parent 1 has 3 child lines, 3 rows are written to the dataset. The parent column values (e.g., OrderNo, SellToCustName) are repeated identically across all 3 rows.
5. If a parent dataitem has multiple sibling nested dataitems (e.g., dataitem(Line; ...) and dataitem(CommentLine; ...)), the NST performs a Cartesian product / outer join across sibling branches, resulting in sparse rows where non-active branch columns contain NULL.

+-----------------------------------------------------------------------------------------+
| Flattened 2D Dataset (DataSet_Result)                                                   |
+------------+------------------+---------+-------------+----------+------------+---------+
| OrderNo    | SellToCustName   | LineNo  | ItemNo      | Quantity | UnitPrice  | LineAmt |
+------------+------------------+---------+-------------+----------+------------+---------+
| 1001       | Adatum Corp.     | 10000   | 1896-S      | 2        | 150.00     | 300.00  |
| 1001       | Adatum Corp.     | 20000   | 1900-S      | 1        | 250.00     | 250.00  |
| 1002       | Trey Research    | 10000   | 1968-W      | 10       | 45.00      | 450.00  |
+------------+------------------+---------+-------------+----------+------------+---------+

Performance & Memory Implications of Dataset Flattening

Because parent data repeats on every child row, emitting dozens of unneeded parent columns can dramatically inflate the memory footprint and network payload transmitted from the Business Central Server tier to the layout rendering engine.

Developers should adhere to the following dataset optimization practices:

• Prune Unused Columns: Only include columns that are directly referenced by active layout designs or required for grouping keys.
• Avoid Cross-Branch Multiplication: Avoid nesting multiple unrelated sibling DataItems under a single parent if both can have large numbers of records; instead, consider using separate integer loops or buffer tables.
• Leverage Partial Records: In report AL triggers (OnAfterGetRecord), use Rec.SetLoadFields() when reading secondary record variables to avoid pulling full table schemas into memory.

4. Column Declarations & Data Mapping

Columns represent the individual data fields exposed to layout designers. A column can source its value from a table field, a global variable, an expression, or a system function.

Column Syntax and Options

column(ColumnName; SourceExpression)
{
    IncludeCaption = true;
    AutoFormatType = 1;
    AutoFormatExpression = Header."Currency Code";
}

• ColumnName: The identifier used in the layout file (XML node name). Must be a valid XML element name without spaces or special symbols (e.g., use PostingDate, not Posting Date!).
• SourceExpression: Table field (e.g., "No."), variable (e.g., TotalAmount), expression (e.g., Format(Today, 0, 4)), or method call.
• IncludeCaption = true: When enabled on a field column, the AL compiler automatically generates an auxiliary caption column in the dataset schema named ColumnNameCaption (e.g., OrderNoCaption). This caption reflects the localized caption of the underlying table field based on the user's active runtime language.
• AutoFormatType & AutoFormatExpression: Formats monetary decimals according to specific currency rules or precision definitions.

RequestFilterFields vs. DataItemTableView

Understanding the distinct roles of RequestFilterFields and DataItemTableView is critical for exam success: