Shipment Value for Power BI Report

(0) Share

Report

Posted on by nbs3

Hi,

I am having difficulty finding the total dollar value of items shipped by Customer. When I look at Posted Sales Shipments, I don't see a dollar value for the shipment. I tried linking to Posted Invoice Lines but there is not match.

Additionally, I tried getting at this information via the Item Entry Ledger and can find the value of the shipment but there is no link to the Customer.

How can I get access to this information? I have searched everywhere but have had no luck. You help is greatly appreciated.

I have the same question (0)

All responses (9)

Answers (1)

MahGah 15,529 on at

Like (0)

Report

Hi

Quick question: how customer capture the shipping cost? do they add it as a line in Posted Invoice Lines or ...?

Thanks

Was this reply helpful? Yes No
Suggested answer

JAngle 133 on at

Like (0)

Report

The item ledger entry table has fields for “Source Type” and “Source No.”. Both aren’t in the pages or query objects as standard. Adding them with an extension would be the most sensible thing to do. Forming the required relationship with the other pages will impact on performance

Was this reply helpful? Yes No
nbs3 67 on at

Like (0)

Report

Yes, shipping costs are line items on Posted Invoice Lines.

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (0)

Report

Hi

I know you can add "Source No" to the Web service.

Go to Web service then select the page that you use in Power BI (for item ledger entry is 38) then Action -> New Document -> Create Data set. Then in new page move to the page that you can select "Edit an existing dataset" . In the next page again select the same page. Then find the "Source No" and select and publish. I could not see Source Type here but you may see it.

This way you have the Source No for report.

Was this reply helpful? Yes No
Suggested answer

MahGah 15,529 on at

Like (0)

Report

Maybe I am missing something here since I never done this before.

In "Posted Sales Invoice Line" you have Sell-To Customer No. In addition you have "Document NO" which is an invoice No. Then either link/merge one of these fields with customer/invoice should provide the data you need. Unless you have a setup as Bill-to customer and you use Bill-To Customer in your Power BI. In this case you need to link Invoice Line to Header and find the Bill-to customer No then use that number.

But I could be wrong here.

Was this reply helpful? Yes No
nbs3 67 on at

Like (0)

Report

Thanks Josh, this seems like the best approach. I am unfamiliar with these fields. How do they lead you to the Customer number so I can link them to the Customer page in Power BI?

Was this reply helpful? Yes No
Robert Jolliffe 885 on at

Like (0)

Report

Just an FYI - We've created a set of Queries that already get all this information out specifically because the out of the box Power BI queries don't.

If you're interested let me know. You can Teams or email me at robert@sabrelimited.com

Was this reply helpful? Yes No
Suggested answer

nbs3 67 on at

Like (1)

Report

For anyone with the same issue I was able to get the information I needed by linking Posted Sales Shipments to Posted Sales Invoices by the invoice number and then connecting the Posted Sales Invoices to the Customer Card via the customer number.

Was this reply helpful? Yes No
Verified answer

Marco Mels on at

Like (0)

Report

Great job. Thank you for sharing final outcome.

Was this reply helpful? Yes No