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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
You'll get an error. But this works fine:

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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary CroxfordOperations Support AnalystAuthor Commented:
Exccellent - thank you for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.