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

13 thoughts on “SSRS Exporting Report to Excel – Keep Formatting on Export”

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  6. 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”

    Like

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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s