Problem:
you thought you wrote an if condition to handle “divide by zero” errors in SSRS but still get the run time error?
Example. my expression is:
[code language=”sql”]
=IIF(Fields!denominator.Value=0,0,
CINT(Fields!numerator.Value/Fields!denominator.Value))
[/code]
Note: if you do not have integers to divide and you allow data conversion to double, it would show on SSRS as “infinity” instead of “#Error”.
Solution:
Let’s understand why does it do it?
So even though you are using IIF condition, the vb IIF condition evaluates every function in every expression before it renders and that’s why the “False” condition that I have in my condition gets run which results in #Error.
Ok, armed with that knowledge, let’s solve the problem.
So here’s a modified version of the expression, have a look:
See what we did there! We added one more IIF condition in the “false” condition of the parent IIF.
[code language=”sql”]
=IIF(Fields!denominator.Value=0,0,
CINT(Fields!numerator.Value
/IIF(Fields!denominator.Value<>0,Fields!denominator.Value,1)))
[/code]
That should solve the problem:
There’s also an alternative to this especially if you have a lot of expressions that does this. You can write your custom code and call it SSRSDIVIDE or you can come up with a better name! Here’s a post that talks about how to do that: http://salvoz.com/blog/2011/11/25/ssrs-checking-for-divide-by-zero-using-custom-code/
Conclusion:
In this post, we saw how to solve the divide by zero errors in SSRS.
Thanks. it worked.
That’s great! I am glad this was useful.
This is great. Thank you for the expression. I encountered another problem with the calculation though. Now the partial pallets are returning 0.0 instead of 0.5. Why would this be the case if the formula returning a floating point number?