Let’s create an Omni-Report, running on any table

In this video, I build a report that can run on multiple tables, defined at runtime. Very convenient when you want to perform the same process on multiple tables, such as running with both posted and unposted data, or perhaps on both customers and vendors. Check out the video:

https://youtu.be/MXx3BXAMLaY

In this video, Erik walks through building what he calls an “Omni Report” in Business Central — a report that isn’t locked into a single table at design time. Instead, the user selects which table to run against at runtime, applies filters dynamically, and then processes or prints the results. This technique is useful when you need a single report object that can operate on customers, vendors, sales documents, or any other table depending on the user’s choice.

The Problem: Reports Are Normally Locked to a Data Set

When you create a standard report in AL, you define the dataset with specific data items tied to specific tables. That’s fine for most scenarios, but sometimes you need more flexibility. Erik encountered this challenge twice in one week — situations where a user needed to select a table at runtime and then process whatever records matched their filters.

Use cases include:

  • Running the same processing logic on both unposted and posted sales documents
  • Operating on both customers and vendors with the same report
  • Building generic data tools that work across any table in the system

Starting with a Blank Report

Erik begins by creating a new report and initially sets it to ProcessingOnly = true — essentially a batch job. A processing-only report has no visual output; it just iterates through data and performs actions. Even with an empty dataset, the report is valid and can be run — it just doesn’t do anything yet.

Letting the User Select a Table

The first step is adding a request page where the user can specify which table to run against. This is done with a simple integer field for the table number:

requestpage
{
    layout
    {
        area(Content)
        {
            field(TableNo; TableNo)
            {
                trigger OnValidate()
                begin
                    Ref.Open(TableNo);
                    RecCount := Ref.Count();
                end;
            }
        }
    }
}

var
    Ref: RecordRef;
    TableNo: Integer;

When the user enters a table number (e.g., 18 for Customer, 23 for Vendor), the OnValidate trigger opens a RecordRef on that table. The RecordRef is the key to this whole approach — it’s a generic reference to any table, determined at runtime rather than compile time.

Adding Dynamic Filters with FilterPageBuilder

Knowing which table to use isn’t enough — the user also needs to filter records. Rather than asking users to type raw filter strings, Erik uses the FilterPageBuilder to present a proper filter dialog:

field(ViewString; ViewString)
{
    trigger OnAssistEdit()
    var
        Builder: FilterPageBuilder;
        v: Variant;
    begin
        Builder.PageCaption := 'Select ' + Ref.Name;
        v := Ref;
        Builder.AddRecord(Ref.Name, v);
        if ViewString <> '' then
            Builder.SetView(Ref.Name, ViewString);
        if Builder.RunModal() then begin
            ViewString := Builder.GetView(Ref.Name, true).Replace('VERSION(1) ', '');
            Ref.SetView(ViewString);
            RecCount := Ref.Count();
        end;
    end;
}

The Variant Trick

There’s an interesting quirk here. The AddRecord method expects a parameter of type “Table,” but a RecordRef is not a “Table” — the compiler won’t accept it directly. The workaround is to assign the RecordRef to a Variant first. A Variant is accepted where a “Table” type is expected, even though a RecordRef is not. It’s a bit of a quirk in AL’s type system, but it works reliably.

Handling the View String

The GetView method returns a string representation of the applied filters. Erik uses the named version (passing true) for readability, though he notes that the unnamed version (using field numbers instead of field names) is safer in some edge cases. He also strips out the VERSION(1) prefix that Business Central includes in view strings, since it’s just noise for the user:

ViewString := Builder.GetView(Ref.Name, true).Replace('VERSION(1) ', '');

Showing the Record Count

A nice usability touch is displaying how many records match the current filters:

field(RecCountControl; RecCount)
{
    Caption = 'Selected Records';
    Editable = false;
}

This updates whenever the user changes the table or applies new filters, giving immediate feedback — for example, “21 customers” before filtering, then “5 customers” after applying a “Balance <> 0″ filter.

Processing the Records: Two Approaches

Option 1: Processing Only (No Dataset)

If you don’t need to print or export, you can skip the dataset entirely and do all the work in the OnPreReport trigger:

trigger OnPreReport()
begin
    if Ref.FindSet() then
        repeat
            // Do your processing here
            message('%1', Ref.Field(1).Value);
        until Ref.Next() = 0;
