SSRS 2012 Divide By Zero Error

Question

I've read through the answers and updated my expression and I continue to get #Error in the row that has a 0 for the budget.

All the percentages are being calculated correctly.

=IIF(SUM(Fields!CM_Budget.Value,"Name")=0,0,SUM(Fields!MTD_Actual.Value,"Name")/SUM(Fields!CM_Budget.Value,"Name"))

niktrs niktrs · Accepted Answer · 2017-07-06T05:22:42

This is because SSRS always evaluates both expressions. You have to break your expression in two parts.

=IIF(SUM(Fields!CM_Budget.Value,"Name")=0,0,SUM(Fields!MTD_Actual.Value,"Name"))
/
IIF(SUM(Fields!CM_Budget.Value,"Name")=0,1,SUM(Fields!CM_Budget.Value,"Name"))

An alternative could be using custom code like below

Public Function Divider (ByVal Dividend As Double, ByVal Divisor As Double)
If IsNothing(Divisor) Or Divisor = 0
  Return Nothing
Else
  Return Dividend/Divisor
End If
End Function

Then you can write your expression like:

=Code.Divider(SUM(Fields!MTD_Actual.Value,"Name"),SUM(Fields!CM_Budget.Value,"Name"))

I usually prefer the second way to do it, to make my expressions more readable

SSRS 2012 Divide By Zero Error

1 Answers