Mark Brown's Blog

TFS, Visual Studio, SQL Server, BizTalk, SharePoint, .Net, and more ...

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.

Published Thursday, June 28, 2007 6:49 PM by mab
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

sbarrow@cfoconsult.com said:

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

July 9, 2007 3:14 PM
 

mab said:

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.

July 27, 2007 10:55 AM
 

desireemm said:

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

\\GCSQL\Database\Backgrounds\SCANNED ACROBAT DOCUMENTS (BACKGROUNDS)\SCANNED TERMINATION DOCUMENTS\employee 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??

--------------------------------------------------------------------------------

March 13, 2009 3:36 PM

Leave a Comment

(required) 
(optional)
(required) 

  
Enter Code Here: Required
Submit

© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Microsoft
Page view tracker