15
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 ?
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
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.
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.
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.
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.
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)
Hope this helps.
Thanks.
ZHU
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
#1
Sohail Ahmed
691
#2
YUN ZHU
646
Super User 2025 Season 1
#3
Mansi Soni
495
Share
Report
All responses (
Answers (
