I have a BAQ on the LaborDtl table. It has a calculated field “count(distinct(LaborDtl.EmployeeNum)” where it is returning on a job on an operation how many different resources recorded labor.
I would like for it to say “Multiple” if it is > 1 and the EmployeeNum if it is = 1.
I am just not able to wrap my head around how to display this.
You will needed to setup a new calculated field with an IF statement. The calcualted field will need to be a nvarchar. Something like:
Actually, the distinct part might not work in the formula. You may need to push the distinct part out to the subquery options.
Post a copy of your BAQ here and I will take a look.
Here’s what I got.
JobSummaryV2.baq (56.2 KB)
In your top level query, add the calculated nvarchar field. The formula should be:
iif(SubQuery2.Calculated_EmpCount >1,'Multiple', SubQuery2.Calculated_EmpCount )
I am getting a server side SQL error that I can’t find on my end. But this code should work. I will let you know if I figure out that error.
ok so SubQuery2.Calculate_EmpCount is an INT so i would have to convert to a string. but then this gives me ‘Multiple’ or 1. I want to replace the 1 with the value of the employeeNum.
Where it stays Not Multiple it should read 432 (the number of the distinct employee that entered a labordtl record)
Oh I missed that! In this case you will need a 2 step query. Add in a third subquery that lists the labordetails without aggregating them into a count. So for jobs with multiple labor entries this subquery would return all the emp ids that it would normally count.
Then in your top level query, write a calculation that looks at the value like we just did. Instead of returning the count, return the linked value from the new subquery you just made.
iif(SubQuery2.Calculated_EmpCount >1,'Multiple', SubQuery5.EmpBasic1_EmpID )
Strangely I keep getting a server side error. I think it is from the “count distinct” part of your formula. But I can’t quite get it working. Either way, that code should get you close!
Yeah I am still getting the group by issue
and if I add it to the group by then I lose the count
For some reason your group by fields are all greyed out on my end. Strange! Try adding the emp name to the subquery 5 group by only. That shouldn’t affect your count in subquery2. I linked my subquery 5 to subquery 2 at the top level using all the field available.
so I found the server error. I have some calculated fields doing division and there are some instances of dividing into and by zero. solved that with a case statement.