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)

Torse

Answer 1

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.

Waive

Thanks Robert. That does work. I am still getting the divide by zero error so now its just a matter of hunting the error down. – Torse

I found the where the error was happening. it was in the SQL and not in the report. – Torse

Tried the switch function for my dilemma and it also seems to evaluate all statements. – Neysa

@UserSmith: Then use the other variant I posted here. – Waive

Mine was a lot more complicated than the above. Although I found this solution....... works pretty well. =REPLACE(Fields!MYFIELD_CONCAT.Value & " ", ", ", "") – Neysa

Answer 2

This happens because in VBScript all conditions within an IIF will be evaluated first before any functionality occurs.

Iverson

Thanks Brian. I figured that was the case. I suspect that it is the case for all flavours of VB. – Torse

Note: full up VB.NET now has an IF() ternary operator that does short-circuit; but as implied, SSRS is using the VB Script or "VB for Applications" subset, and does not have that. – Kussell

Answer 3

Add the following to your code:

Public Function SafeDiv(byval num as double, byval den as double) as object

    If den = nothing then return nothing
    If den = 0 then return nothing

    return num / den

End Function

Then call

=Code.SafeDiv(Revenue,KgSold) 

in the text box epression

Animalize