AL development from scratch, part 8: reports, report extensions, queries and XMLports

This is part 8 of AL development from scratch. Pallet Manager can now store pallet types, link them to items and calculate pallets needed (part 7). This part covers the three object types that move data in and out in bulk:

As in the rest of the series, every complete listing starts with namespace BusinessBoxed.PalletManager; and the using lines for the Microsoft objects it refers to (here, using Microsoft.Inventory.Item; for the Item table). Namespaces are optional: leave those lines out and everything still works in the global namespace. See part 1 for when and why to use them.

• Reports, which read data and produce a document, a spreadsheet or a batch job.
• Queries, which join and total tables in a single trip to the database.
• XMLports, which import and export files in XML or delimited text such as CSV.

We'll build one of each for Pallet Manager: a "Pallet Requirements" report with an Excel layout, an "Items by Pallet Type" query, and a CSV import for pallet types.

Reports

A report has four parts, and it helps to keep them separate in your head:

1. Properties: what it's called, where it appears, how it runs.
2. The dataset: the tables it reads (dataitems) and the values it outputs (columns). This is the report's real content.
3. The request page: the options and filters the user fills in before it runs.
4. The rendering section: the layouts that turn the dataset into something to look at. One dataset can have several layouts: an Excel one for analysis and a Word one for printing, for example.

The big shift from old NAV reports is that the dataset and the layout are completely separate. The AL decides what data the report produces; the layout file decides how it looks. Users can switch between layouts, and even upload their own, without touching the AL. (See report layouts: RDLC, Word or Excel.)

Report properties

The dataset: dataitems and columns

A dataitem is a table the report loops through. The report reads every row of the dataitem (after filters) and, for each row, outputs its columns and runs any nested dataitems. A column is one value in the output. It can be a field, a variable or any expression.

dataset
{
    dataitem(Item; Item)
    {
        DataItemTableView = sorting("No.") where(Type = const(Inventory));
        RequestFilterFields = "No.", "Item Category Code", "BBX Default Pallet Type";
        PrintOnlyIfDetail = false;

        column(ItemNo; "No.") { IncludeCaption = true; }
        column(ItemDescription; Description) { IncludeCaption = true; }
        column(PalletsNeeded; PalletsNeeded) { }

        dataitem(PalletType; "BBX Pallet Type")
        {
            DataItemLink = Code = field("BBX Default Pallet Type");

            column(PalletTypeCode; Code) { }
            column(PalletDescription; Description) { }
        }
    }
}

Column names (ItemNo, PalletsNeeded) are what the layout sees, so they must be simple identifiers and they become part of your contract with every layout ever made for the report. Don't rename them casually. IncludeCaption = true also sends the field's caption, so the layout can use translated headings.

Report and dataitem triggers

Inside these triggers, CurrReport.Skip() skips the current row, CurrReport.Break() stops the current dataitem, and CurrReport.Quit() stops the whole report.

The request page

The request page holds options (variables the user sets) as well as the filter fields. It's a page, with the same layout syntax you learned in part 5.

requestpage
{
    SaveValues = true;

    layout
    {
        area(Content)
        {
            group(Options)
            {
                Caption = 'Options';
                field(QuantityField; Quantity)
                {
                    ApplicationArea = All;
                    Caption = 'Quantity per item';
                    ToolTip = 'Specifies the quantity, in the base unit of measure, to calculate pallets for.';
                    DecimalPlaces = 0 : 5;
                }
                field(OnlyMissingField; OnlyMissing)
                {
                    ApplicationArea = All;
                    Caption = 'Only items without units per pallet';
                    ToolTip = 'Specifies whether to show only items that have a pallet type but no units per pallet.';
                }
            }
        }
    }
}

SaveValues = true remembers the user's last choices, which users appreciate more than almost anything else on a request page.

The rendering section

Layouts are files in your project, declared in the rendering section. Each has a name, a type and a file.

rendering
{
    layout(ExcelLayout)
    {
        Type = Excel;
        LayoutFile = 'src/report/layouts/BBXPalletRequirements.xlsx';
        Caption = 'Pallet requirements (Excel)';
        Summary = 'A worksheet of items, pallet types and pallets needed, ready for a pivot table.';
    }
    layout(WordLayout)
    {
        Type = Word;
        LayoutFile = 'src/report/layouts/BBXPalletRequirements.docx';
        Caption = 'Pallet requirements (Word)';
        Summary = 'A printable list.';
    }
}

