Problem Trapping Errors in Expression Calculating % Difference

Question

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")

Answer 1

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

Answer 2

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.

Answer 3

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

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

--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")
--your expression
=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")

Answer 4

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

Answer 5

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

Answer 6

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