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.

Comments (3)

  1. sbarrow@cfoconsult.com says:

    I’ve formatted some fields as P2 (as discussed above), which display correctly.  However, after exporting to Excel, some of the percentages show 3 decimal places instead of 2.  Any ideas as to why?

    Thanks, Shawn

  2. MarkABrown says:

    It shows in excel the exact underlying data of the report and not the formatted report display.  Changing the underlying query to truncate/round as appropriate should get your desired result.

  3. desireemm says:

    Hello I have been having an issue opening hyperlinks in reporting services. we scan all the employee documents and link them to an Access front end (SQL SErver 2005 is the Engine) the pdfs are stored on our server adn the links will look like this


    they are saved by Employee Number which will always be different. Every time I try to link a Pdf to reporting services It never works, all i see is the link not the pdf. Can anyone help pls??