Solved

Need help with syntax: Report Builder 3.0

Posted on 2011-03-02
6
2,843 Views
Last Modified: 2013-11-09
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.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.

0
Comment
Question by:gberkeley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 27

Accepted Solution

by:
planocz earned 500 total points
ID: 35018427
Try this...

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"))
0
 

Author Comment

by:gberkeley
ID: 35018692
Thanks for the response!   I tried the suggestion and am getting instead the following error:
    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)< 21, "Under 21", IIf((First(Fields!Age.Value)>= 21 And First(Fields!Age.Value)) <= 50, "Between 21 and 50", "Over 50"))  as a label, and when I went back to design and added the = before the expression, it changed to <expr> and threw the error noted above.  I guess I am looking for the syntax that will group the ages by the specified ranges and show a label for each range as a column group.
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.
0
 
LVL 27

Assisted Solution

by:planocz
planocz earned 500 total points
ID: 35019750
For each label and Age Group you will need to make just one IIF each.
Sample
Label :      "Under 21"
Textbox/cell:    =IIF(First(Fields!Age.Value)<21 , First(Fields!Age.Value, " " )
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Closing Comment

by:gberkeley
ID: 35020005
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.
0
 

Author Comment

by:gberkeley
ID: 35026734
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
0
 
LVL 27

Expert Comment

by:planocz
ID: 35027837
That would be one of the first choices to do (check the Query).
Nice job done.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
The purpose of this video is to demonstrate how to insert an Iframe into WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Open Page or Post…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question