Answered by:
How to trap a divide by zero expression in a table
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
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

Thanks I'll give it a whirl
 Marked as answer by DaveKStLMo Tuesday, March 9, 2010 11:58 AM
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.bpmsbi.com Proposed as answer by Jeevan Dasari Monday, March 8, 2010 11:47 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

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

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

Thanks I'll give it a whirl
 Marked as answer by DaveKStLMo Tuesday, March 9, 2010 11:58 AM

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

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.bpmsbi.com