Cool trick to run a Report on a Query in AL

During Days of Knowledge, Tonya Bricco-Meske showed us a very cool trick with queries and reports, check out the video to see the trick:

https://youtu.be/GrAEs1tNFxw

In this video, Erik shares a clever technique he picked up from a session by Tanya at Days of Knowledge Americas in Atlanta. Tanya demonstrated how to run a report based on a Query object in AL — something that isn’t natively supported by the report engine but can be achieved with an elegant workaround using the Integer table.

Why Would You Run a Report on a Query?

In Business Central, queries operate at the SQL level, which means aggregations and data joins can be significantly faster than doing the equivalent work at the AL level inside a report’s data items. If you’ve already built a query that retrieves exactly the data you need, it would be wasteful to rebuild that same dataset logic in a report. This trick lets you reuse your query as the data source for a report.

That said, this is a specialized technique — it doesn’t mean you should build all your reports this way. But when the shoe fits, it’s a beautiful solution.

The Problem

When you define a dataitem in a report, you can only specify a table as the source — not a query. There’s no way to directly point a data item at a Query object. So how do you get around that?

The Trick: Using the Integer Table

The solution is to use the Integer table as the data item source. The Integer table is a virtual system table that simply contains numbers. It’s commonly used as a “driver” table when you need to iterate over data that doesn’t come from a standard table — and that’s exactly what we need here.

The approach works like this:

  1. Declare a Query variable in the report.
  2. Use the Integer table as the data item.
  3. In the OnPreDataItem trigger, open the query.
  4. In the OnAfterGetRecord trigger, read the next query row. If there are no more rows, break out of the data item loop.
  5. Map your report columns to the query fields.

The Implementation

Here’s the complete report object that makes this work:

report 50100 "Query Report"
{
    Caption = 'Query Report';
    UsageCategory = ReportsAndAnalysis;
    ApplicationArea = all;
    DefaultLayout = Excel;
    ExcelLayout = 'queryreport.xlsx';
    dataset
    {
        dataitem(CLE; Integer)
        {
            column(A1; Q.Amount)
            { }
            column(A2; Q.Customer_Name)
            { }
            column(A3; Q.Document_Date)
            { }
            trigger OnPreDataItem()
            begin
                Q.Open();
            end;

            trigger OnAfterGetRecord()
            begin
                if not Q.Read() then
                    CurrReport.Break();
            end;
        }
    }
    var
        Q: Query "Cust. Ledger Entries";
}

How It Works Step by Step

The data item source: The dataitem(CLE; Integer) line uses the Integer table as a dummy source. The report engine will iterate over rows in this table, but we don’t actually care about the Integer values — we’re using it purely as a loop driver.

Opening the query: In the OnPreDataItem trigger, we call Q.Open(). This is where the query actually executes at the SQL level. By the time this call returns, the query has done its work and the results are ready to be read.

Reading rows: In the OnAfterGetRecord trigger, we call Q.Read() which advances to the next row in the query result set. The Read() method returns a Boolean — true if there’s a row to read, false when we’ve exhausted all results. When it returns false, we call CurrReport.Break() to stop the data item loop.

Mapping columns: The column definitions directly reference the query fields using dot notation: Q.Amount, Q.Customer_Name, Q.Document_Date. This is clean and straightforward.

Limitations to Be Aware Of

There are a few trade-offs with this approach:

  • Filtering: Setting filters on the query isn’t as seamless as with a standard data item. You can’t use the built-in report request page filtering. Instead, you’d need to create custom request page fields and feed those values into the query using SetFilter or SetRange before opening it.
  • Data links: You can’t use the report engine’s automatic data linking between data items, because from the report engine’s perspective, the only field in this data item is the Number field from the Integer table.
  • Combining with other data items: You can add other data items to the report, but linking them automatically to the query-driven data item won’t work through the standard mechanisms.

Conclusion

This is a simple, elegant trick that lets you leverage the power of AL queries — with their SQL-level performance for aggregations and joins — inside a report. The Integer table serves as a loop driver, while the query’s Open() and Read() methods handle the actual data retrieval. It’s not something you’ll use for every report, but when you’ve already built a query and need to print its output, this approach saves you from duplicating your data logic. Credit to Tanya for sharing this technique — be sure to check out her blog for more query tips and tricks.

Picture of hougaard

hougaard