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