BAQ Running Total

Any way to create a running total and show it next to each record on a BAQ?
I have a BAQ that pulls in all jobs for a resource and sorts it by a custom “start time sequence” field. I want to do a running total of the estimated production hours next to each job to show how many hours away the estimated completion is.

1 Like

Here is an example SQL for a BAQ that is returning a running total column “SumEstProd”.
uses two JobOper subqueries and a top level query to combine them. Guessing there are other/better ways to do this too.

[JobOpL2].[JobOperL2_Company] as [JobOperL2_Company],
[JobOpL2].[JobOperL2_JobNum] as [JobOperL2_JobNum],
[JobOpL2].[JobOperL2_AssemblySeq] as [JobOperL2_AssemblySeq],
[JobOpL2].[JobOperL2_OprSeq] as [JobOperL2_OprSeq],
[JobOpL2].[JobOperL2_OpCode] as [JobOperL2_OpCode],
[JobOpL2].[JobOperL2_EstProdHours] as [JobOperL2_EstProdHours],
(sum( JobOpEstProd.JobOper_EstProdHours )) as [Calculated_SumEstProd]
from (select
[JobOperL2].[Company] as [JobOperL2_Company],
[JobOperL2].[JobNum] as [JobOperL2_JobNum],
[JobOperL2].[AssemblySeq] as [JobOperL2_AssemblySeq],
[JobOperL2].[OprSeq] as [JobOperL2_OprSeq],
[JobOperL2].[OpCode] as [JobOperL2_OpCode],
[JobOperL2].[EstProdHours] as [JobOperL2_EstProdHours]
from Erp.JobOper as JobOperL2
where (JobOperL2.JobComplete = false)) as JobOpL2
inner join (select
[JobOper].[Company] as [JobOper_Company],
[JobOper].[JobNum] as [JobOper_JobNum],
[JobOper].[AssemblySeq] as [JobOper_AssemblySeq],
[JobOper].[OprSeq] as [JobOper_OprSeq],
[JobOper].[EstProdHours] as [JobOper_EstProdHours]
from Erp.JobOper as JobOper
where (JobOper.JobComplete = False)
group by [JobOper].[Company],
[JobOper].[EstProdHours]) as JobOpEstProd on
JobOpL2.JobOperL2_Company = JobOpEstProd.JobOper_Company
and JobOpL2.JobOperL2_JobNum = JobOpEstProd.JobOper_JobNum
and JobOpL2.JobOperL2_AssemblySeq = JobOpEstProd.JobOper_AssemblySeq
and ( JobOpEstProd.JobOper_OprSeq <= JobOpL2.JobOperL2_OprSeq )

group by [JobOpL2].[JobOperL2_Company],
order by JobOpL2.JobOperL2_Company, JobOpL2.JobOperL2_JobNum, JobOpL2.JobOperL2_AssemblySeq, JobOpL2.JobOperL2_OprSeq

Yes, something like this should work in a calculated field…
You can use the "SUM " function from SQL with the “OVER” clause.

Here is an example from PartTran, but it works the same in yours… just change the “partition by” and “OrderBy” fields to your needs.

SUM(PartTran.TranQty) OVER (PARTITION BY PartTran.PartNum ORDER BY PartTran.TranNum)


For more info, read: Data with Bert


Hi Tim,

epicor version 9.05.701 (apologies - i should really make a new post for E9)

Any idea what the BAQ syntax would be for a running total in E9? I can’t get it to work…
I have tried making the APINVHED table a summary table and then turning it off.

I am using the following:


Does someone know the syntax to get the similar ROW_NUMBER() instead of a field.
when i try to use ROW_NUMBER() to get 1, 2, 3, 4, 5, … i get errors.
although SUM(1) works - it gives a total number not a sequence 5, 5, 5, 5, 5,


This is the statement in a calc field gives an error


something about a “Possible Unauthorized Query”

the SUM(1) actually gives the same error - but still runs ??:slight_smile:

Hi @amaragni, you should use ‘Order by’ instead of ‘partition by’ i.e. Row_Number() over (Order by APInvHed.InvoiceNum)

‘Partition by’ will restart the count for each value in the partition.

1 Like

This works for me. What is your full query? Hard to see what isn’t written. :slight_smile:

Select a.PartNum, a.RevisionNum, a.RevShortDesc
  From [PartRev] a
  Inner Join ( 
      Select partnum, SysRevID, RevisionNum,
          ROW_NUMBER() OVER (PARTITION BY partnum ORDER BY  ApprovedDate DESC) As _RowNum
      From [PartRev]  where Approved = 1 and EffectiveDate <= getdate() 
              ) b On b.partnum = a.partnum and a.RevisionNum = b.RevisionNum
                  Where 1 = 1 and b._RowNum = 1 
order by a.PartNum, a.RevisionNum
1 Like

ok - the issue with the security - i suppose - was a syntax error ;
i.e.i was including the “SELECT” keyword.

The calculated field works perfectly when i use THIS code.

ROW_NUMBER() OVER (PARTITION BY InvcHead.InvoiceNum Order BY InvcHead.InvoiceNum)

(notice the SELECT keyword is EXCLUDED from the syntax)
i suppose the "“Possible Unauthorized Query” is an indicator of possible SQL insertion? IDK

Thank you guys for your help

1 Like

Your assumption is correct. BAQ Designer does not allow any potentially dangerous constructs in a calc field expression, like SELECT, INSERT, etc.

1 Like

thank you DK, KN and GN

Just wondering… what version of MS SQL?
ORDER BY can throw an error when used with OVER PARTITION.
I don’t think the statement above would work until… MS SQL 2012?

When I don’t know the SQL server details, I can add a calculated field to get the version - e.g. nvarchar statement:

this is a fairly recent install - i think it’s sql2012 or later for sure