Nested If Statements in SSRS

Question

Hi I am having trouble running the following Nested if statement in an SSRS expression

= SUM(IIF(UCASE(TRIM(Fields!UOP.Value))="Y", IIF(UCASE(TRIM(Fields!HED.Value))="Y",
  IIF(UCASE(TRIM(Fields!FROMB.Value))="RED",
IIF(UCASE(TRIM(Fields!TOB.Value))="BLUE",1,0),0),0))

Not sure if my parentheses are in the right position or not....

Basically I want to find a sum total of the expression only if all if statements are true and has the above values else not.

an alternative to nested if statements would be appreciated too if possible...thanks — user2628606
Yes i am...it says wrong number of arguments...i can't understand why. — user2628606

lc. lc. · Accepted Answer · 2013-07-29T01:38:18

Let's dissect your statement:

=SUM(
    IIF(UCASE(TRIM(Fields!UOP.Value))="Y", 
        IIF(UCASE(TRIM(Fields!HED.Value))="Y",
            IIF(UCASE(TRIM(Fields!FROMB.Value))="RED",
                IIF(UCASE(TRIM(Fields!TOB.Value))="BLUE",
                    1,
                    0),
            0),
        0)
    )

Your parentheses are unbalanced, and you are missing the false argument for the first IIF.

Alternatively, you can invert your conditions and use SWITCH instead, which would be a bit easier to read:

=SUM(Switch( _
    UCASE(TRIM(Fields!UOP.Value))<>"Y", UOPFalseValue, _
    UCASE(TRIM(Fields!HED.Value))<>"Y", 0, _
    UCASE(TRIM(Fields!FROMB.Value))<>"RED", 0, _
    UCASE(TRIM(Fields!TOB.Value))<>"BLUE", 0, _
    True, 1))

Or, since it appears your false value is always 0, you can just combine the conditions using And:

=SUM(IIF(And(And(And(UCASE(TRIM(Fields!UOP.Value))="Y", _
    UCASE(TRIM(Fields!HED.Value))"Y"), _
    UCASE(TRIM(Fields!FROMB.Value))="RED"), _
    UCASE(TRIM(Fields!TOB.Value))="BLUE"), 1, 0))

Nested If Statements in SSRS

1 Answers