Problem Trapping Errors in Expression Calculating % Difference

# Problem Trapping Errors in Expression Calculating % Difference

• Sunday, April 29, 2012 6:52 PM

Hi All -

I am working on an expression which should calculate % Difference between 2 values. I only want to run the calculation when I am sure that the two values involved are legit and the divisor is not 0. If any of these conditions is not met, I want to show "N/A." When my data includes information that does not pass these tests, the expression below works fine when I leave the division out of the calculation but shows "#Error" when it is included. Any insight would be most appreciated.

TIA,

Sven

=iif(IsNumeric(Fields!TabA_UN_Depth.Value) = true AND IsNumeric(Fields!TabB_UN_Depth.Value) =

true AND Fields!TabB_UN_Depth.Value = 0,

((Fields!TabB_UN_Depth.Value-Fields!TabA_UN_Depth.Value)/Fields!TabB_UN_Depth.Value), "N/A")

### All Replies

• Sunday, April 29, 2012 6:58 PM

I believe it should be instead:

=iif(IsNumeric(Fields!TabA_UN_Depth.Value) = true AND IsNumeric(Fields!TabB_UN_Depth.Value) =

true AND Fields!TabB_UN_Depth.Value <> 0,

((Fields!TabB_UN_Depth.Value-Fields!TabA_UN_Depth.Value)/Fields!TabB_UN_Depth.Value), "N/A")

In other words, we need to make sure that the divisor is not 0.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Sunday, April 29, 2012 7:05 PM

Thanks for the reply but I think my code was a bad copy/paste. I was already using <> rather than = but I am still getting the "#Error" message.
• Sunday, April 29, 2012 9:48 PM
Moderator

Hi There

SSRS will evaluate your expression fully, Even though you want it to stop after the 'true' part of your iff statement it is failing in your expression when there is 0 values

I have put your expression and my expression side by side so that you can feel the difference

I hope this will helps

I am putting screenshot for your help

Many thanks

Syed Qazafi

--My Expressions
=iif(IsNumeric(Fields!TabA_UN_Depth.Value) = true
AND IsNumeric(Fields!TabB_UN_Depth.Value) = true
AND Fields!TabB_UN_Depth.Value <> 0,
((Fields!TabB_UN_Depth.Value-Fields!TabA_UN_Depth.Value)/iif(Fields!TabB_UN_Depth.Value=0,1,Fields!TabB_UN_Depth.Value)),"N/A")
=iif(IsNumeric(Fields!TabA_UN_Depth.Value) = true
AND IsNumeric(Fields!TabB_UN_Depth.Value) = true
AND Fields!TabB_UN_Depth.Value <> 0,
((Fields!TabB_UN_Depth.Value-Fields!TabA_UN_Depth.Value)/Fields!TabB_UN_Depth.Value), "N/A")

• Sunday, April 29, 2012 11:46 PM

Why do you need to check for IsNumeric? What is the type of these fields?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Sunday, April 29, 2012 11:54 PM
Moderator

Why do you need to check for IsNumeric? What is the type of these fields?

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Hi Naomi

I don’t believe the problem is with isnumeric though please see my response above

Many thanks

Syed

• Wednesday, May 02, 2012 6:03 AM
Moderator

Hi Gimcrack,

The cause of this error is that the IIf function always evaluates both the true part and the false part, even though it returns only one of them. This means both the true part and the false part need to be evaluated.

In order to resolve this issue, you need to use a nested IIf function to avoid the zero-divisor in any rate just like the expression gave by Syed.

Thanks,

Challen Fu

TechNet Community Support