gberkeley
asked on
Need help with syntax: Report Builder 3.0
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
Columns: Gender
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.Valu
I am getting this error: The expression used for the calculated field '=IIF(First(Fields!Age.Val
How do I get the Age Group I need?
Thanks for your help.
Row: Service Code
Columns: Gender
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.Valu
I am getting this error: The expression used for the calculated field '=IIF(First(Fields!Age.Val
How do I get the Age Group I need?
Thanks for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I wish I had an answer to the other questions - is this the correct approach? Why am I getting the listed error messages? What exactly does the syntax need to look like for the specific example I provided?
Responsiveness was EXCELLENT. Information provided was great for that particular part of the inquiry.
Responsiveness was EXCELLENT. Information provided was great for that particular part of the inquiry.
ASKER
Problem Solved. See syntax below. I had to insert some language to the dataset's SQL query. used Edit as Text...created a dataset vs. embedded report.
SELECT
Claim.ClaimAmount
,Claim.Status AS [Claim Status] //etc - finish the SELECT statement, end with comma//
case when Age < 4 then '0-3' //insert this statement before the JOINS//
when Age > 4 and Age < 12 then '4-12'
when Age > 12 and Age < 18 then '13-17'
when Age > 17 and Age < 22 then '18-21'
when Age > 21 then '22+'
end Age_Range //this adds the field with the name you want to give it//
FROM
Claim
INNER JOIN ClaimService
ON Claim.ClaimID = ClaimService.ClaimID //.....etc - finish JOIN statements//
WHERE //Wrap up with any parameters//
ClaimService.StartDate >= @StartDate
AND ClaimService.StartDate <= @StartDate2
SELECT
Claim.ClaimAmount
,Claim.Status AS [Claim Status] //etc - finish the SELECT statement, end with comma//
case when Age < 4 then '0-3' //insert this statement before the JOINS//
when Age > 4 and Age < 12 then '4-12'
when Age > 12 and Age < 18 then '13-17'
when Age > 17 and Age < 22 then '18-21'
when Age > 21 then '22+'
end Age_Range //this adds the field with the name you want to give it//
FROM
Claim
INNER JOIN ClaimService
ON Claim.ClaimID = ClaimService.ClaimID //.....etc - finish JOIN statements//
WHERE //Wrap up with any parameters//
ClaimService.StartDate >= @StartDate
AND ClaimService.StartDate <= @StartDate2
That would be one of the first choices to do (check the Query).
Nice job done.
Nice job done.
ASKER
The Value expression for the text box ‘AGEGROUP’ refers to the field ‘Age’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. (AGE is the field name, soo that may be part of the problem?)
So I am confused. An I taking the correct approach to create an Age_Group field that is then populated with a single expression? The suggested syntax is an alternate proposed by MSFT. The query returned the expression IIf(First(Fields!Age.Value
I tried to recreate the AgeGroup field and used the syntax and I am getting a different error:
The grouping ‘GENDER’ has ‘AGEGROUP’ as a toggle item. Toggle items must be text boxes that share the same scope as the hidden item or are in a scope that contains the hidden item, and cannot be contained within the current report item unless current grouping scope has a Parent.
I can't find any indication that these fields are tied together so am not sure what it's doing here.