If we want to answer a simple question in Business Central - “How many items we have in each location?” then there aren’t that many great built in reports available. There is the standard “Items by Location” report that is not that great. It doesn’t offer exporting, doesn’t offer filtering, you can’t see item values etc.
So how do we create a better report?
To start designing a report the first thing we need to think about is that we need to calculate the inventory for each item in each location by summarizing the Item Ledger Entries. And there can be millions of Item Ledger Entries so it must be performant.
We can use a query to calculate the quantities in each location like this:
query 50101 "Items By Location"
{
QueryType = Normal;
elements
{
dataitem(Item_Ledger_Entry; "Item Ledger Entry")
{
column(Location_Code; "Location Code")
{
}
column(Item_No_; "Item No.")
{
}
column(Remaining_Quantity; "Remaining Quantity")
{
Method = Sum;
ColumnFilter = Remaining_Quantity = filter(> 0);
}
}
}
}
Its pretty simple query and not much useful by its own.
But we can use that query to fill a temporary table with a following structure:
table 50100 "Items By Location"
{
DataClassification = ToBeClassified;
TableType = Temporary;
fields
{
field(1; "Location Code"; Code[20])
{
DataClassification = ToBeClassified;
Caption = 'Location Code';
}
field(2; "Item No."; Code[20])
{
DataClassification = ToBeClassified;
Caption = 'Item No.';
}
field(3; "Item Unit Value"; Decimal)
{
Caption = 'Value per item';
}
field(4; "Quantity"; Decimal)
{
DataClassification = ToBeClassified;
Caption = 'Quantity';
}
field(5; "Description"; Text[100])
{
DataClassification = ToBeClassified;
Caption = 'Description';
}
field(6; "Item Total Value"; Decimal)
{
DataClassification = ToBeClassified;
Caption = 'Item Total Value In Location';
}
}
keys
{
key(Key1; "Location Code", "Item No.")
{
Clustered = true;
}
}
}
To actually fill this temp table we need a report:
report 50100 "Inventory By Location"
{
UsageCategory = ReportsAndAnalysis;
ApplicationArea = All;
DefaultRenderingLayout = DefaultLayout;
Caption = 'Inventory By Location';
dataset
{
dataitem("ItemsByLocation"; Integer)
{
column("Location"; ItemByLocationTemp."Location Code")
{
}
column("Item_No"; ItemByLocationTemp."Item No.")
{
}
column(Quantity; ItemByLocationTemp.Quantity)
{
}
column("Item_Unit_Value"; ItemByLocationTemp."Item Unit Value")
{
}
column(Description; ItemByLocationTemp.Description) { }
column(Item_Total_Value; ItemByLocationTemp."Item Total Value") { }
trigger OnAfterGetRecord()
begin
IF Number = 1 THEN
ItemByLocationTemp.FindFirst()
ELSE
ItemByLocationTemp.Next();
end;
trigger OnPreDataItem()
begin
ItemsByLocation.SetRange(Number, 1, ItemByLocationTemp.Count);
end;
}
}
requestpage
{
layout
{
area(Content)
{
group("Items By Location")
{
field(LocationFilter; LocationFilter)
{
ApplicationArea = All;
Caption = 'Location';
TableRelation = Location.Code;
}
}
}
}
}
rendering
{
layout(DefaultLayout)
{
Type = Excel;
LayoutFile = './ItemsByLocation.xlsx';
}
}
trigger OnPreReport()
var
ItemsByLocationQuery: Query "Items By Location";
Item: Record Item;
begin
if LocationFilter <> '' then
ItemsByLocationQuery.SetFilter(ItemsByLocationQuery.Location_Code, LocationFilter);
ItemsByLocationQuery.Open();
while ItemsByLocationQuery.Read() do begin
Item.SetLoadFields("Unit Cost", Description);
Item.SetFilter("No.", ItemsByLocationQuery.Item_No_);
if Item.FindFirst() then begin
ItemByLocationTemp.Init();
ItemByLocationTemp.Validate("Location Code", ItemsByLocationQuery.Location_Code);
ItemByLocationTemp.Validate("Item No.", ItemsByLocationQuery.Item_No_);
ItemByLocationTemp.Validate(Quantity, ItemsByLocationQuery.Remaining_Quantity);
ItemByLocationTemp.Validate("Item Unit Value", Item."Unit Cost");
ItemByLocationTemp.Validate("Item Total Value", ItemsByLocationQuery.Remaining_Quantity * Item."Unit Cost");
ItemByLocationTemp.Validate(Description, Item.Description);
ItemByLocationTemp.Insert(true);
end;
end;
ItemsByLocationQuery.Close();
end;
var
ItemByLocationTemp: Record "Items By Location";
LocationFilter: Code[20];
}
This report is Integer based report which gives us a nice generic report structure.
Now on the reports OnPreReport trigger we can use the query to fill out the temp table which is this part here:
trigger OnPreReport()
var
ItemsByLocationQuery: Query "Items By Location";
Item: Record Item;
begin
if LocationFilter <> '' then
ItemsByLocationQuery.SetFilter(ItemsByLocationQuery.Location_Code, LocationFilter);
ItemsByLocationQuery.Open();
while ItemsByLocationQuery.Read() do begin
Item.SetLoadFields("Unit Cost", Description);
Item.SetFilter("No.", ItemsByLocationQuery.Item_No_);
if Item.FindFirst() then begin
ItemByLocationTemp.Init();
ItemByLocationTemp.Validate("Location Code", ItemsByLocationQuery.Location_Code);
ItemByLocationTemp.Validate("Item No.", ItemsByLocationQuery.Item_No_);
ItemByLocationTemp.Validate(Quantity, ItemsByLocationQuery.Remaining_Quantity);
ItemByLocationTemp.Validate("Item Unit Value", Item."Unit Cost");
ItemByLocationTemp.Validate("Item Total Value", ItemsByLocationQuery.Remaining_Quantity * Item."Unit Cost");
ItemByLocationTemp.Validate(Description, Item.Description);
ItemByLocationTemp.Insert(true);
end;
end;
ItemsByLocationQuery.Close();
end;
This report also uses a Excel based layout so we can format the report how ever we like.
I like it like this:
There might be better ways but I think this approach is really performant and extendable. You can easily add also Lot No’s, Serial No’s or what ever information you need about your inventory.