Preserve SSRS formatting when exporting to Excel

  • 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!

  • bump

  • Any help at all?

  • 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.

  • 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.

  • 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.

  • 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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply