SQLTeam.com | Weblogs | Forums

Expression in calculated field not working


#1

Hi, Hopefully someone can put me out my misery here :slight_smile: I'm trying to group the values from the EmployeesTotal field into a new field called EmployeesTotalGrouped. I've created the new field and entered the following expression:

=IIF(First(Fields!EmployeesTotal.Value)>=1 AND First(Fields!EmployeesTotal.Value)<=10,"1-10",(IIF(First(Fields!EmployeesTotal.Value)>=11 AND First(Fields!EmployeesTotal.Value)<=49,"11-49",(IIF(First(Fields!EmployeesTotal.Value)>=50 AND First(Fields!EmployeesTotal.Value)<=249,"50-249",(IIF(First(Fields!EmployeesTotal.Value)>=250,"250+",(IIf(IsNothing(Fields!EmployeesTotal.Value), "Unknown","None")))))))))

But SSRS is complaining that 'Previous and lookup functions cannot be used in calculated field expressions'. I am stuck. Can anyone please help?

Thanks in advance,