Search code examples
reporting-servicesreportingservices-2005iif-function

Does the iif function compute both paths in SSRS or is it short-circuited?

I am trying to evaluate a Price per Kilo ($/Kg) based on sales of a product. This works fine if the product was acutally sold during the period specified. However if the product is not sold the Kg (the denominator) ends up being 0 (zero) and an error results. - Divide by Zero error.

I tried this

=iif(KgSold=0,0,Revenue/KgSold)

It appears that the iif function is calculating both the true and false results. How do I get around this.

Should I be using the switch function instead?

=switch(KgSold=0,0
        KgSold<>0,Revenue/KgSold)
Solution

  • You're right, it doesn't short circuit. That sucks.

    You'll have to do something like this:

    = Iif(KgSold = 0, 0, Revenue) / Iif(KgSold = 0, 1, KgSold )

    The switch function should also work.