I thought that this would be easy but apparently not so.
I’m writing a BAQ that reports the number of Operations that have NOT been completed on a job. I’ve tried this several different ways but this is the last formula I came up with:
"When JobOper.OpComplete = ‘FALSE’ then count( JobOper.OpComplete) else end"
Any idea’s?
If I remember correctly, count will include false as it already exists. Are you getting the total number of operations instead of the ones complete?
I used sum and a condition to convert the bool to an int. Different case but should work for operations.
sum( case when JobHead.JobComplete=‘true’ then 1 else 0 end )
I understand what you did, I think I have done something similar in another BAQ (thank you for reminding me). However I’m getting varied counts. For example:
I have 6 jobs for the same part. Each job has the same number of operations (6 OP’s). One job for instance has 1 open OP, but I am receiving a count of 17. The next job has the same number of OP’s (6 OP’s), and the count is coming back as 9 OP’s.
Any thoughts on what it might be counting?
Thank you!
Oh I reread it, makes sense why your solution works for counting because of the when. The issue may be in the grouping. Do you have the job number as part of the grouping so it isn’t totaling open ops by part number?
I just thought of that too…I’m looking at my joins and groupings now…
You may want to use the ProdComplete and SetupComplete fields instead. In my experience these are more reliable and in line with what our operators report in MES. OpComplete is a calculated field maintained by Epicor and I have seen it’s results be unpredictable.
This might help!?
Count(*)over(partition by JobOper.JobNum)
Above statement will show count for all the operations which are open but job is marked completed.
And if you want to show results as in a single row, you might need to make a subquery out of it which would look like this:
Just noticed your reply - thank you so much!
I will be trying this shortly…
Yes, it is name of SubQuery
Could you explain the following (highlighted in yellow):
I’m not familiar with the count(*)over(partition…and what it is doing.
Also I am only looking at Open Jobs so I really don’t need the JobComplete counter. I’m assuming all I have to do is leave it out but if I do how will the affect the syntax of the formula?
I’m using the BAQ Designer so that might be why I’m confused 
Count statement is a window function in SQL that counts the number of rows in a result set within specific partitions.
For eg: if you have a job which has 10 operations out of which 7 are open, it will show 7 as a result. Hence counting the open operations.
Yeah! if you are looking at the open jobs then probably set the table criteria as JobOper.JobComplete = 0. I amended that in snapshot below.
I am also using BAQ designer, it’s just I placed snapshot of QueryPhrase.
But here is the breakdown:
SubQuery:
Add a TopLevel query: Mark it as distinct.
Bring JobHead table and join the subquery:
This should help.
This helped out a lot. What I’m experiencing now are issue’s with the other fields I’m bringing into the BAQ.
I’m going to build it now step-by-step and will most likely have better results. 
Thank you for showing me this formula:
Count(*)over(partition by JobOper.JobNum)
I’m sure I will be using variations from here on out.
Grateful for your assistance!
Could you show what issue you are facing while bringing other fields? Should be pretty straightforward.
Perhaps I can share with you tomorrow - I have to move on to something else now and maybe a fresh pair eyes when I get in tomorrow will help!
Thanks!
