I have a column in a report that calculates a variance between two other columns and displays a percentage.
Some of the columns end up with a zero which would result in a divide by zero error.
So I wrote the following IIF statement that should have handled these situations and just filled in a zero:
=iif(Fields!RBC_Prev_Year.Value=0,0,(Fields!RBC.Value-Fields!RBC_Prev_Year.Value)/Fields!RBC_Prev_Year.Value)
The problem is, well it didn’t work. My report would still end up with an #Error value in the column. After some searching I found the solution on this website.
Essentially even though I was trying to handle the divide by zero issue with the IIF statement reporting services still evaluated each part of the statement before displaying the results which causes the error.
By re-writing it like this:
=iif(Fields!RBC_Prev_Year.Value=0,0,(Fields!RBC.Value-Fields!RBC_Prev_Year.Value)/
iif(Fields!RBC_Prev_Year.Value=0,1,Fields!RBC_Prev_Year.Value))
It works! Weird, but oh well.