Search code examples
reporting-servicesssrs-2008ssrs-2008-r2

Why is this IIF comparison failing

I have to work with a database that returns string data that is both alpha characters but also uses numeric characters. For example 1 record may return the string"1000" the next may return the string "blah" All alpha strings will always be "Blah"

When a numeric string is returned I want to format it as currency and if it is alpha show "Blah".

This is what I have tried and it kind of works???

 =IIf(IsNumeric(First(Fields!MyField.Value, "DataSet1")), FormatCurrency(First(Fields!MyField.Value, "DataSet1")) ,"Blah")

If the MyField value is a string that is numeric such as "1000" it is formatted on the report as $1,000.00. However if the MyField value is "blah", the report shows "ERROR"

Can anyone explain why I am seeing this strange behavior?

Thanks Perry

Solution

  • Both conditions of an IIf expression are always evaluated so the problem occurs when FormatCurrency("blah") is evaluated even if IsNumeric("blah") return False.

    You can solve this problem using the same logic in a custom code where you can use a full If/Then/Else code.