Eliminate #Error within Report Builder 3.0 when dividing by 0

Question

When the following formula divides by 0, all three of the below are returning #Error

=(Sum(Fields!Q2ActDelta.Value))/(Sum(Fields!Q1Actuals.Value))

=IIf(Sum(Fields!Q1Actuals.Value)=0,"",(Sum(Fields!Q2ActDelta.Value))/(Sum(Fields!Q1Actuals.Value)))

=IIf((Sum(Fields!Q1Actuals.Value))=0 Or (Sum(Fields!Q2ActDelta.Value))=0," ",(Sum(Fields!Q2ActDelta.Value))/(Sum(Fields!Q1Actuals.Value)))

What do I need to do to eliminate #Error? Why isn't report builder following the true condition?

Thanks!!

Answer 1

It's caused by the fact that ReportBuilder still tries to evaluate the false path, even if the condition resolves to true. The command below should solve your problem.

 =IIf(Sum(Fields!Q1Actuals.Value)=0,0,Sum(Fields!Q2ActDelta.Value)/IIf(Sum(Fields!Q1Actuals.Value)=0,1,Sum(Fields!Q1Actuals.Value)))

Answer 2

The above answer is missing one parenthesis it should be

 =IIf(Sum(Fields!Q1Actuals.Value)=0,0,Sum(Fields!Q2ActDelta.Value)/IIf(Sum(Fields!Q1Actuals.Value)=0,1,Sum(Fields!Q1Actuals.Value)))