Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Preserve SSRS formatting when exporting to Excel Expand / Collapse
Author
Message
Posted Tuesday, February 15, 2011 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 31, 2011 1:04 PM
Points: 6, Visits: 18
I'm pretty sure SSRS doesn't do this on its own, but I was wondering if someone could help me find a third party product that could accomplish what I need from SSRS.



Specifically, I need SSRS to preserve settings when I export a report to excel for such things as excel page headers and footers, print range, column width, etc.



How can I accomplish this with SSRS, or what third party API will accomplish this?



Thanks!
Post #1064504
Posted Wednesday, February 16, 2011 12:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 31, 2011 1:04 PM
Points: 6, Visits: 18
bump
Post #1065247
Posted Thursday, February 17, 2011 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 31, 2011 1:04 PM
Points: 6, Visits: 18
Any help at all?
Post #1065906
Posted Wednesday, March 2, 2011 1:05 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, February 10, 2013 11:44 AM
Points: 170, Visits: 442
SSRS absolutely sucks when it comes to clean exports to Excel. It loves to put in hidden columns and all kinds of other trash that makes the export useless to an analyst who intends to do further work with it. It's okay for just viewing the data but if you were only doing that, why would you bother exported to Excel? It's so bad, that for our analysts I am recommending we move their reports out of SSRS and make them natively in Excel and pull in the data through ODBC directly.
Post #1071767
Posted Thursday, March 3, 2011 6:31 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, December 4, 2014 6:12 AM
Points: 429, Visits: 995
The biggest issue I've seen with reports is the merging of cells. This usually happens if I have multiple tablixes on the same page that would export to being in the same tab in a spread sheet. The only way I've found to get around that is to have each tablix on its own tab, that is, page break between tablixes.

Another trick to remove "hidden" or extra rows is to be sure to line up multiple items right next to each other, in other words use the tools to have tablix2 begin immediately below tablix 1 by editing Location values.

If your users are most interested in doing pivot tables of the data exported to excel, I recommend making sure the raw data formats correctly in CSV format and then encouraging users to export to CSV output files where they can take the raw data and mush it up to their heart's content.
Post #1072495
Posted Monday, November 19, 2012 11:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 3, 2014 12:33 PM
Points: 474, Visits: 209
I have also found that if you're having problems with headers (text boxes above the tablix(s)), you can just add rows above the tablix header row. Then you can merge and center as you wish, only affecting the row you specify. Also, this will not create ghost columns (blank columns between columns of data).

If this does not work for you, SSIS is going to be your best choice.
Post #1386533
Posted Friday, July 25, 2014 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 10, 2014 1:43 PM
Points: 4, Visits: 53
I have the same problem...users who wanted to immediately download a report to Excel and start doing their own analysis. They do not want to spend time removing headers and other stuff from the Excel spreadsheet.

I solve this by adding conditional statements to boxes, rows, and images that I don't want to export to Excel. Then I put an extra parameter in the report called 'Export to Excel'. When the user selects the Export to Excel = true, then the conditional statements hide those elements.

Basically, it's a way to trick SSRS into making two reports from one .rdl.

Here's my tutorial for this method (with screen shots). I hope this helps.

Hiding Parts of SSRS Reports from Excel exports

http://sqlstuff.weebly.com/report-server/hiding-headers-of-an-ssrs-report-from-excel-export
Post #1596316
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse