March 4, 2018 at 7:24 pm
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?
March 5, 2018 at 1:02 am
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.)
March 5, 2018 at 11:20 am
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?
March 5, 2018 at 11:40 am
"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?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply