SSRS Format string to percentage inside if statement

Question

I do have column as string, which contains numbers and some values is "N/A". I want when it is not "N/A" format to percentage, and when "N/A" just keep "N/A".

I have tried some options like:

iif(FieldColumn="N/A","N/A",FormatPercent(FieldColumn))

it is converting to percentage when it is not "N/A", but for the N/A rows it shows #Error.

Any solution? Is it possible to do it? Thanks

Answer 1

There might be a neater way to do this but as your field is a string we need a couple of steps to acheive this.

First, change the Value expression to this.

=IIF(
     ISNUMERIC(Fields!myField.Value) = 0 , "N/A",
     VAL(Fields!myField.Value)
     )

This will return either "N/A" or an actual number

Next set the Format property of the textbox to

=IIF(Fields!myField.Value = "N/A", Nothing, "p2")

This sets the format to 'percentage with 2 decimal places' unless the field is "N/A" in which case no formatting is applied.