none
How to trap a divide by zero expression in a table RRS feed

  • Question

  • There is a situation where there can be a divide by zero in an expression.  The expression returns a NaN which is okay until I export the report to Excel and this effects 2 rows in the return for each Nan.  How do I trap or correct a divide by zero expression so exports work okay?  I tried an IIF Statement, but unsuccessfully.

    Thanks

    Dave
    Monday, March 8, 2010 10:42 PM

Answers

  • The Isnull function in SSRS is ISNOTHING.  So you can try this

    =IIF(Fields!Denominator.Value=0 OR ISNOTHING(Fields!Denominator.Value), 0, Fields!Numerator.Value / Fields!Denominator.Value)

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    • Marked as answer by DaveKStLMo Tuesday, March 9, 2010 12:00 PM
    Monday, March 8, 2010 10:56 PM
  • Thanks I'll give it a whirl
    • Marked as answer by DaveKStLMo Tuesday, March 9, 2010 11:58 AM
    Monday, March 8, 2010 11:00 PM

All replies

  • Hi Dave,

    You can generally do it with iif:

    =iif(Fields!Denominator.Value = 0, 0, Fields!Numerator.Value / Fields!Denominator.Value)

    However, it could be more complex - please show me your code to get a more exact answer.

    Boyan Penev --- http://www.bp-msbi.com
    • Proposed as answer by Jeevan Dasari Monday, March 8, 2010 11:47 PM
    Monday, March 8, 2010 10:45 PM
  • What if the field is Null, similar to

    =iif(Fields!Denominator.Value = 0, 0, Fields!Numerator.Value / Fields!Denominator.Value)

    Is there a Fields!Denominator.Value = Null or IsNull
    • Proposed as answer by Jeevan Dasari Monday, March 8, 2010 11:47 PM
    Monday, March 8, 2010 10:55 PM
  • The Isnull function in SSRS is ISNOTHING.  So you can try this

    =IIF(Fields!Denominator.Value=0 OR ISNOTHING(Fields!Denominator.Value), 0, Fields!Numerator.Value / Fields!Denominator.Value)

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    • Marked as answer by DaveKStLMo Tuesday, March 9, 2010 12:00 PM
    Monday, March 8, 2010 10:56 PM
  • Also, beyond this, sometimes even this approach can throw an error, since the expression isn't necessarily fully read from left to right like we might read it.  You might need to verify the value in Denominator a second time at the end like this:

    =IIF(Fields!Denominator.Value=0 OR ISNOTHING(Fields!Denominator.Value), 0, Fields!Numerator.Value / IIF(Fields!Denominator.Value=0 or ISNOTHING(Fields!Denominator.Value),1,Fields!Denominator.Value))

    Note that it replaces it with 1.  Ultimately this will never be calculated, but the reason you might need to put it in there is to make sure that an error isn't thrown during build.

    Aaron Jarboe

    -Remember to mark as an answer if this post has helped you.
    • Proposed as answer by Boyan Penev Tuesday, March 9, 2010 1:26 AM
    Monday, March 8, 2010 11:00 PM
  • Thanks I'll give it a whirl
    • Marked as answer by DaveKStLMo Tuesday, March 9, 2010 11:58 AM
    Monday, March 8, 2010 11:00 PM
  • Try this dude. I had the same problem after doing this it cleared.

    =IIF((fields!fieldName.Value)=

    Nothing ,0,(fields!fieldName.Value)/SUM(fields!fieldName.Value, "DatasetName"))

    Any issues let me know.


    Dasari
    • Proposed as answer by Jeevan Dasari Monday, March 8, 2010 11:47 PM
    Monday, March 8, 2010 11:07 PM
  • I have used this in the past and it did work as expected. It is unfortunate that SSRS does not always go correctly over the expression and produces an error in a branch, which should be clean of nulls.

    Boyan Penev --- http://www.bp-msbi.com
    Tuesday, March 9, 2010 1:27 AM