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



  • Pingback: stevienova.com » Blog Archive » SSRS Exporting Report to Excel - Keep Formatting on Export Round 2

  • MikeViking

    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.

  • Mattias Johansson

    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.

  • Luke

    Brilliant! Thank you so much, this has been causing me grief all afternoon :-)

  • mikey mileos

    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.

  • Bhushan

    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

  • Owen

    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

  • Craig

    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”

  • Ralfi

    You’re genius.

  • Iseg

    Thank you very much!

  • notarnet

    The way that works for me is use CDec in the cell expression and also set P2 (C2,N2,…) in the Format of properties.

  • http://www.facebook.com/people/Michael-Juarez/100002115224464 Michael Juarez

    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