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?

What is the or doing in there? The above is mean to be returning a scalar value, not a boolean. Also, why tag SSRS 2012 *and Reporting Services 2005 (the latter of which has been unsupported for 5~ years). — Larnu

Alan Schofield Alan Schofield · Accepted Answer · 2020-07-14T16:53:00

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.

How can I use multiple iif in SSRS report?

1 Answers