ssrs 2008 using nested iif expressions

  • In an ssrs 2008 report, there is the following existing iif expression:
    =IIF(Fields!CorrespondenceLanguage.Value = "English", IIF(IsNothing(Fields!firstDay.Value), "Thursday, August 17, 2017", Fields!firstDay_WeekDayEnglish.Value + ", " + Fields!firstDay_MonthEnglish.Value + " " + Fields!firstDay_Day.Value + ", " + Fields!firstDay_Year.Value), "")

    I want to add the following to precede the existing IsNothing logic:
    IIF(INSTR("066", Fields!schoolType.Value) > 0 and IIF(IsNothing(Fields!firstDay.Value), "Wednesday, August 9, 2017",

    I would like the IIf expression to look like the following:
    =IIF(Fields!CorrespondenceLanguage.Value = "English",IIF(INSTR("066", Fields!schoolType.Value) > 0 and IIF(IsNothing(Fields!firstDay.Value), "Wednesday, August 9, 2017",  IIF(IsNothing(Fields!firstDay.Value), "Thursday, August 17, 2017", Fields!firstDay_WeekDayEnglish.Value + ", " + Fields!firstDay_MonthEnglish.Value + " " + Fields!firstDay_Day.Value + ", " + Fields!firstDay_Year.Value), "")

    This logic is basically not workiing. I basically want to check  if the school number = 066 and (IsNothing(Fields!firstDay.Value), I want the date to be "Wednesday, August 9, 2017". For any other school when there is not a first day set, I want the default to be
    Thursday, August 17, 2017.

    Thus can you show me how to change the iif expression to accomplish my goal?

  • wendy elizabeth - Thursday, June 1, 2017 4:16 PM

    In an ssrs 2008 report, there is the following existing iif expression:
    =IIF(Fields!CorrespondenceLanguage.Value = "English", IIF(IsNothing(Fields!firstDay.Value), "Thursday, August 17, 2017", Fields!firstDay_WeekDayEnglish.Value + ", " + Fields!firstDay_MonthEnglish.Value + " " + Fields!firstDay_Day.Value + ", " + Fields!firstDay_Year.Value), "")

    I want to add the following to precede the existing IsNothing logic:
    IIF(INSTR("066", Fields!schoolType.Value) > 0 and IIF(IsNothing(Fields!firstDay.Value), "Wednesday, August 9, 2017",

    I would like the IIf expression to look like the following:
    =IIF(Fields!CorrespondenceLanguage.Value = "English",IIF(INSTR("066", Fields!schoolType.Value) > 0 and IIF(IsNothing(Fields!firstDay.Value), "Wednesday, August 9, 2017",  IIF(IsNothing(Fields!firstDay.Value), "Thursday, August 17, 2017", Fields!firstDay_WeekDayEnglish.Value + ", " + Fields!firstDay_MonthEnglish.Value + " " + Fields!firstDay_Day.Value + ", " + Fields!firstDay_Year.Value), "")

    This logic is basically not workiing. I basically want to check  if the school number = 066 and (IsNothing(Fields!firstDay.Value), I want the date to be "Wednesday, August 9, 2017". For any other school when there is not a first day set, I want the default to be
    Thursday, August 17, 2017.

    Thus can you show me how to change the iif expression to accomplish my goal?

    Have you tried using a switch statement ???

  • Would you show me how to use a switch statement?

  • Follow link for syntax.

    https://www.sqlservercentral.com/Forums/1873806/Highlight-every-other-row-based-on-group

    Switch is somewhat like the SQL Case statement. Whatever condition it encounters first is used and the rest are ignored. It can have any number of conditions. Additionally if you want to have a "default" make it the last condition and make sure it is true all of time example "1=1".

  • Looked interesting so tried it both ways and I think both statements are correct or should be close enough for you to fix.

    =IIF(Fields!CorrespondenceLanguage.Value = "English" --***CONDITION 1***
    , IIF(IsNothing(Fields!firstDay.Value) and INSTR("066", Fields!schoolType.Value) = 66 --***CONDITION 1 TRUE AND CONDITION 2***
     , "Wednesday, August 9, 2017" --***CONDITION 2 TRUE***
     , IF(IsNothing(Fields!firstDay.Value) and INSTR("066", Fields!schoolType.Value) <> 66 --***CONDITION 2 FALSE AND CONDITION 3***
      , "Thursday, August 17, 2017" --***CONDITION 3 TRUE***
      , (Fields!firstDay_WeekDayEnglish.Value + ", " + Fields!firstDay_MonthEnglish.Value + " " + Fields!firstDay_Day.Value + ", " + Fields!firstDay_Year.Value)) --***CONDITION 3 FALSE AND END***
     ) --***CONDITION 2 END***
    , "") --***CONDITION 1 FALSE AND END***

    SWITCH(
      Fields!CorrespondenceLanguage.Value <> "English", "",
      (IsNothing(Fields!firstDay.Value) AND INSTR("066", Fields!schoolType.Value) = 66), "Wednesday, August 9, 2017",
      IsNothing(Fields!firstDay.Value), "Thursday, August 17, 2017",
      1=1, (Fields!firstDay_WeekDayEnglish.Value + ", " + Fields!firstDay_MonthEnglish.Value + " " + Fields!firstDay_Day.Value + ", " + Fields!firstDay_Year.Value)
       ) 

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply