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

Question

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) ```

Accepted Answer

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.

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

Solution

Problem