BAQ Running Sum

[DaveOlender]

Does Anyone know if there is a way to do a running sum in BAQ’s or maybe a sample?

Thanks,
DaveO

[SteveFossey]

What do you mean a “running sum”, what’s the equivalent in regular SQL?

You mean a field that adds the previous field to increment itself?

A normal sum is just…

[DaveOlender]

Mr. Steve: Yes a running sum is a field that is incremented for each record and represents the total of all the previous records.

I see a sql reference on the “inter-web” SUM (Fields.OrderDtl.Qty.Value) OVER (ORDER BY Fields!OrderDtl.OrderNum.Value) AS RunningQtyTotal

can that be done in the BAQ environment?

DaveO

[Asz0ka (Adam Szoka)]

Yes it can, as a calculated field.

I’ve used something similar to get a running total of inventory over time:

SUM(ActTransQty) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranNum ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

[DaveOlender]

Mr. Adam:Thank you - that works.

You are the MAN!

DaveO

[Banderson (Brandon Anderson)]

I think the BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING part might not be needed, or maybe overly specific. I think by default it uses only the previous row.

There’s some more good info in this post about how that code works.

Perform a calculation on BAQ Summary Fields

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:…