my gutt tells me (without going the BAQReport/SSRS route) that this is not possible using any combination of baq / subquery or calculated field magic - but requires a dashboard customization to access the summary totals.
i imagine somewhere, somehow, this was already been done. and would love to see an example.
if someone is willing to share
regards
And if you use the subquery method, you will have to define the grouping at the BAQ level, so it won’t be dynamic. Is that what you are after?
What are you wanting to group by here?
the grouping would be by Part, Vendor or a similar entity like SO or Project
in the case i am looking at the dashboard grouping will not change.
I just don’t yet recognize how the sub-query gives me my result … but i will try it some.
it is possible. just start posting your query phrase when you get stuck.
You won’t be able to do the grouping in a grid, (that’s what I mean by not dynamic) But you can group them in the BAQ.
Your first sub will be what you have on the top, qty, price, and the extension for qty*price. Then you need the fields for what you want to group by.
the next sub will bring that as a table in and group by your criteria, summing up the quantities and the extended prices.
the next level up will bring in the summed up quantities and extended prices, and make a calculated field to divide them (like you have in your example)
It can probably be done with fewer levels, but that’s how I would attack it.
Hi Banderson,
i am not sure if i understand what you mean here, but let me suggest this -please correct me if i am wrong- you want the weighted average unit cost instead of the normal average for the unit cost counting the factor of produced qty NOT based on the occurrence no., i.e. you want to give more weight to the cost of 80-off more than the cost of 10-off, then do not calculate the average at the lower level, only get the two totals and do the avarage calculation on the higher level.
Lower level Query
Qty || Cost || Cost/Per Record(calculated as QTY / COST)
oooooooooooooooooooooooooooooooooooooooooooooooooo
80 || 800.00 || 10.00
80 || 800.00 || 10.00
80 || 800.00 || 10.00
10 || 10.00 || 1.00
Top Level Query Grouped by your criteria let say PartNum
oooooooooooooooooooooooooooooooooooooooooooooooooo
PartNum || TTL Produced Qty || TTL Cost || Average Cost/Per Unit for all records (calculated as TTL Cost / TTL Produced Qty)
oooooooooooooooooooooooooooooooooooooooooooooooooo
250 || 2410.00 || 9.64 = (2410/ 250)
oooooooooooooooooooooooooooooooooooooooooooooooooo
I think you might be able to do this with a window function query. You can see an example with the system BAQ named: zGSTR1-B2B_InvcDtl_IN . If you change the ICE.QueryHdr.CGCode to an empty string you can load the BAQ in the designer and see how Epicor created it. What you want is to sum it over the group something like:
SELECT CustNum, OrderNum, PartNum,
SUM(UnitPrice*OrderQty) OVER(PARTITION BY PartNum) AS SubTotal
FROM ERP.OrderDtl;
There is a ton of information on window functions here: https://windowfunctions.com/ also on Pluralsight or even Youtube Window functions in SQL Server - YouTube
correct
I don’t want an average of the averages
I want an average of the totals
since an average of the averages applied the same ‘weight’ to each occurrence
What are you actually wanting to display in the end result?
You can get what you want, I believe, by sub-query within sub-query. One to sum everything you want and return a single line, then an outer query to calculate on those totaled results. I wonder if your problem is that you actually want to see the rows that lead to the result?
If so, you can keep them in a BAQ by having a further query for the rows alone, again, and doing a union with the total.
Or, as you say, depending what your end goal is, you can do your calculations in a customization, which we’ve done a few times when people want to be able to see different results depending on their filtering.
This appears to be something like the coding customization option
OK - I will try to look into this
thanks
this sounds like an the uncustomizied option
OK thanks - I will look into this as well.
and reply back for all to see
Just checking - you talk about BAQs, but the way you mention summaries sounds like you’re meaning more specifically the results of a BAQ in a grid, when you summarise using the headers. In which case the answers are going to be quite different because the logic isn’t happening in the BAQ at all.
that’s exactly what I mean
the affects when you choose to “show summaries” and select average or sum on those columns
although IF combining multiple queries - without using “summaries” provides the same result -why not?
you should be able to do this. we have a few queries that sum/avg/whatever. then there is a main query to show the results.
As I understand it, when you summarise a grid, each column is totally independent.
In the BAQ itself, it’s perfectly possible to divide the sum of one column by the sum of another and use the resulting weighted average, but as you see, to be able to return the same thing in a summary means you need to provide a separate column to calculate on. It feels kind of unlikely you can provide a figure which will make sense to users on each line yet summarise to give the result you want.
Here’s a query you may want to investigate and compare:
select
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
[OrderDtl].[ExtPriceDtl] as [OrderDtl_ExtPriceDtl],
(OrderDtl.ExtPriceDtl / OrderDtl.OrderQty) as [Calculated_PricePer],
(sum(OrderDtl.OrderQty) over (partition by OrderNum order by OrderLine rows between unbounded preceding and current row)) as [Calculated_RunningQty],
(sum(OrderDtl.ExtPriceDtl) over (partition by OrderNum order by OrderLine rows between unbounded preceding and current row)) as [Calculated_RunningTotal],
(RunningTotal / RunningQty) as [Calculated_RunningPricePer]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.OrderNum = @OrderNum)
Daryl
Thank you very much.
Got what I needed with your and everyone else’s help …
select
[OrderDtl].[Company] as [OrderDtl_Company],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
[OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice],
[OrderDtl].[ExtPriceDtl] as [OrderDtl_ExtPriceDtl],
(sum(OrderDtl.OrderQty) over (partition by OrderDtl.Company order by OrderDtl.Company rows between unbounded preceding and current row)) as [Calculated_TotalRunQty],
(sum(OrderDtl.ExtPriceDtl) over (partition by OrderDtl.Company order by OrderDtl.Company rows between unbounded preceding and current row)) as [Calculated_TotalExtPrice],
(sum(OrderDtl.ExtPriceDtl) over (partition by OrderDtl.Company order by OrderDtl.Company rows between unbounded preceding and current row) / sum(OrderDtl.OrderQty) over (partition by OrderDtl.Company order by OrderDtl.Company rows between unbounded preceding and current row)) as [Calculated_TotalAvgPrice]
from Erp.OrderDtl as OrderDtl
Good news. Always nice to hear of a success.
YES
I often forget how the calculated BAQ fields support an extensive amount of “SQL” coding
So it was nice to see the solution come out of the BAQ instead of a customized dashboard.
It’s a simpler solution
Thanks again to everyone for their kind assistance.
