9/18/2014

Divide by Zero error in SSRS reports


This is a common error when we divide one field's value with another fields value or if second field contains 0 or null then we will be getting #Error issue in our reprot so this error can be handeled in two ways:

1. use IIF condition to avoid this divide by 0 issue:
                -> we can use IIF function of SSRS in expression to avoid this issue as suppose we have tow fields which we are using for calculation as Fields!Hours.Value / Fields!Qty.Value so if Fields!Qty.Value contains 0 then it will give #Error so write your expression as:

                 IIf(Fields!Qty.Value = 0, nothing,Fields!Hours.Value/Fields!Qty.Value)
             
Or Fields!Qty.Value contains Null vlaue then use below expression:

                IIf(ISNothing(Fields!Qty.Value), 0,Fields!Hours.Value/Fields!Qty.Value)

2. if both (0 or null) scenarios can be occured in all reports so we can write a custom code to use in the expression
To add a custom code function for the division (in Report -> Report Properties -> Code):

     Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
        If second = 0 Then

            Return 0

        Else

            Return first / second

        End If

    End Function

Then, modify the expression accordingly:

= IIF(Fields!Qty.Value = 0, "None", Code.Divide(Fields!Hours.Value, Fields!Qty.Value))
This will help us to get resolve #Error issue while dividing by zero.