When creating Reporting Services (SSRS) reports, I usually use FormatCurrency() and FormatPercent() around values to make the report look good. Today I found out that when you use those, and export to Excel, you lose the ability to sum the values, because everything is exported as text.
The right way (or at least the way that I have found that works) is this. Remove all the FormatCurrency() and FormatPercent() functions. On all your currency fields, highlight them, then in the properties area, there is a “Format” property. I set that to C2 (C for Currency, 2 for the number of digits after the decimal, so if you wanted zero, it would be C0). Again for Percentages, same thing, highlight all the fields, and set “Format” to P2 (P for Percentage, 2 for the number of digits after the decimal).
Steve Novoselac's Blog 
16 replies on “SSRS Exporting Report to Excel – Keep Formatting on Export”
[…] Earlier I blogged about exporting SSRS (SQL Server Reporting Services) Reports to Excel and keeping formatting. Well, my previous solution works, but not always (doh!) […]
LikeLike
Formats can be frustrating in SSRS2005. I have some VB code called from a references library as an expression on a vlaue box. For example, the vlaue expression is similart to this
=CommonReportFunctions.DivideByTwo(Fields!RESULT_TEXT.Value)
The function returns a numeric, and the format code is set to D for decimal, but still it exports to excel as text. It seems that once you place an expression in the value field, everything is exported to excel as text.
LikeLike
I found a solution for the problem. If you use CDec to convert the whole calculation in your cell then it will work to export to excel and summarize the values correctly.
LikeLike
This worked. Thank you so much!!!!!
LikeLike
Brilliant! Thank you so much, this has been causing me grief all afternoon 🙂
LikeLike
I use the format property on cells in a table with values like C2, or P1 or F0 and they work kinda. When I export to excel they are still numbers and still percentages, but they have the wrong decimal places, which sucks.
LikeLike
Hi,
When I export my report to Excel,it gets expanded format along with Document map sheet, but in preview the report is actually in grouped format.I want the report to be exported in Excel w/o document map & in grouped format..can u give me any solution??Can you tell me what are the properties associated with Export settings of ssrs
LikeLike
I need to format a cell with zero decimal places and no thousand separator – i.e. f0. It looks fine on the screen, but when I export to Excel, it adds a decimal place to the value.
Any idea why it does this or what I can do to stop this happening?
Thanks
LikeLike
I’m having a different issue when exporting to Excel. I keep getting an Indexing error. I’ve found a lot of posts on the Internet with similar issues but no definitive answer. Any thoughts or suggestions?
Actual error msg:
“Index was out of range. Must be non-negative and less than the size of the collection. Parameter name:index”
LikeLike
You’re genius.
LikeLike
Thank you very much!
LikeLike
The way that works for me is use CDec in the cell expression and also set P2 (C2,N2,…) in the Format of properties.
LikeLiked by 1 person
I like the FormatDate() values to be set as standard as well. This doesnt cause any confusion when the formulas don’t pull through properly.
-Michael
http://www.oneclickcommissions.com/excel-reports.html
LikeLike
The article is very helpful! Thank you,
I’ll be sure to use your advice. Personally, I had some problems
converting excel files over to PDF. I got so annoyed from manually
moving the information (such as commission reports, invoices, etc.)
over to another document, but then I found this program at my work
that quickly and painlessly converts my excel documents into PDF and
ready to go templates and reports, saving me aggravating hours upon
hours. It provides my business a simple way to make individualized
statements for our business associates, taking our invoices and
seamlessly placing the information in the awesome templates. Check it
out here.
LikeLike
I have the expression in SSRS as CDbl(code.GetValue(0)).When I ran the report,I got the value. But when I export into excel it shows #Value!, Could anyone please help me on how to export the correct number?I
LikeLike
ngcc here
LikeLike