user3847080 user3847080 - 24 days ago 8
SQL Question

Eliminate #Error within Report Builder 3.0 when dividing by 0

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

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