The layout types are RDLC (pixel-precise, designed in Report Builder or Visual Studio), Word (designed in Word, and the easiest for users to change), Excel (the dataset lands on a data sheet and you build tables, pivots and charts on other sheets) and Custom (for layouts rendered by an extension). For a brand new Excel or Word layout, add the layout entry with a file name that doesn't exist yet and build the project: the compiler creates a starter file containing the dataset for you to design from.

Pallet Manager: the Pallet Requirements report

namespace BusinessBoxed.PalletManager;

using Microsoft.Inventory.Item;

report 50100 "BBX Pallet Requirements"
{
    Caption = 'Pallet Requirements';
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = All;
    AdditionalSearchTerms = 'pallets, logistics, palletisation';
    DefaultRenderingLayout = ExcelLayout;
    DataAccessIntent = ReadOnly;

    dataset
    {
        dataitem(Item; Item)
        {
            DataItemTableView = sorting("No.") where(Type = const(Inventory));
            RequestFilterFields = "No.", "Item Category Code", "BBX Default Pallet Type";

            column(ItemNo; "No.") { IncludeCaption = true; }
            column(ItemDescription; Description) { IncludeCaption = true; }
            column(BaseUnitOfMeasure; "Base Unit of Measure") { IncludeCaption = true; }
            column(PalletTypeCode; "BBX Default Pallet Type") { IncludeCaption = true; }
            column(PalletDescription; PalletType.Description) { }
            column(UnitsPerPallet; "BBX Units per Pallet") { IncludeCaption = true; }
            column(QuantityRequested; Quantity) { }
            column(PalletsNeeded; PalletsNeeded) { }
            column(PalletCost; PalletCost) { }

            trigger OnPreDataItem()
            begin
                SetFilter("BBX Default Pallet Type", '<>%1', '');
                if OnlyMissing then
                    SetRange("BBX Units per Pallet", 0);
                SetLoadFields("No.", Description, "Base Unit of Measure", "BBX Default Pallet Type", "BBX Units per Pallet");
            end;

            trigger OnAfterGetRecord()
            begin
                Clear(PalletType);
                if PalletType.Get("BBX Default Pallet Type") then;

                PalletsNeeded := 0;
                PalletCost := 0;
                if "BBX Units per Pallet" > 0 then begin
                    PalletsNeeded := PalletMgt.CalcPalletsNeeded(Item, Quantity);
                    PalletCost := PalletsNeeded * PalletType."Unit Cost";
                end;
            end;
        }
    }

    requestpage
    {
        SaveValues = true;

        layout
        {
            area(Content)
            {
                group(Options)
                {
                    Caption = 'Options';
                    field(QuantityField; Quantity)
                    {
                        ApplicationArea = All;
                        Caption = 'Quantity per item';
                        ToolTip = 'Specifies the quantity, in the base unit of measure, to calculate pallets for.';
                        DecimalPlaces = 0 : 5;
                    }
                    field(OnlyMissingField; OnlyMissing)
                    {
                        ApplicationArea = All;
                        Caption = 'Only items without units per pallet';
                        ToolTip = 'Specifies whether to show only items that have a pallet type but no units per pallet.';
                    }
                }
            }
        }
    }

    rendering
    {
        layout(ExcelLayout)
        {
            Type = Excel;
            LayoutFile = 'src/report/layouts/BBXPalletRequirements.xlsx';
            Caption = 'Pallet requirements (Excel)';
            Summary = 'Items, pallet types and pallets needed, ready for a pivot table.';
        }
    }

    labels
    {
        PalletsNeededCaption = 'Pallets needed';
        PalletCostCaption = 'Pallet cost';
        QuantityCaption = 'Quantity';
    }

    var
        PalletType: Record "BBX Pallet Type";
        PalletMgt: Codeunit "BBX Pallet Mgt.";
        Quantity: Decimal;
        PalletsNeeded: Integer;
        PalletCost: Decimal;
        OnlyMissing: Boolean;

    trigger OnInitReport()
    begin
        Quantity := 1000;
    end;
}

