How to handle #ERROR, NAN and INFINITY using expression in SSRS?

Question

1.00/5 (1 vote)

See more:

Hi I am wondering how to handle #ERROR, NAN and INFINITY in a single expression of SSRS reprot when my formula is (A/B)-1). I have used the following expression to handle NAN and INFINITY which worked perferctly fine.

=iif ( ( ((A/B)=0) OR B=0 ), 0, (A/B)-1) )

( Says that if A/B=0 or B=0 then display 0 else display (A/B)-1=0 )

With this code I am getting #ERROR in my report when it divides 0/0 as the value of a and b is zero. My problem is to handle # ERROR also in this expression. Please help.

Posted 20-Feb-13 17:51pm

Dhivya.J

Updated 10-Aug-21 12:05pm

v3

Add a Solution

2 solutions

Solution 1

Simply try:

VB

=iif(B=0, 0, (A/B)-1))

You need to make sure it's a valid number.

Posted 21-Feb-13 5:02am

Sandeep Mewara

Comments

Dhivya.J 26-Feb-13 7:47am

Thank you for answering. This only handles NAN, not infinity and #Error. I need to handle all these three(NAN,Infinity,#ERROR) in this expressions.

Sandeep Mewara 26-Feb-13 8:02am

Why so? For infinity, denominator has to be non-zero which is checked.
For #Error, it should be a valid expression, which it is.

To me all is handled, not sure what other thing you are looking for or facing.

Dhivya.J 27-Feb-13 0:52am

My report contains float values. For me the below expression worked.

=IIF( (B=0 OR A=0),0, ( ( A/IIF(B=0,1,B) )-1 ) ).

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Srinivasan Vinayagam · Accepted Answer · 2013-03-06T19:51:00

Solution 2

(A/IIF(B<=0,1,B))-1

The above solution will handle all issue, because 0/anything is zero, so it will be automatically handled. We have to handle divide by zero error only

Posted 6-Mar-13 19:51pm

Srinivasan Vinayagam

Comments

Dhivya.J 29-Mar-13 0:58am

Thank you:)