none
how to handle "divide by zero" exceptions in my expressions? RRS feed

  • Question

  • I've got a report where an error introduced by a divide by zero calculation causes the report display to change. It breaks my backcolor expression and also keeps my embedded chart from showing. What I would like to do is put in some logic to handle this error because I know at some point in time bad data will end up in the table that my report runs from.

    Can someone tell me how I can handle a error in a calculated column?

    I've attached screenshots of the issue.

    http://img231.imageshack.us/img231/5388/ssrsissue.png

    Here's the switch statement that controls the backcolor:

    =switch(Fields!Read_Rate.Value <= 90, "Red", Fields!Read_Rate.Value > 90 and Fields!Read_Rate.Value < 95, "Yellow", Fields!Read_Rate.Value > 95, "Green")

    I tried to add another clause to this switch for Fields!Read_Rate.Value.ToString = "Infinity", "Orange" but it apparently did not work..

    I am still a SSRS Noob so any help here would be appreciated. I put all this work into this report getting drilldown behavior right and one single zero breaks it. Sigh. I don't really have control over the datastore's table constraints either so I can't fix it that way.

    • Edited by Vegeta4ss Thursday, March 25, 2010 7:07 PM fixed hyperlink
    Thursday, March 25, 2010 7:04 PM

Answers

  • =iif(Fields!numberPackages.Value=0,0,Fields!numberReads.Value/Fields!numberPackages.Value)

     

     ofcourse you can always change it as per your need. but it is just an idea.


    mark it as answer if it answered your question :)
    • Marked as answer by Vegeta4ss Thursday, March 25, 2010 7:53 PM
    Thursday, March 25, 2010 7:38 PM

All replies

  • =switch(Fields!Read_Rate.Value is nothing or Fields!Read_Rate.Value = 0, "Black", Fields!Read_Rate.Value <= 90, "Red", Fields!Read_Rate.Value > 90 and Fields!Read_Rate.Value < 95, "Yellow", Fields!Read_Rate.Value > 95, "Green")
    mark it as answer if it answered your question :)
    Thursday, March 25, 2010 7:07 PM
  • =switch(Not isnumeric(Fields!read_read.value) or Fields!Read_Rate.Value is nothing or Fields!Read_Rate.Value = 0, "Black", Fields!Read_Rate.Value <= 90, "Red", Fields!Read_Rate.Value > 90 and Fields!Read_Rate.Value < 95, "Yellow", Fields!Read_Rate.Value > 95, "Green")
    mark it as answer if it answered your question :)
    Thursday, March 25, 2010 7:11 PM
  • also your condition on 95 is incorrect because you put < 95 or > 95, what about if it is 95. just a hint.
    mark it as answer if it answered your question :)
    Thursday, March 25, 2010 7:21 PM
  • It looks like the error might be with the calculation that determines the read rate, not with the Switch statement governing the background color.  Your switch statement has nothing to do with a divide by 0 error.  Is Fields!Read_Rate.Value a field that is calculated within your dataset definition?

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    Thursday, March 25, 2010 7:25 PM
  • I was just coming back to add more info about that field. Lots of responses already, thanks for the help Parry2k and Aaron Jarboe!

     

    Read_Rate is a calculated column that is not located in my view, it is created as a field in the report's dataset.  Here's the expression behind it:

    =Fields!numberReads.Value/Fields!numberPackages.Value

    Thursday, March 25, 2010 7:35 PM
  • =iif(Fields!numberPackages.Value=0,0,Fields!numberReads.Value/Fields!numberPackages.Value)

     

     ofcourse you can always change it as per your need. but it is just an idea.


    mark it as answer if it answered your question :)
    • Marked as answer by Vegeta4ss Thursday, March 25, 2010 7:53 PM
    Thursday, March 25, 2010 7:38 PM
  • This did it. Thanks so much Parry2k!
    Thursday, March 25, 2010 7:53 PM