Notice the report does no pallet maths of its own: it calls the codeunit from part 7, so the report, the Item Card action and any API all give the same answer. The labels section sends extra translatable captions to the layout for columns that aren't table fields. The if PalletType.Get(...) then; with an empty then is the standard way to read a row that may not exist without raising an error.

Because the report is ReadOnly and ReportsAndAnalysis, users find it by searching for "pallet", and it can be scheduled from the request page to run overnight and land in their Report Inbox.

Processing-only reports: batch jobs

Set ProcessingOnly = true and leave out the rendering section, and a report becomes a batch job: a request page for options and filters, a loop over the data, and code in OnAfterGetRecord that does the work. Users get filters, saved settings and scheduling for free, which is why so many of Microsoft's batch jobs ("Adjust Cost - Item Entries", "Suggest Vendor Payments") are reports. For example, a "Replace Pallet Type" batch job would filter Items on the old pallet type, Validate the new one and Modify each, then Message a count in OnPostReport. Remember DataAccessIntent must stay ReadWrite (the default) for anything that writes.

Report extensions

A report extension adds to someone else's report without copying it. You can:

• add columns to an existing dataitem,
• add new dataitems,
• add fields to the request page,
• add new layouts that use the extended dataset,
• add code to the existing triggers with modify (OnBeforeAfterGetRecord, OnAfterAfterGetRecord and similar).

You can't remove or rename existing columns or dataitems, change the original dataset logic, or edit the original layouts. If you need the standard layout plus one extra field, add a new layout to the extension (often a copy of the standard one with your field added) and make it the default in Report Layouts.

namespace BusinessBoxed.PalletManager;

reportextension 50100 "BBX Pallet Req. Extra" extends "BBX Pallet Requirements"
{
    dataset
    {
        add(Item)
        {
            column(GrossWeight; "Gross Weight") { IncludeCaption = true; }
        }
        modify(Item)
        {
            trigger OnAfterAfterGetRecord()
            begin
                // runs after the report's own OnAfterGetRecord for each item
            end;
        }
    }

    requestpage
    {
        layout
        {
            addlast(Content)
            {
                field(ShowWeightField; ShowWeight)
                {
                    ApplicationArea = All;
                    Caption = 'Show gross weight';
                    ToolTip = 'Specifies whether to include gross weight.';
                }
            }
        }
    }

    rendering
    {
        layout(ExcelWithWeight)
        {
            Type = Excel;
            LayoutFile = 'src/report/layouts/BBXPalletRequirementsWeight.xlsx';
            Caption = 'Pallet requirements with weight (Excel)';
        }
    }

    var
        ShowWeight: Boolean;
}

We've extended our own report here to keep the example self-contained, but the syntax is identical for Microsoft's reports, and that's where you will mostly use it: an extra column on the sales invoice dataset, or a customer's own layout for the statement.

Queries

A query reads several tables in one go, joined and optionally grouped and totalled, and sends a single statement to the database. Where a report or a loop in AL would read table A, then table B for each row of A, a query lets SQL do the join. For lookups across tables and totals, that's usually much faster. Queries can also be published as web services or used as API queries (part 9), and are a good source for Power BI. (See query objects in AL for more on when they're worth it.)

Query anatomy

Pallet Manager: Items by Pallet Type

This query returns one row per pallet type with how many items use it and the total units per pallet across those items.

namespace BusinessBoxed.PalletManager;

using Microsoft.Inventory.Item;

query 50100 "BBX Items by Pallet Type"
{
    Caption = 'Items by Pallet Type';
    QueryType = Normal;
    OrderBy = descending(NoOfItems);

    elements
    {
        dataitem(PalletType; "BBX Pallet Type")
        {
            column(PalletTypeCode; Code) { }
            column(PalletDescription; Description) { }
            column(Material; Material) { }
            filter(Blocked; Blocked) { }

            dataitem(Item; Item)
            {
                DataItemLink = "BBX Default Pallet Type" = PalletType.Code;
                SqlJoinType = InnerJoin;

                column(NoOfItems)
                {
                    Method = Count;
                }
                column(TotalUnitsPerPallet; "BBX Units per Pallet")
                {
                    Method = Sum;
                }
            }
        }
    }
}

