IIF gives error, but shouldn't

This one's driving me nuts.

I'm trying to show a "variance from revenue budget" column in an otherwise functioning report. The basic formula is:

(((Fields!budget.value-Fields!actual.value)/Fields!budget.value)*-1)

which displays as:

Agent

Variance

Budget

Sales

John

20%

$80

$106

Mary

#Error

$0

$50

I want Mary to display 0%, but division by 0 gives an error. Fair enough. Let's test for a 0 value:

IIF(Fields!budget.value = 0,0,5)

Agent

Variance

Budget

Sales

John

5%

$80

$106

Mary

0%

$0

$50

Okay, the test works. So this should work, right:

IIF(Fields!budget.value = 0,0,(((Fields!budget.value-Fields!actual.value)/Fields!budget.value)*-1))

Nope:

Agent

Variance

Budget

Sales

John

20%

$80

$106

Mary

#Error

$0

$50

How about reversing the terms:

IIF(Fields!budget.value > 0,(((Fields!budget.value-Fields!actual.value)/Fields!budget.value)*-1),0)

Nope:

Agent

Variance

Budget

Sales

John

20%

$80

$106

Mary

#Error

$0

$50

So I have an IIF statement where either terms works alone, the test works, and I still get #Error displayed in the cell. I've also tried going back to the SQL and putting the NULL values back to NULL and using IsNothing(Fields!budget.value) as my test--same result. Any other ideas?

IIF(Fields!budget.value > 0,(((Fields!budget.value-Fields!actual.value)/Fields!budget.value)*-1),0)

For the Fields!actual.value, is it null?

|||

IIF is a function call of the VB runtime, hence all arguments are evaluated before the function is invoked. In your case, the devision by Fields!budget.value is triggered a division by zero exception.

You can use the following expression:

= -1 * iif(Fields!budget.Value <=0, 0, (Fields!budget.Value-Fields!actual.Value) / iif(Fields!budget.Value > 0, Fields!budget.Value, 1))

Alternatively, you can write an IF - ELSE statement in a custom code function to avoid the division by zero and call it from the expression.

-- Robert