SSRS Exporting Report to Excel - Keep Formatting on Export
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).
Simlar Posts
- SSRS Exporting Report to Excel - Keep Formatting on Export Round 2
- SQL Server Reporting Services: Quick way to get 10 digit year (all the zero’s) using String.Format
- SSRS: RunningValue() to get Cumulative Totals
- SSRS and SSAS Formatting Dates
- Reporting Services Scripter: Sync Reporting Services Instances and Objects
June 29th, 2007 at 9:08 am
[...] 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!) [...]
April 15th, 2008 at 8:31 pm
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.