High performant inventory report

The problem with the standard report

The standard"Items by Location" report in Business Central is convenient for quick checks but becomes a bottleneck as your item and ledger entry count grows. It iterates row-by-row through Item Ledger Entries without the aggregation and filtering optimisations you get from a dedicated query object, and its layout options are limited. For companies with thousands of items across multiple warehouses, this results in slow render times and a report that is difficult to extend.

Here is the standard report output that most users are familiar with:

The solution: a query-backed report with Excel layout

The approach is to use a Business Central Query object to summarise Item Ledger Entries at the database level, load the result into a temporary table in OnPreReport, and then use an Integer-based report to iterate through that temporary table. Coupling this with an Excel layout gives users a familiar, filterable spreadsheet they can pivot and sort without developer involvement.

Query: Items By Location

The query groups Item Ledger Entries by Item No. and Location Code and sums the Quantity field. This aggregation happens in SQL, meaning the report only ever loads one row per item-location combination into BC memory rather than every individual ledger entry.

query 50101"Items By Location"
{
 QueryType = Normal;
 Caption = 'Items By Location';

 elements
 {
 dataitem(ItemLedgerEntry;"Item Ledger Entry")
 {
 column(ItemNo;"Item No.") { }
 column(LocationCode;"Location Code") { }
 column(SumQuantity; Quantity)
 {
 Method = Sum;
 }

 filter(LocationCodeFilter;"Location Code") { }
 }
 }
}

Temporary table: Inventory By Location Buffer

The temp table mirrors the query output and holds one row per item-location combination while the report runs. Using a temp table decouples the data-loading step from the report rendering step and makes it easy to apply additional in-memory filtering or sorting before the report iterates the rows.

table 50100"Inventory By Location Buffer"
{
 TableType = Temporary;
 Caption = 'Inventory By Location Buffer';

 fields
 {
 field(1;"Item No."; Code[20]) { Caption = 'Item No.'; }
 field(2;"Location Code"; Code[10]) { Caption = 'Location Code'; }
 field(3; Quantity; Decimal) { Caption = 'Quantity'; }
 }

 keys
 {
 key(PK;"Item No.","Location Code") { Clustered = true; }
 }
}

Report: Inventory By Location

The report datasource is an Integer table, which lets us control exactly how many rows are rendered by setting Number to the count of rows in the temp table. OnPreReport runs the query, respecting the location filter from the request page, and fills the temp table. Each OnAfterGetRecord on the Integer dataitem advances a pointer through the temp table rows.

report 50100"Inventory By Location"
{
 UsageCategory = ReportsAndAnalysis;
 ApplicationArea = All;
 Caption = 'Inventory By Location';
 DefaultLayout = Excel;
 ExcelLayout = 'InventoryByLocation.xlsx';

 dataset
 {
 dataitem(Integer; Integer)
 {
 DataItemTableView = sorting(Number) where(Number = filter(1 .. 999999));

 column(ItemNo; TempBuffer."Item No.") { }
 column(LocationCode; TempBuffer."Location Code") { }
 column(Quantity; TempBuffer.Quantity) { }

 trigger OnPreDataItem()
 begin
 Number := TempBuffer.Count();
 TempBuffer.FindFirst();
 end;

 trigger OnAfterGetRecord()
 begin
 if Number > 1 then
 TempBuffer.Next();
 end;
 }
 }

 requestpage
 {
 layout
 {
 area(Content)
 {
 group(Filters)
 {
 field(LocationFilter; LocationFilter)
 {
 Caption = 'Location Filter';
 ApplicationArea = All;
 }
 }
 }
 }
 }

 trigger OnPreReport()
 var
 ItemsByLocation: Query"Items By Location";
 begin
 if LocationFilter <> '' then
 ItemsByLocation.SetFilter(LocationCodeFilter, LocationFilter);

 ItemsByLocation.Open();
 while ItemsByLocation.Read() do begin
 TempBuffer.Init();
 TempBuffer."Item No." := ItemsByLocation.ItemNo;
 TempBuffer."Location Code" := ItemsByLocation.LocationCode;
 TempBuffer.Quantity := ItemsByLocation.SumQuantity;
 if TempBuffer.Quantity <> 0 then
 TempBuffer.Insert();
 end;
 ItemsByLocation.Close();
 end;

 var
 TempBuffer: Record"Inventory By Location Buffer" temporary;
 LocationFilter: Text;
}

Result

The final report opens in Excel with one row per item-location combination, quantity pre-summed, and the full power of Excel filtering and pivot tables available to the user:

Conclusion

Using a Query object to aggregate Item Ledger Entries at the database level, combined with a temp table buffer and an Integer-based report with an Excel layout, produces a report that is both significantly faster than the standard alternative and straightforward to extend. Adding new columns, filters, or groupings is a matter of updating the query and the temp table — no changes to the report dataitem loop are needed.