Ensure best Excel export formating from SQL Server Reporting Services

When designing reports it is important to be aware that the report may be exported to Excel.   This is most apparent when data manipulation must be done to satisfy the reporting requirements.   SSRS exports to excel using the underlying data source and not the report formating itself.   For example if you have stored in the database:

Region SalesPercent
West        10.5
East          32.2

If you want to display as a percent on the report you might be tempted to use the format string "0.0\%" on your report layout.   Ok, that gets the presentation but when you export to excel you get 10.5\% in the cell which appears to be 10.5 for excel and not .105 or 10.5%.

To help, you may want to tackle the issue of display at the source.   For example in the datasource for the report (the Select statement if you will), divide by 100 and then use the report format of "P".  An example select would be:

SELECT Region. SalesPercent/100 as SalesPercent FROM SalesDataTable

Hope this article gets you thinking about aspects you need to address when design and deploying reports that export data.