end;

This is the simplest approach — a straightforward loop over the filtered records using the RecordRef. Erik demonstrates this by showing how the same code outputs customer primary keys when run on table 18, and vendor primary keys when run on table 23.

Option 2: Using the Dataset (For Printing/Export)

If you need actual report output — printing, PDF, Excel export — you need a dataset. Since you can’t bind a data item to a RecordRef at design time, Erik uses the Integer table as a driver:

dataset
{
    dataitem(Records; Integer)
    {
        column(c1; format(Ref.Field(1).Value))
        { }
        column(c2; format(Ref.Field(2).Value))
        { }

        trigger OnPreDataItem()
        begin
            Records.setrange(Number, 1, RecCount);
        end;

        trigger OnAfterGetRecord()
        begin
            if Records.Number = 1 then
                Ref.FindSet()
            else
                Ref.Next();
        end;
    }
}

Here’s how it works:

  1. OnPreDataItem: The Integer table is filtered to only iterate from 1 to RecCount (the number of matching records). Without this, the Integer table would try to iterate from negative two billion to positive two billion.
  2. OnAfterGetRecord: On the first iteration (Number = 1), it calls FindSet() to position the RecordRef on the first record. On subsequent iterations, it calls Next() to advance to the next record. This effectively synchronizes the Integer-driven loop with the RecordRef iteration.
  3. Columns: The columns use format(Ref.Field(n).Value) to extract values. Since Field().Value returns a generic (joker) type that can’t be used directly as a column, wrapping it in format() converts everything to text — which the report engine accepts.

The Complete Source Code

report 57100 "OmniReport"
{
    Caption = 'Omni Report';
    //ProcessingOnly = true;
    ApplicationArea = all;

    dataset
    {
        dataitem(Records; Integer)
        {
            column(c1; format(Ref.Field(1).Value))
            { }
            column(c2; format(Ref.Field(2).Value))
            { }

            trigger OnPreDataItem()
            begin
                Records.setrange(Number, 1, RecCount);
            end;

            trigger OnAfterGetRecord()
            begin
                if Records.Number = 1 then
                    Ref.FindSet()
                else
                    Ref.Next();
            end;
        }
    }

    requestpage
    {
        layout
        {
            area(Content)
            {
                field(TableNo; TableNo)
                {
                    trigger OnValidate()
                    begin
                        Ref.Open(TableNo);
                        RecCount := Ref.Count();
                    end;
                }
                field(ViewString; ViewString)
                {
                    trigger OnAssistEdit()
                    var
                        Builder: FilterPageBuilder;
                        v: Variant;
                    begin
                        Builder.PageCaption := 'Select ' + Ref.Name;
                        v := Ref;
                        Builder.AddRecord(Ref.Name, v);
                        if ViewString <> '' then
                            Builder.SetView(Ref.Name, ViewString);
                        if Builder.RunModal() then begin
                            ViewString := Builder.GetView(Ref.Name, true).Replace('VERSION(1) ', '');
                            Ref.SetView(ViewString);
                            RecCount := Ref.Count();
                        end;
                    end;
                }
                field(RecCountControl; RecCount)
                {
                    Caption = 'Selected Records';
                    Editable = false;
                }
            }
        }
    }

    var
        Ref: RecordRef;
        RecCount: Integer;
        TableNo: Integer;
        ViewString: Text;
}

Key Takeaways

  • RecordRef is the foundation — it allows you to work with any table dynamically at runtime without knowing the table at compile time.
  • FilterPageBuilder provides a native Business Central filter experience for dynamically chosen tables, so users get the same familiar interface they’re used to.
  • The Variant trick bridges the gap between RecordRef and methods that expect a “Table” type parameter.
  • Using the Integer table as a dataset driver lets you produce printable/exportable report output from dynamically determined data.
  • Wrapping field values in format() converts the generic joker type to text, making it usable in report columns.
  • The view string captures the complete filter state, and stripping VERSION(1) keeps it clean for display.

This “Omni Report” pattern is a powerful technique for building flexible, table-agnostic processing and reporting tools in Business Central. While it’s admittedly an unconventional approach, it solves real problems where you need a single report object to operate across multiple tables based on runtime decisions.

Picture of hougaard

hougaard