Removing Headers from report before exporting to Excel

Auerelio Vasquez
Auerelio Vasquez used Ask the Experts™
on
Is there a way to remove the merging and headers before exporting to excel ? what's happening, is that when exporting, the excel is doing some weird merging with the headers and columns.

All I really want to export is the column names and data. OR maybe i'm doing something wrong with the headers....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Megan BrooksSQL Server Consultant

Commented:
This is an issue that I had to deal with repeatedly over the years, from RS 2005 to RS 2012, and I can't say that I ever found a particularly good solution for it. You can end up with column splits and merges that complicate use of the spreadsheet, and may not even render correctly in some situations.

If you have your own front end UI for the reports then you can launch a "headerless" version of the RDL when exporting to Excel. You could conceivably produce that RDL automatically from the normal version using XSLT or code, as a post-build step, although I never went that far. I even thought about rendering the header as an image. But for the most part we just passed it on to the customer as it was.
Top Expert 2008
Commented:
You could try the following:

Add a parameter to the report

General
Name: DisplayHeader
Prompt: Display header
Data type: Boolean
Parameter visibility: Visible

Default Values
Specify values | Add | Value: true

Next click on each text box in the page header section and under Properties | Visibility | Hidden
and set the condition to:

=Parameters!DisplayHeader.Value=false

Then, if you know you want to export the results to Excel, change the Display header option to False before clicking View Report. Otherwise, before you export to Excel, change the Display header option to False, click the View Report button to regenerate the report without the page header.

This may not be the slickest way to accomplish the task, but it's a work-around.
Megan BrooksSQL Server Consultant

Commented:
I don't recall having had that work for us. If it does, go for it.
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Top Expert 2008

Commented:
Also, forgot to mention that the text box(s) in the page header should also have the Properties | General | CanShrink set to True
Auerelio VasquezETL Developer

Author

Commented:
This solution almost works! I got rid of the header, and I know this is nitpicky, but this is what my users complained about. They want the column headings to start at row one, it looks there is a tiny bit of white space, between the tablix and header..... So the column headers start on row 2. Know if there is a way around that ?
Top Expert 2008

Commented:
Okay. Say there is one text box within the page header. If that is the case, set the top of the text box = 0 and the height of the text box = the the height of the page header.
Top Expert 2008

Commented:
If the height of the text box = the height of the page header, then the gap exists with the page where the tablix is.  In that case, ensure the the top of the tablix = 0
Auerelio VasquezETL Developer

Author

Commented:
is that under alignment ? padding options ? that's the only places I see the words "Top" or "Bottom"
Top Expert 2008

Commented:
For the textbox it would be under Properties | Location | Top
Top Expert 2008

Commented:
The height would be under Properties | Size | Height
Auerelio VasquezETL Developer

Author

Commented:
So, close, i'll award the points, but it still starts on row 2..... Thre is so little white space now, I can't even delete it lol
Megan BrooksSQL Server Consultant

Commented:
You might have to disable the header area in the RDL to get rid of that. Still, you fared better than I did (given that my employers didn't want me to spend any time on the problem).
Auerelio VasquezETL Developer

Author

Commented:
This was a request from the customer, so I have to try and figure it out. But it's much better than before, being that all they have to do is delete one row, and not have to worry about all the merged columns.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial