5

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?

eknofsky
  • 163
  • 1
  • 9

3 Answers3

8

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)
Chris Latta
  • 20,316
  • 4
  • 62
  • 70
2

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

eknofsky
  • 163
  • 1
  • 9
1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Is there a way to properly mix numbers / strings in one column and maintaining the ability to format the numbers? Or is this strictly not possible? – eknofsky Jun 19 '17 at 02:18
  • 2
    @gordon-linoff The OP was referring to the iif() function in the context of Reporting Services functions (I think they're VB.net functions), not SQL Server t-sql scalar functions. The SSRS functions are used within the rdl itself. – DatumPoint Jun 19 '17 at 07:05