SSRS currency formatting issue using IIF condition

0
votes

I have a column in SSRS report which should be displayed in currency format. The data which is returned from dataset is of string datatype and it contains hyphen("-") character as well.

I am using below expression. =IIF(Fields!PREMIUM.Value="-","-",CDbl(Fields!PREMIUM.Value))

My intention is if the value is "-" then am ignoring it, otherwise am converting the string value to double.

Note: The textbox properties is already set to currency format.

The expression is working if there is any value other than "-"

This is the output. #Error is displayed

This is the output. #Error is displayed

1
reporting-servicesexpressionssrs-2012iif

1 Answers

0
votes

As you said, the issue is related to when the database returns the dash. The error occurs because SSRS doesn't have a way to handle a dash when you format the textbox as currency. The best solution for this would be to just leave the textbox as default format and modify your expression to format the values, as necessary. Something like the following should solve the issue. Simply add more # if you need a longer number.

=IIF(Fields!PREMIUM.Value="-","-",Format(CDbl(Fields!PREMIUM.Value)), "$#######.##")