Because two columns have a Method, the others (code, description, material) become the GROUP BY. A Count column needs no source field. The InnerJoin leaves out pallet types no item uses; switch to the default LeftOuterJoin to include them with a count of zero.

Reading a query in AL

A query variable works like a cursor: set filters, Open, Read until it returns false, Close.

procedure ShowPalletUsage()
var
    ItemsByPalletType: Query "BBX Items by Pallet Type";
    Builder: TextBuilder;
    LineLbl: Label '%1 (%2): %3 items', Comment = '%1 = Pallet type code, %2 = Description, %3 = Number of items';
begin
    ItemsByPalletType.SetRange(Blocked, false);
    ItemsByPalletType.Open();
    while ItemsByPalletType.Read() do
        Builder.AppendLine(StrSubstNo(LineLbl, ItemsByPalletType.PalletTypeCode, ItemsByPalletType.PalletDescription, ItemsByPalletType.NoOfItems));
    ItemsByPalletType.Close();
    Message(Builder.ToText());
end;

Filters can be set on columns and on filter elements (Blocked here). A query can also be shown to users through a list page that reads it into a temporary table.

Queries are read-only. If you need to change data, find the rows with a query and then update them with records.

XMLports

An XMLport describes the structure of a file and maps it to tables, so Business Central can import or export it. Despite the name, they handle delimited text (CSV, tab-separated) as well as XML. For a one-off load, configuration packages and Excel import may be quicker. An XMLport is the right tool when users import the same file format regularly, or when another system sends you files.

XMLport properties

The schema

The schema describes the file's structure with three kinds of node:

• textelement: a node that isn't tied to a table field. In VariableText files, the outermost node must be a textelement, and textelements are handy for columns you want to read into a variable and handle yourself.
• tableelement: a node that repeats once per record of a table. On import, each occurrence creates a record.
• fieldelement: a node mapped straight to a field of the table.

Tableelements have properties that control the import: AutoSave (insert the record automatically, default true), AutoUpdate (update an existing record if the key already exists) and AutoReplace (replace an existing record with the imported one). The triggers you'll use most are OnPreXmlPort and OnPostXmlPort for the whole run, and OnAfterInitRecord and OnBeforeInsertRecord on a tableelement, where you can check, transform or skip each record with currXMLport.Skip().

Pallet Manager: importing pallet types from CSV

The file looks like this, with a header row:

Code,Description,Material,Length,Width,MaxWeight,UnitCost
EUR1,Euro pallet,Wood,1200,800,1500,9.50
UK1,UK standard pallet,Wood,1200,1000,1500,11.00
HALF,Half pallet,Plastic,800,600,500,14.25

We read each column into a textelement, skip the header, and set the fields with Validate so the table's own rules run. Reading into textelements, rather than fieldelements straight into the fields, lets us handle the enum and the decimal format ourselves and give a clear error for a bad row.

namespace BusinessBoxed.PalletManager;

