BAQ Qty Rollup

Kinetic ERP
,
1

I have a BAQ that is pulling the Project ID, Part Num, Part Desc and a calculated field for ReqQty. I would like the ReqQty to rollup grouped by Project ID and Part Num to only show one line instead of multiple. Is there a quick way to get that to happen? I tried grouping the Project ID, Part and Desc fields but there was a Bad SQL statement error

image
image563×632 114 KB

2

In the query, click the grouping box for Project and Part. Then remove the ReqQty, and insert a calcualted field with the expression:

sum(ReqQty)
3

Our current calculated field for ReqQty is defined as: (case when QuoteMtl.FixedQty = ‘false’ then QuoteDtl.SellingExpectedQty * QuoteMtl.QtyPer else QuoteMtl.QtyPer end)

4

That is ok. Keep that. Oh, but you need to move all this to a subquery. Then at the top-level query add in your grouping fields and check them as grouped and add that calculated sum field.

5

So in the subquery have 3 fields and not the calculated field, and then in the top level have all 4 fields but group the non calculated ones?

6

Yes, subquery should have part, project and your calculated ReqQty. None grouped. The top level will pull in the values from this subquery and show part and project with grouped=true, and a new calculated sum field to sum that ReqQty you calculated in the subquery.

1 Like
7

In the top level, should the calculated req qty be pulled as well as the new calculated field for the sum of the req qty, or just the sum field?

8

Okay I got it to work. Thank you!

1 Like