CODEWITHSUNDEEP
.netsql-serverexcelvbareporting-services
IWriteApps
IWriteApps

Reputation: 993

Microsoft/SSRS report textbox returns #error when string value that is decimal is converted to decimal and empty string is just printed

I have a value that is coming back from a database as string, but it could be a decimal and when it is a decimal I want to display it as a decimal and format it a certain way, if it isn't a decimal then I just display whatever value comes back.

Here's my code:

=Iif
(
    IsNothing(Fields!Amount.Value) Or Len(Trim(Fields!Amount.Value)) < 1, 
    Fields!Amount.Value, //have even tried to do CStr(Fields!Amount.Value) in case conversion below makes the report expect decimals for all values
    Iif
    (
        IsNumeric(Fields!Amount.Value), 
        CDec(Fields!Amount.Value),
        Fields!Amount.Value
    )
)

The comment above is not part of the code, I just put that here. Anyway, based on the above, all decimals are successfully converted to decimals and display ok, but all those strings that are either empty or hold a non-numeric value show up as #Error.

Here's a sample result display:

72.00
95.00
#Error
20.00

What's wrong with this expression? and why couldn't SSRS use c# instead of VB?!!?

UPDATE: I know that the problem has to do with the conversion and not the logic to check whether the value is nothing, less than 1 character, or a numeric, because the following works:

=Iif
(
    IsNothing(Fields!Amount.Value) Or Len(Trim(Fields!Amount.Value)) < 1, 
    "is nothing or less than 1",
    Iif
    (
        IsNumeric(Fields!Amount.Value), 
        "is numeric",
        "is not numeric"
    )
)

this will correctly display:

is numeric
is numeric
is nothing or less than 1
is numeric

Upvotes: 1

Views: 8045

Answers (2)

IvanH
IvanH

Reputation: 5159

Iif is a function and so all arguments are evaluated before the function is called. So there is no use in using Iif to prevent error.

I suppose you need a User defined function (Writing Custom functions in SSRS)

So instead Iif(IsNumeric(Fields!Amount.Value),...)

define a function 

Function DecimalIfPossible(Value as string) As Object
  If IsNumeric(Fields!Amount.Value) then
      Return  CDec(Fields!Amount.Value)
   else
      Return  Fields!Amount.Value
   End if
End Function

and call it DecimalIfPossible(Fields!Amount.Value).

Upvotes: 1

Anup Agrawal
Anup Agrawal

Reputation: 6669

Why not convert the decimal back to string

 IIF
    (
        IsNumeric(Fields!Amount.Value), 
        CStr(CDec(Fields!Amount.Value)),
        Fields!Amount.Value
    )

Based on your logic you are looking for empty or Null string, so handle it in SQL. Convert it to NULL if it is non numeric something like

CASE WHEN ISNUMERIC(Amount) = 1 THEN Amount ELSE NULL END As Amount

or use NULLIF

Upvotes: 0

Related Questions