Order Release Value in BAQ

I am attempting to modify a Sales Report dashboard. The issue now is that the records are duplicating for multiple releases. Since it is showing the Line Extended and Total Price, if there is more than one release then the line records repeat and the sum of the total price for the order is wrong. Ex: Order/Line/Rel 17708/1/1 Total Price = 89, 17708/1/2 Total Price = 89… Order Total should be 89 but if you sum Total Price = 178.

How do others go about getting the value of Order Releases? It must pull through to invoice each release, so should I go through that side? Or do I have to do a manual calculated field to multiply the release qty and unit price? This seems like it would be a pretty common report, so I am surprised it doesn’t just show the value of each release in OrderRel.

The Sales team now currently tries to avoid entering releases at all because I believe a few reports are this way, but surely there is a way to do this appropriately.

Thanks!

It depends the requirement. OrderRel has information on shipped qty. So if you want to report based on open qty, then you need to refer to OrderRel. Else OrderDtl will do.

Sounds like your dashboard includes detail columns from both OrderDtl and OrderRel tables?

If so then yes, you’re mixing “dimensions” but the issues only reveal themselves when you have more than one release for the line(s).

Without more detail, I’m just guessing you need to add some aggregation somewhere.
Here is a screen shot of an example BAQ, where the OrderRel values are summarized in a subquery.

image

I think I am going to push the users who use this report to only pull of the line level. The report is pulling data from the OrderDtl table such as Ext Price, but then is duplicating because of the RelNum being added. I believe all they are interested in is total sales amounts so the lines are all that matter here. We just dont want them duplicating the rows.

I will meet with the users who use this report and discuss what is actually desired with this report. Thanks for your replies!