SRSS Report Loses some number formats when exporting to MS Excel

Thank you for looking at my question.

We have a report on SRSS that lists transactions in a range off currencies, GBP, USD, AED.

The formatting of the report cell containing the currency value is handled using an expression:

=Fields!CurrencySymbol.Value & "#,##0.00"

where the column CurrencySymbol is a char(3) field.

On the report, when previewed on the report server or viewed via a web browser the currency values all are preceded by the  relevant currency symbol eg £150.00, $99.00, AED500.00 but when I export the report to Excel I get an error messsage about some number fields losing their formats - specifically the AED values lose their prefix, all other values are OK.

I tried exporting the report as a csv file and the formatting remains as it should.

How can I retain the formatting for all values?
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?
 
Nico BontenbalCommented:
You're welcome.

Try this as the format expression:
="""" & Fields!CurrencySymbol.Value & """" & "#,##0.00"

Open in new window

In my test this solved the problem.

In Excel when you enter this as format:
AED#,##0.00
You'll get an error. But this works fine:
"AED"#,##0.00

Looks like when you specify the quotes in SSRS everything works fine both in SSRS and Excel. I couldn't find any documentation about it, but it looks like the " explicitly tell SSRS to use the characters as a string. Suppose you want your numbers formatted as:
## 123
Then you can use this as the format:
="""##"" #,##0.00"
It results in the format string:
"##" #,##0.00
So whatever is between the " is copied directly to the formatted string. Both Excel and SSRS interpret the " in the same way. Where the string AED has a different meaning in SSRS and Excel. (At least, that's my theory).
0
 
Gary CroxfordOperations Support AnalystAuthor Commented:
Exccellent - thank you for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.