Converting * character in SSRS Expression

Question

I am using the following expression in SSRS

=IIF(ReportItems!Textbox69.Value = "~*", 
        "Excellent", IIF (ReportItems!Textbox69.Value = 1,
        "Very Good", IIF (ReportItems!Textbox69.Value = 2,
        "Good", IIF (ReportItems!Textbox69.Value = 3,
        "Modest", "Cause for Concern")
        )
        )
    )

When I run my report, all the fields that had a * originally are coming up as an error. How can I convert this character please?

niktrs niktrs · Accepted Answer · 2018-07-05T08:39:19

Since your textbox contains strings you have to use quotes in comparisons

=IIF(ReportItems!Textbox69.Value = "*", 
        "Excellent", IIF (ReportItems!Textbox69.Value = "1",
        "Very Good", IIF (ReportItems!Textbox69.Value = "2",
        "Good", IIF (ReportItems!Textbox69.Value = "3",
        "Modest", "Cause for Concern")
        )
        )
    )

I would also suggest to consider using Switch instead of nested Iif

= Switch(
ReportItems!Textbox69.Value = "*", "Excellent",
ReportItems!Textbox69.Value = "1", "Very Good", 
ReportItems!Textbox69.Value = "2", "Good", 
ReportItems!Textbox69.Value = "3", "Modest", 
True,"Cause for Concern"
)

Converting * character in SSRS Expression

1 Answers