SSRS IIF Statement showing #Error when value is no

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

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

Answer 2

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 3

IIF() is a function in SQL Server. As such, it returns a value whose type is specified by its arguments. As explained in the documentation:

Returns the data type with the highest precedence from the types in true_value and false_value. For more information, see Data Type Precedence (Transact-SQL).

If one of the two values is a number, then that has precedence. The assumption is that both are numbers. In other words, an expression in SQL only returns one type for all rows.