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?
get the data using a stored procedure and do these conditions there. then use ssrs at what it does best, reporting.
at some point iif can become unmanageable.
I agree with yosiasz - multiple IIFs in SSRS can be really hard to read and diagnose.
Instead, I usually just add a SQL "case when" statement in the query:
SELECT * (or whatever fields you want)
, CASE
WHEN grade BETWEEN prek AND kindergarten AND language = Spanish THEN 'prek to Kindergarten Spanish statement' ELSE 'pre-k to kindergarten English statement'
WHEN grade BETWEEN 01 AND 05 AND language = Spanish THEN '01 to 05 Spanish statement' ELSE '01 to 05 English statement'
END AS STATEMENT
This way, you avoid all the crazy nested IIFs in SSRS:
IIF('this and that1', 'then this1', IIF('this and that2', 'then this2', IIF('this and that3', 'then this3', 'else3'), 'else2'), 'else1')
Hope this helps.
that is why they call it immediate if, but people use it as long term if 
Thanks for your answer so far!
However, I am not allowed to change the basic query since it is a stored procedure that is used by lots of ssrs reports. The stored procedure is setup to get all the data needed for all the subreports that use this data.
The only option that I have is to use iif statements unless you have a better option.
Thus can you show me how to setup the complex iif statement?
your data types are hard to work with for example
If grade level is prek to Kindergarten
do you have numerical values for these?
same for grade 01 to grade 05
these are string values you cannot do ranges on ie grade between 1 and 5. to do this you have to convert 01 to 1 which further complicates your requirement.
If you are not allowed to change sprocs you need someone that can make that change.
To avoid using a long-winded IIF - what I would recommend and do myself is to create a separate dataset with the values I need based on the key values.
This dataset would contain a row with a key value to identify the grade and language - for example:
PreK-English
PreK-Spanish
Kindergarten-English
Kindergarten-Spanish
01-English
01-Spanish
02-English
02-Spanish
...
12-English
12-Spanish
The dataset would have 2 columns - the key value (StatementID) and the text (StatementText) to be returned.
You then utilize a LOOKUP to get the appropriate row from the dataset - the lookup will concatenate the grade with the language and returns the appropriate text value from the dataset.
=Lookup(Fields!Grade.Value & "-" & Fields!Language.Value, Fields!StatementID.Value, Fields!StatementText.Value, "Statements")
To create the dataset - you just need a query like this:
Select concat(ag.GradeID, '-', st.StatementLanguage) As StatementID, st.StatementText
From (
Values ('K', 'PreK')
, ('K', 'Kindergarten')
, ('05', '01')
, ('05', '02')
, ('05', '03')
, ('05', '04')
, ('05', '05')
, ('08', '06')
, ('08', '07')
, ('08', '08')
, ('12', '09')
, ('12', '10')
, ('12', '11')
, ('12', '12')
) As ag(GradeKey, GradeID)
Inner Join (
Values ('K', 'English', 'English version of text')
, ('K', 'Spanish', 'Spanish version of text')
, ('05', 'English', 'English Version of Text')
, ('05', 'Spanish', 'Spanish Version of Text')
, ('08', 'English', 'English Version of Text')
, ('08', 'Spanish', 'Spanish Version of Text')
, ('12', 'English', 'English Version of Text')
, ('12', 'Spanish', 'Spanish Version of Text')
) As st(StatementKey, StatementLanguage, StatementText) On st.StatementKey = ag.GradeKey
what happens when a new value comes in?
If there are other possibilities for language - then a small change to the lookup:
=Lookup(Fields!Grade.Value & "-" & Iif(Fields!Language.Value <> "Spanish", "English", "Spanish"), Fields!StatementID.Value, Fields!StatementText.Value, "Statements")
If new values are needed you would have to update the dataset to include the additional languages. I cannot see how there would be additional grades - but if that is possible then you would have to expand the dataset to include those other values.