BAQ with Count causing Group By issue

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.

Thanks,
Eric

You will needed to setup a new calculated field with an IF statement. The calcualted field will need to be a nvarchar. Something like:

if(count(distinct(LaborDtl.EmployeeNum)>1,"Multiple",count(distinct(LaborDtl.EmployeeNum))

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)

Thanks

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.

image
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.