Small and medium business | Business Central, N...

How to display location wise item in report in business central

(6) Share

Report

Posted on by Test user

321

Hello,

We want to display location wise item on report

Example:

Item no. Location inventory

100 East 1

100 West 7

101 east 20

102 east 30

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

CU05081638-4 4 on at

Like (1)

Report
Copy link

Link copied!

Hi,
It is easy to create this report by taking following three tables as dataitem.

1. Item
2. Location
3. Item Ledger Entry

In the Item Ledger entry Table for each and location combination you need to apply filter and sum of the remaining quantity field will be your inventory column.

Regards,
Niharika

1 people found this reply helpful.

Was this reply helpful? Yes No
TAHER_El_Mehdi 836 on at

Like (1)

Report
Copy link

Link copied!
report 50001 "Item Inv. by Location"

{

    Caption = 'Item Inventory by Location';

    UsageCategory = ReportsAndAnalysis;

    ApplicationArea = All;

    dataset

    {

        dataitem(InvByLoc; "Item Ledger Entry")

        {

            column(ItemNo; "Item No.") { }

            column(LocationCode; "Location Code") { }

            column(Quantity; "Remaining Quantity") { }

        }

    }

rendering

{

    layout(Rdlc)

    {

        Type = RDLC;

        LayoutFile = 'ItemInvByLocation.rdlc';

    }

}

}

Open ItemInvByLocation.rdlc in Report Builder.

Insert a Table (Tablix) bound to the dataset.

Create Row Groups:

Child group on =Fields!LocationCode.Value

Parent group on =Fields!ItemNo.Value

In the detail cell for quantity, use an expression:

=Sum(Fields!Quantity.Value)

1 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

Sumit Singh 11,757 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Hi,

You can use standard report by the name of "Items by location". Please refer below screenshots to access the report:-

Was this reply helpful? Yes No
Test user 321 on at

Like (1)

Report
Copy link

Link copied!

Thank you for your response.

@CU05081638-4,@TAHER Mehdi

Our report ,have already designed with dataitem as item only.

I am trying to used Item -location filter, flowfilter field,but i think it gets not stored the data directly.

Is any other way to do this?

@Sumit Singh How to used in our report this standard -Item matrix by location?

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 99,084 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Here is a standard query that can meet your needs.

Thanks

ZHU

Was this reply helpful? Yes No
Suggested answer

OussamaSabbouh 12,878 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hello,

Location-wise inventory is not stored on the Item table in Business Central; it is always calculated from Item Ledger Entries, grouped by Item No. and Location Code and summed by Quantity. So the correct and standard way to show your example is either to use the standard “Items by Location” report or build a simple custom report/query based on Item Ledger Entry, grouping by Item + Location. This is expected BC behavior and the only reliable way to get accurate inventory per location.

Regards,
Oussama Sabbouh

1 people found this reply helpful.

Was this reply helpful? Yes No
Suggested answer

Saurav.Dhyani 14,419 User Group Leader on at

Like (2)

Report
Copy link

Link copied!

Hi,

As ZHU Mentioned, please use Analysis Mode to retrieve data in the way you want.

Regards,

Saurav Dhyani

https://www.youtube.com/@sauravdhyani

Was this reply helpful? Yes No
Suggested answer

Dhiren Nagar 2,898 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi,

As mentioned by other fellow members you can use Analyze feature to create a view to see the inventory by location. It may become bit difficult to view if number of locations are many.

You can develop the report by taking Item and Location as your data item. So your report will iterate through both the tables records i.e. all item and location combination.

Now for each combination you need inventory which you can get by applying location filter on Item. You can do so as mentioned in below code.

I hope this helps.

Regards,

Dhiren.

report 80000 InventoryByLocation

{

UsageCategory = ReportsAndAnalysis;

ApplicationArea = All;

DefaultRenderingLayout = LayoutName;

dataset

{

dataitem(Item; Item)

{

dataitem(Location; Location)

{

column("ItemNo"; Item."No.")

{

}

column("LocationCode"; Location.Code)

{

}

column("Inventory"; Inventory)

{

}

trigger OnAfterGetRecord()

var

ItemLRec: Record Item;

begin

Item.Reset();

if ItemLRec.Get(Item."No.") then begin

ItemLRec.SetFilter("Location Filter", Location.Code);

ItemLRec.CalcFields(Inventory);

Inventory := ItemLRec.Inventory;

end;

end;

}

}

}

rendering

{

layout(LayoutName)

{

Type = Excel;

LayoutFile = 'InventoryByLocation.xlsx';

}

}

var

Inventory: Integer;

}

Was this reply helpful? Yes No