SSRS Exporting Report to Excel – Keep Formatting on Export

While creating Reporting Services (SSRS) reports, I usually use Format Functions around values to make the report look good (mostly for Number with decimal and comma). By doing that way while export to Excel we may lose the ability to sum the values, because everything is exported as text.

There is a right way to make it work. Remove all the Format function like Format(‘Field.xxxx’, "N2"). On all your number fields, highlight them, then in the properties area, there is a “Format” property. Set Format Code in that to N2 (N for Number, 2 for the number of digits after the decimal, so if you wanted zero, it would be N0). Again for Percentages & currency its the same way P2 or P0.

Advertisements

About msarm

Aspiring Enterprise Architect.
This entry was posted in SQL Server 2005/2008. Bookmark the permalink.

One Response to SSRS Exporting Report to Excel – Keep Formatting on Export

  1. Rey Villarte says:

    Simple and Great Solution!
    You’ve made my day!

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