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.
select
[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].[JobNum],
[JobOper].[AssemblySeq],
[JobOper].[OprSeq],
[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],
[JobOpL2].[JobOperL2_JobNum],
[JobOpL2].[JobOperL2_AssemblySeq],
[JobOpL2].[JobOperL2_OprSeq],
[JobOpL2].[JobOperL2_OpCode],
[JobOpL2].[JobOperL2_EstProdHours]
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
10 Likes
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:
SUM(APINVHED.DOCINVBAL) OVER (PARTITION BY VENDOR.VendorID ORDER BY APINVHED.DUEDueDate)
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,
thanks
This is the statement in a calc field gives an error
SELECT
ROW_NUMBER() OVER (PARTITION BY InvcHead.InvoiceNum)
something about a “Possible Unauthorized Query”
the SUM(1) actually gives the same error - but still runs ??
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.
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:
@@VERSION
this is a fairly recent install - i think it’s sql2012 or later for sure