Need help with syntax: Report Builder 3.0
Posted on 2011-03-02
Working on complex report using shared dataset. Trying to get at Total Claims Paid by Service Code and Gender and age group.
Row: Service Code
Totals and Subtotals are working for gender. THe problem is that I have data for AGE but not for Age Group. I want to establish, (I think), a new field called Age Group and define an expression for it that will calculate a sum by group for Age 0-3, Age 4-12, Age 13-17, Age 18-21, and Age 22+ (these are the desired groups/labels - assuming we have to do some grouping by age range?)
And then I am thinking I would place the Age Group at the top so the layout looks like so AgeGROUP Age 0-3 Age 4-12 Age 13 - 17 Age 18-21 Age 22+ etc.
Gender Male Female Total
Service Code T2016-UC 4 4 $ 40,000
I tried the syntax on the MS site - Syntax:
=IIF(First(Fields!Age.Value)<21,"Under 21",(IIF(First(Fields!Age.Value)>=21 AND First(Fields!Age.Value)<=50,"Between 21 and 50","Over 50")))
I am getting this error: The expression used for the calculated field '=IIF(First(Fields!Age.Value)<23,"Age 0-3",(IIF(First(Fields!Age.Value)>=4 AND First(Fields!Age.Value)<=12,"Age 4-12","Over 12")))' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.
How do I get the Age Group I need?
Thanks for your help.