SSRS Report Builder expression using IIF() function is unable to correctly evaluate conditions containing '<' and '>' characters

0
votes

I am designing a tablix report and need to check the value of a field and do some calculations accordingly. I am using the IIF() function to do the comparisons. The comparisons are correct for values which do not contain the "<" or ">" characters, however values starting with "<" or ">" characters are not evaluated properly.

Please refer to the example below for reproducing the scenario.

=iif(Fields!Allowance.Value = "Between $50 and $100", "True", "False")

If the value of the string based Allowance field is equal to "Between $50 and $100", the output is "True" else "False".

However the same code is unable to evaluate the next condition where the value of the Allowance field is "> $100" or "< $50".

=iif(Fields!Allowance.Value = "> $100", "True", "False")

Even if the value of the Allowance field is "> $100", the output is always "False".

I have also tried expressions like

=iif(Fields!Allowance.Value = "&gt; $100", "True", "False") and =iif(Fields!Allowance.Value = (CHR(62) + " $100"), "True", "False")

as i assumed the "<" and ">" signs might be treated as HTML when being evaluated by the expression, but still no luck.

There are no errors being thrown on runtime and the rendered value of Allowance field in the tablix is "> $100". Does anybody have any idea why this particular expression is not being evaluated correctly? Am i missing something here?

Thanks.

1
reporting-servicesreportbuilder3.0
It sounds like whatever is stored as the greater than symbol, it isn't chr(62) but some other character that looks like it. Try outputing the value of the field and look at in a hex editor to see what characters actually make up the field. - Chris Latta
Hex editor says the value is 0x3e, which maps to chr(62) - Shalvendra Sukul
Okay, maybe you have a leading or trailing space character. Try something like =iif(LTrim(RTrim(Fields!Allowance.Value)) = "> $100", "True", "False") - Chris Latta

1 Answers

0
votes

I'd double check what's in the database itself (I had an issue where someone had copied data which included an en and em dash - which was interpreted as a hyphen).

If that or what Chris Latta suggests doens't work, then I'd try using the Replace function to substitute another character in the formula.

You may also want to try using SWITCH and instead of using True or False, use 1 and 0. I've found that that sometimes gets me around text issues too.