xmlport 50100 "BBX Pallet Type Import"
{
    Caption = 'Import Pallet Types';
    Direction = Import;
    Format = VariableText;
    FieldSeparator = ',';
    TextEncoding = UTF8;
    UseRequestPage = false;

    schema
    {
        textelement(Root)
        {
            tableelement(PalletType; "BBX Pallet Type")
            {
                AutoSave = true;
                AutoReplace = true;

                textelement(CodeText) { }
                textelement(DescriptionText) { }
                textelement(MaterialText) { }
                textelement(LengthText) { }
                textelement(WidthText) { }
                textelement(MaxWeightText) { }
                textelement(UnitCostText) { }

                trigger OnBeforeInsertRecord()
                var
                    Material: Enum "BBX Pallet Material";
                    Number: Decimal;
                begin
                    LineNo += 1;
                    if (LineNo = 1) or (CodeText = '') then
                        currXMLport.Skip();

                    PalletType.Validate(Code, CopyStr(CodeText, 1, MaxStrLen(PalletType.Code)));
                    PalletType.Validate(Description, CopyStr(DescriptionText, 1, MaxStrLen(PalletType.Description)));

                    if not Evaluate(Material, MaterialText) then
                        Error(BadValueErr, MaterialText, PalletType.FieldCaption(Material), LineNo);
                    PalletType.Validate(Material, Material);

                    PalletType.Validate("Length (mm)", ToInteger(LengthText, PalletType.FieldCaption("Length (mm)")));
                    PalletType.Validate("Width (mm)", ToInteger(WidthText, PalletType.FieldCaption("Width (mm)")));

                    if not Evaluate(Number, MaxWeightText, 9) then
                        Error(BadValueErr, MaxWeightText, PalletType.FieldCaption("Max. Weight (kg)"), LineNo);
                    PalletType.Validate("Max. Weight (kg)", Number);

                    if not Evaluate(Number, UnitCostText, 9) then
                        Error(BadValueErr, UnitCostText, PalletType.FieldCaption("Unit Cost"), LineNo);
                    PalletType.Validate("Unit Cost", Number);

                    Imported += 1;
                end;
            }
        }
    }

    var
        LineNo: Integer;
        Imported: Integer;
        BadValueErr: Label '"%1" is not a valid value for %2 on line %3 of the file.', Comment = '%1 = Value from the file, %2 = Field caption, %3 = Line number';
        DoneMsg: Label '%1 pallet types were imported.', Comment = '%1 = Number of pallet types';

    trigger OnPostXmlPort()
    begin
        if GuiAllowed() then
            Message(DoneMsg, Imported);
    end;

    local procedure ToInteger(Value: Text; FieldCaptionText: Text): Integer
    var
        Result: Integer;
    begin
        if not Evaluate(Result, Value) then
            Error(BadValueErr, Value, FieldCaptionText, LineNo);
        exit(Result);
    end;
}

A few details. Evaluate(Number, Text, 9) reads the number in the standard (XML) format, with a full stop as the decimal separator, whatever the user's regional settings, which is what a CSV from another system will contain. Evaluate on an enum accepts the value's name, so "Wood" works. AutoReplace = true means re-importing the file updates existing pallet types instead of failing on a duplicate key. And because any Error rolls back the whole import, a file with one bad row imports nothing, and the user is told which line to fix.

Running it from an action

Add an action to the pallet type list page (actions were covered in part 6):

action(ImportPalletTypes)
{
    ApplicationArea = All;
    Caption = 'Import from CSV';
    ToolTip = 'Import pallet types from a comma-separated file with a header row.';
    Image = Import;

    trigger OnAction()
    begin
        Xmlport.Run(Xmlport::"BBX Pallet Type Import", false, true);
        CurrPage.Update(false);
    end;
}

Xmlport.Run(ID, ShowRequestPage, Import) runs it and asks the user for the file. To import from a stream you already have (an email attachment, a file from an API), declare an XMLport variable, call SetSource(InStream) and then Import(); for export, SetDestination(OutStream) and Export().

Which one do I need?

Common mistakes

• Calculations in the layout instead of the dataset. Put the logic in AL (ideally a codeunit) and keep layouts for presentation, or every layout has to repeat it.
• No UsageCategory or ApplicationArea, so nobody can find the report.
• Renaming dataset columns after release, breaking every layout users have built.
• Huge datasets for Excel layouts with every field "just in case". Output what the layout needs.
• Forgetting SetLoadFields and DataAccessIntent on read-only reports over big tables.
• Looping in AL where a query would do, reading thousands of rows one by one to produce a total.
• Using fieldelements straight into decimal or date fields for files from other systems, then getting different results depending on the user's regional settings.
• Not skipping the header row of a CSV, or not handling blank lines at the end of the file.
• Trying to remove or change existing parts of a report in a report extension. You can only add.

Where we are

Pallet Manager can now produce a pallet requirements report in Excel, answer "which pallet types are used most?" with a single query, and import pallet types from a CSV file. Next we open it up to other systems with API pages and API queries.

Previous: part 7, codeunits, AL basics, working with records and events. Next: part 9, API pages and API queries. Back to the series contents.

Want help with your first extension? Book a 30-minute call.

Related posts