Dynamically assign Dollar and percent sign to the Column value in SSRS expression

Question

I have a column with the below sample data coming from the table , I am loading this data to SSRS report. I have to fomat the data based on Type column, if the type is dollar in the report it should show as $78.00 if the type is 'percent' it should show 78% .

ID	col	Type
1	78.00	dollar
2	98.00	dollar
3	0.78	percent

I tried the below format expression but it is not working.

=IIF(Fields!Type.Value ="Percent", 
Format(Fields!col.Value,"0%"),
Format(Fields!col.Value,"C"))  

after using this format function the dollar amount values are doubling like this

ID	col
1	$7878.00
2	$9898.00
3	78%

Am I using the incorrect format function ?. Please advice

Answer 1

The expression need to be changed to :

=IIF(Fields!Type.Value = "dollar", "$" + Format(Fields!col.Value, "0.00"),
     IIF(Fields!Type.Value = "percent", Format(Fields!col.Value, "0%"), ""))

Like in this DBFIDDLE Demo

This gives me the expected output:

ID	col
1	$78.00
2	$98.00
3	78%

Update :

=IIF(Fields!Type.Value = "percent",
Format(Fields!col.Value, "0%"),
Format(Fields!col.Value, "$0.00"))

Answer 2

The first thing I would say is don't convert the values to anything as they become useless if you need to do any kind of calculation on them after they have been formatted, or if you want to export to Excel etc..

Instead, change the format property of the textbox that displays them.

The principle is the same.

So remove the formatting value expression and change the format property to

=IIF(Fields!Type.Value ="percent", "p0", "c2")