SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ssrs 2010 iif statement


ssrs 2010 iif statement

Author
Message
wendy elizabeth
wendy elizabeth
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14783 Visits: 960
In an existing ssrs 2012 report, I need to modify the iif logic of the same line in a tablix. The line is used in an existing letter that is sent out to students in a large school district. The line is based upon the grade the student is at and if they speak Spanish or English. The default language is English if the family does not speak Spanish. I know this probably needs to be a complex iif statement, but I do not know how to setup one of these complex iif statements. Below is the general logic of how the iif statement should be setup:
1. If grade level is prek to Kindergarten, and
a. if the language = Spanish then prek to Kindergarten Spanish statement else
b. pre-k to kindergarten English message,
2. if grade level = 01 to 05 and
a. if the language = Spanish then grade 01 to grade 01 Spanish statement else
b. then grade 01 to grade English statement,
3. if grade level = 06 to 08 and
a. if the language = Spanish then grade 06 to 08 Spanish statement else
b. then grade level 06 to 08 English message,
4. if grade level = 09 to 12 and
a. if language= Spanish then grade 09 to 12 Spanish message else
b. then grade 09 to 12 English message.
Thus can you show me how to setup this iif statement?


pietlinden
pietlinden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52050 Visits: 16671
Are you allowed to create calculated fields in your report definition?
=CHOOSE(Fields!GradeLevel.Value,"Pre K","K","1st","2nd","3rd","4th","5th","6th","7th", "8th", "9th", "10th", "11th", "12th")

(You could alter it to include different messages for each grade... or create a more complex expression...)

=IIF(Fields!Lang.Value="Spanish","Spanish Message","Default English Message") & " - " & Fields!TextGradeLevel.Value


Then you just fork Spanish/Non-Spanish first, and then the Grades using CHOOSE()
IIF(FIelds!Lang.Value="Spanish",CHOOSE(Fields!GradeLevel.Value,"Spanish Pre K/K", "SpanishPre K/K", "1st", "2nd"...., "12th"),
CHOOSE(Fields!GradeLevel.Value,"English PreK/K", "English 1st", English 2nd", ....) )


(basically split the two languages with IIF() then use CHOOSE for the grades, Just use the same message for Pre-K and K.)
wendy elizabeth
wendy elizabeth
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14783 Visits: 960
Thanks for your answer so far! However, I am not allowed to use the chose option. How would the iif statement be setup if grade level was the first part and then followed by the language later on?
pietlinden
pietlinden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52050 Visits: 16671
"I can't use CHOOSE"? What kind of requirement is that?
You have to do it in SSRS, because you said you can't modify the underlying query. How do they suggest you do it? If they know how to do it, let them do it for you.

Can you create a table of (Language (PK1), Grade (PK2), Message) and then just join to it? Or would that be too easy?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search