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

Item Location odata link

(0) Share

Report

Posted on by Andrew Lane Lindy

I want to analyse Item availability by location e.g inventory, demand, free stock. I would like the flexibility to do this in Excel & Power BI is it possible to access this information through Odata ?

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Inge M. Bruvik 1,111 Moderator on at

Like (0)

Report

Yes, that is fully possible.

docs.microsoft.com/.../odata-web-services

If you connect your Power BI to Business Central it will show you a list of available API's you can chose from.

And if there is data you need that are not covered by the default API's you can publish additional pages or quires as webservice.

docs.microsoft.com/.../across-how-use-financials-data-source-powerbi

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,729 Super User 2025 Season 2 on at

Like (0)

Report

Hi, yes.

For example, in Power BI

You can access Odata in Websevice, and APIs.

With Power BI, there is another interesting use.

Set Business Central data to show in Excel Organization Data Types Gallery (Featured tables in Power BI)

https://yzhums.com/25234/

Hope this helps.

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

JAngle 133 on at

Like (0)

Report

100% possible based on what has been suggested thus far. Tempted to suggest you may need an additional query or api to handle your request in an optimum way. Qty. Reserved is not part of the existing api’s, which might be needed to get the true “free stock” number.

Was this reply helpful? Yes No
Andrew Lane Lindy 15 on at

Like (0)

Report

Thanks, the info. I can't see an Odata feed that does this out of the box (even just getting the stock figure by location without looking at free stock) and I have tried creating a new Odata feed using page 491 Items by Location, 492 Item Availability by Location, 515 Lines & 9231 Items by Location Matrix. One of these just returns zeros and the others report errors. I have only been using Dynamics for 2 months so forgive me if this is a stupid question.

Was this reply helpful? Yes No
Andrew Lane Lindy 15 on at

Like (0)

Report

Thanks, the info. I can't see an Odata feed that does this out of the box (even just getting the stock figure by location without looking at free stock) and I have tried creating a new Odata feed using page 491 Items by Location, 492 Item Availability by Location, 515 Lines & 9231 Items by Location Matrix. One of these just returns zeros and the others report errors. I have only been using Dynamics for 2 months so forgive me if this is a stupid question.

Was this reply helpful? Yes No
Suggested answer

Steven Renders 5,672 Moderator on at

Like (0)

Report

Those pages are doing calculations in AL code to show the inventory by location, using tep tables, and in odata they don't display any data indeed.

Best is for example to use the Item page and the Item Ledger Entry page as odata or api data sources in Power BI. Then use the Quantity or Remaining Quantity fields of the Item Ledger entry table in combination with for example Location Code, to display in a Matrix visual or in Power BI measures.

Even better is creating Query (API) objects, Item, Item Ledger Entry,... with exactly the columns you need. Queries support group by, so the resultset is smaller and so the data load is faster in PBI.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,729 Super User 2025 Season 2 on at

Like (0)

Report

Hi, You can try the Create Data Set feature in Web Service page, but keep in mind that not all existing pages support Odata well, please try to create an API page for best performance, if you are not familiar with the API page, please try to create a new page, and then publish to Web Service.

Hope this helps.

Thanks.

ZHU

Was this reply helpful? Yes No