SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Preserve SSRS formatting when exporting to Excel


Preserve SSRS formatting when exporting to Excel

Author
Message
bcwhiteh
bcwhiteh
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 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!
bcwhiteh
bcwhiteh
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 18
bump
bcwhiteh
bcwhiteh
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 Visits: 18
Any help at all?
tnk
tnk
Mr or Mrs. 500
Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)Mr or Mrs. 500 (570 reputation)

Group: General Forum Members
Points: 570 Visits: 443
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.
Bob Razumich
Bob Razumich
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 1216
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.
kevin.unglesbee
kevin.unglesbee
Mr or Mrs. 500
Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)Mr or Mrs. 500 (538 reputation)

Group: General Forum Members
Points: 538 Visits: 242
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.
donna.migliore
donna.migliore
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 82
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search