• Replies 2 replies
  • Subscribers 337 subscribers
  • Views 25 views
  • Users 0 members are here
Related

SSRS divide by zero error

Former Member
Former Member $organization

Hi:

I am trying to avoid showing an error when dividing by zero in my SSRS report.  I can avoid it with an IIF statement that changes the denominator to 1, but this causes problems when the numerator is something other than zero.  I am trying to show the value as a percent.  Is there a way to do this so that it will return a zero if the denominator is zero?

 

This is what I have now:

=(Fields!ss_amount.Value+Fields!sb_amount.Value+Fields!ds_amount.Value+Fields!nr_amount.Value+Fields!rs_amount.Value)/IIF(Fields!capacity.Value=0,1,Fields!capacity.Value)

 

Thanks,

James

Parents
  • $organization

    It’s because the rendering engine evaluates all clauses in the IIF() expression even if it doesn’t get used.  If you search for “divide by zero SSRS” in Google, you’ll get a bunch of posts regarding this…This has always seemed to me to be the most elegant way of resolving the issue:

     

    http://www.sqlservercentral.com/scripts/Data+Warehousing/31510/

     

     

    +Ryan Creps

    +Tessitura Network

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of James Fairfield
    Sent: Wednesday, November 30, 2011 1:27 PM
    To: Ryan Creps
    Subject: [Tessitura Technical Forum] SSRS divide by zero error

     

    Hi:

    I am trying to avoid showing an error when dividing by zero in my SSRS report.  I can avoid it with an IIF statement that changes the denominator to 1, but this causes problems when the numerator is something other than zero.  I am trying to show the value as a percent.  Is there a way to do this so that it will return a zero if the denominator is zero?

     

    This is what I have now:

    =(Fields!ss_amount.Value+Fields!sb_amount.Value+Fields!ds_amount.Value+Fields!nr_amount.Value+Fields!rs_amount.Value)/IIF(Fields!capacity.Value=0,1,Fields!capacity.Value)

     

    Thanks,

    James




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Reply
  • $organization

    It’s because the rendering engine evaluates all clauses in the IIF() expression even if it doesn’t get used.  If you search for “divide by zero SSRS” in Google, you’ll get a bunch of posts regarding this…This has always seemed to me to be the most elegant way of resolving the issue:

     

    http://www.sqlservercentral.com/scripts/Data+Warehousing/31510/

     

     

    +Ryan Creps

    +Tessitura Network

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of James Fairfield
    Sent: Wednesday, November 30, 2011 1:27 PM
    To: Ryan Creps
    Subject: [Tessitura Technical Forum] SSRS divide by zero error

     

    Hi:

    I am trying to avoid showing an error when dividing by zero in my SSRS report.  I can avoid it with an IIF statement that changes the denominator to 1, but this causes problems when the numerator is something other than zero.  I am trying to show the value as a percent.  Is there a way to do this so that it will return a zero if the denominator is zero?

     

    This is what I have now:

    =(Fields!ss_amount.Value+Fields!sb_amount.Value+Fields!ds_amount.Value+Fields!nr_amount.Value+Fields!rs_amount.Value)/IIF(Fields!capacity.Value=0,1,Fields!capacity.Value)

     

    Thanks,

    James




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Children
  • Former Member
    Former Member $organization in reply to Ryan Creps (Past Staff Member)

    Thank you Ryan!

    That link was missing the function, but I found another that worked too:

    Public Function DivideBy(ByVal Exp1, ByVal Exp2)

    If Exp2 = 0 Then
    DivideBy = 0
    Else : DivideBy = Exp1 / Exp2
    End If
    End Function

    Then insert the expression
    =code.DivideBy(Field!ToBeDivided.Value,Field!DividingBy.Value)
    into any cell that has the potential for divide by zero problems.