How can I use multiple iif in SSRS report?

Question

I have an IIF expression I am trying to use ...

=iif(sum(Fields!Actual.Value) > 0,"Increased",

iif(sum(Fields!Actual.Value) < 0,"Decreased",

iif(sum(Fields!Actual.Value) = 0,"No Change","None")))

or iif(isnothing(sum(Fields!Actual.Value)),"N/ap","None")

This is throwing me an error. Is there any other workaround for this?

Answer 1

In cases like this it's much easier to use SWITCH. Switch uses pairs of expressions to evaluate and values to return. It's much easier to read and requires no nesting normally.

So for you example you can do something like this.

=SWITCH (
         SUM(Fields!Actual.Value) > 0, "Increased",
         SUM(Fields!Actual.Value) < 0, "Decreased",
         SUM(Fields!Actual.Value) = 0, "No Change",
         ISNOTHING(SUM(Fields!Actual.Value)) , "N/ap",
         True, "None"
        )

The True at the end basicaly acts like an ELSE, it captures anything that does not match previous expressions.

SWITCH stops when it finds the first match so depending on result, you may have to alter the order or each check but start with this and see where you get.