SSRS IIF Statement showing #Error when value is non numeric

Question

I have a value that will either be a decimal or string. Sample

0.41
0.91
"0 / 2"
0.75

My current expression is =IIF(IsNumeric(Fields!currentRate.Value), Format(CDBL(Fields!currentRate.Value), "P2"), Fields!currentRate.Value)

This properly returns the decimals formatted as a percentage, however the strings are only showing #Error. I've tried messing with various logic in the IIF statement and using a Switch instead. However the decimals always properly show as a percent, while the string only shows #Error.

Is it possible to display both numeric and string values in the same column while maintaining formatting on the numeric value?

Answer 1

The error relates to the CDbl function throwing an exception when trying to convert columns that are strings to a number. Yes, I know you're checking if it is numeric first but IIF is not a language construct, it is a function and as a function it evaluates all its parameters before passing them to the function. This means that both the True and False parameters get calculated even though one will be discarded and when it calculates CDbl on a string it throws an error.

Try the Val function. It has the benefit of not throwing errors when it gets passed non-numeric data - it just does the best it can to convert it to a number.

=IIF(IsNumeric(Fields!currentRate.Value), Format(Val(Fields!currentRate.Value), "P2"), Fields!currentRate.Value)

Answer 2

For anyone else who stumbles upon this question. Changing my formatting from using CDBL to VAL allows this to work properly.