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

  • Anonymous
    July 09, 2007
    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

  • Anonymous
    July 27, 2007
    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.

  • Anonymous
    March 13, 2009
    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 \GCSQLDatabaseBackgroundsSCANNED ACROBAT DOCUMENTS (BACKGROUNDS)SCANNED TERMINATION DOCUMENTSemployee number 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??