I have a BAQ that lists all PartBin.OnHandQty per bin, but I want to get the sum of all OnHandQty for each part. So if we have three bins with the same part, those three needed added together. Then move on to the next part and do the same.
I realize that I can put this BAQ into a Dashboard and then use Group By and Summaries to see this number, but then the user has to click to expand each Part group one-by-one and sees multiple lines that they don’t really care about. The end goal here is to have something more usable, something that can be easily copied to Excel with the total OnHandQty for each part (one line for each part rather than multiple lines for each bin where the part is located).
It depends on the complexity of the overall BAQ. If its very simple then you can use Group By options in the Display tab and create a SUM(PartBin.OnHandQty) calculated column (which wouldnt be checked for group by checkbox).
If its a more complex BAQ then you can make a Sub-Select Sub-Query. Basically its a Sub-Query that returns SUM(PartBin.OnHandQty) but you dont join it, you instead reference it in the Main Query under Calculated_TotalOnHandQty with a value of {YourSubQueryName}.
You can look on forum, for examples how to configure the WHERE Clauses in that Sub-Query to use values from Main Query.
It is a simple BAQ. I think that I understand what you are saying but am not sure how to implement it. If I do this, then I get an error when trying to run the BAQ:
You will need to create a calculated field to show the total on-hand quantity using the sum() function. Then mark the Group By checkbox for every other column you don’t want to sum up. You will need to remove BinNum from your query.
This is the query we use for on-hand quantity by part and plant:
Ah ok. I was trying to use that Advanced Group By button down on the bottom instead of just checking the Group By boxes. It works now. Super simple now that I know how to do it! Thank you!