Total OnHandQty

Epicor ERP 10
, ,
1

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).

What is the best way to approach this?

2

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.

1 Like
3

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:

Screenshot 2023-06-02 at 11.03.55 AM
Screenshot 2023-06-02 at 11.03.55 AM1920×1213 144 KB

4

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:

image
image861×753 35.9 KB

5

Use a windowing function,

sum(PartBin.QtyOnHand) over (partition by PartBin.PartNumber)

This will give you a column with the total for all of the parts on hand.

Also, I just guessed at the field names, so they might not be right. adjust as necessary.

This is kinda long, but goes into detail about how windowing functions work.

2 Likes
6

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!

Screenshot 2023-06-02 at 11.10.39 AM
Screenshot 2023-06-02 at 11.10.39 AM2356×930 314 KB

1 Like
7

Sorry, the windowing function isn’t what you want (I didn’t read the whole post, oops)

you would only use that if you want to keep the rows expanded seperately.

One helpful thing that would be cool for this is to put the bin numbers in a comma del list using string_agg()

string_agg(partBin.Bin, ',')

Then you can see the bins without messing up the totals.

1 Like
8

If he was doing something a little more complex and wanted to embed it in another subquery that would be awesome though.