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.

1 comment:

  1. Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing
    Msbi Training
    Msbi Online Course

    ReplyDelete

Tableau interview questions and answers for experienced professionals

  Tableau Interview Questions and Answers for experienced professional 1. What is TABLEAU? Tableau  is the powerful and fastest visualizing ...