Purchase Order Line Items Report

(0) Share

Report

Posted on by lytt

Hi,

How do I generate a PO line items report that will also show me the vendor, item, quantities and cost in Dynamics 365 Business Central?

For both open and closed purchase orders.

Thank you!

I have the same question (0)

All responses (20)

Answers (1)

Suggested answer

JAngle 133 on at

Like (2)

Report

If you are focussing on inventory based PO's there are some standard options like Vendor/Item Purchases or Inventory Purchase Orders. If not then you will need to explore outside of this with a reporting solution - Power BI or Jet Reports are popular. Having a custom report devised in BC is possible too.

If you don't have licenses for the above and do have Excel maybe using an OData feed you can produce what you need. "Status" is on the header so you would require logic to handle that. If you use the OData feed (check the web services page in BC for page 518 - add it if missing) make sure you use the below authentication in Excel:

If the above is a satisfactory pathway I would use power query in excel to merge a query to the purchase line and one to the purchase header like this:

Pages 50 and 518 as web services will result in a table like this:

Which you can do some nice Excel bits with like a pivot table:

Was this reply helpful? Yes No
Verified answer

Steven Renders 5,672 Moderator on at

Like (0)

Report

Build reports for Microsoft Dynamics 365 Business Central - Learn | Microsoft Docs

Use Power BI in Microsoft Dynamics 365 Business Central - Learn | Microsoft Docs

Was this reply helpful? Yes No
samwillm 142 on at

Like (1)

Report

Purchase Order Line Items Report: Need to any help you can visit www.dynamicssquare.co.uk/.../

Dynamics Square is a leading Microsoft Partner.

Was this reply helpful? Yes No
Suggested answer

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

Like (0)

Report

Hi, If you only want to use the standard features, I hope the link below will help you.

https://yzhums.com/8876/

In addition, it is possible to easily import data into Excel using the Open In Excel function on the page.

Hope this will help.

Thanks.
ZHU

Was this reply helpful? Yes No
lytt 5 on at

Like (0)

Report

Hi Josh,

I have added page 518 in BC web services but encountered the following authentication error:

Will I be able to add a dimension (ie. Project code) as one of the fields when I extract the report?

Thanks!

Was this reply helpful? Yes No
lytt 5 on at

Like (0)

Report

Hi Steven,

Thank you for the links you have shared.

However, I do not have access to Power BI and I have attempted to create a custom layout report with an existing RDLC report (Inventory Purchase Orders) but am not successful due to my lack of developer skills and knowledge. The Inventory Purchase Orders report is the closest to what I am looking for, and all I want to do is add a dimension field (Project code) to the report.

I am really looking for an inquiry function to extract such a report from Dynamics.

Was this reply helpful? Yes No
Suggested answer

Steven Renders 5,672 Moderator on at

Like (1)

Report

Actually Power BI is Free ;-)

Modifying the dataset of a report is not possible in the application, you need to develop a report extension, so you will need a developer to do that for you.

The best answer to your question, and you can do it completely for free is: Power BI and the standard api's.

Here's an example on how that works:

Did you know that the Business Central connector in Power BI supports related tables when using APIs? – think about IT

The API's also include the dimensions, so that should also work.

Finally, you can use Excel to connect to the Power BI dataset, if that is what you want:

Start in Power BI with Analyze in Excel - Power BI | Microsoft Docs

Was this reply helpful? Yes No
lytt 5 on at

Like (0)

Report

Hi ZHU,

I have referred to the link https://yzhums.com/8876/ and I think this is close to what I am looking for. However, I am finding it hard to execute the steps as per the link. Firstly, I do not think I have the design and developer access. I tried to force the page 516 but was unsuccessful. Is there a function like Dynamics AX where I can put in an inquiry for PO Line Items?

Thanks!

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (0)

Report

Hi

Josh idea and Zhu idea are the best route. If you can solve the connection issue with excel then you can use Josh idea (final result will be something like www.youtube.com/watch

For Zhu suggestion see this video usedynamics.com/.../

Do you have the same user and password in Excel as Business Central?

if you are on SaaS version you can test following free app in Sandbox. It is not exactly what you need but I thought to share it with you

appsource.microsoft.com/.../PUBID.nubetechspa|AID.nbt_exp|PAPPID.98ed8df0-3215-4025-a0a7-4de475d2c79b

Was this reply helpful? Yes No
lytt 5 on at

Like (0)

Report

Hi Josh,

I managed to resolve the authentication issue and was able to extract page 518 which is the Purchase Order lines. Is there a way to extract the Archived Purchase Order Lines?

Thank you!

Was this reply helpful? Yes No