How to Eliminate Merged Cells from a Reporting Services Excel Export
Ihavea reportgenerated fromVisual Studio 2008 Reporting Services that the users like to export to Excel then sort. The report contains a couple images in thereport header. When the report exports to Excel, the header is causing merged cells in the spreadsheet. These merged cell are preventing theusers from sorting with an error "This operation requires the merged cells to be identically sized." Does anyone know if the images can be removed from the export from this report without affecting other any other report exports?
July 3rd, 2008 9:12pm

While I don't know an easy way to exclude images from the export, I have a tip on reducing the number of merged cells in your report. If you size the images (or other items) in the report header (and footer) so that their left and right edges align exactly with the left and right edges of your report columns, it is possible to eliminate the merged cells. Of course, that might not be an option for you, but I've found that to be helpful when sizing and placing header and footer items.If CSV export is an option, that could serve as a plan B for users who want to sort and manipulate data in Excel.
Free Windows Admin Tool Kit Click here and download it now
July 3rd, 2008 9:39pm

Kyle Priddle wrote: While I don't know an easy way to exclude images from the export, I have a tip on reducing the number of merged cells in your report. If you size the images (or other items) in the report header (and footer) so that their left and right edges align exactly with the left and right edges of your report columns, it is possible to eliminate the merged cells. Of course, that might not be an option for you, but I've found that to be helpful when sizing and placing header and footer items.If CSV export is an option, that could serve as a plan B for users who want to sort and manipulate data in Excel. As with others I've had this problem too. When I still got merged cells after ensuring the header/title matched exactly to the left and right edges of my table I checked the column widths to see if anything else was amiss. Here's an example of thewidths I saw: Title: 20cm Table: 20cm - Column 1: 4.9525 - Column 2: 5.0475 - Column 3: 4.9255 - Column 4: 5.0745 Overall, the widths match but why all those decimal places? Possibly due to resizing the table and it auto-calculating the column widths. So, I resized each individual column width to the following and my merged cells problem went away: Title: 20cm Table: 20cm - Column 1: 5 - Column 2: 5 - Column 3: 5 - Column 4: 5 I don't know if this is the solution to this problem but it worked for me and it's something else to try.
November 6th, 2008 6:02pm

Here's more info on the Excel merged cell behavior: http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx Hope this helps! -Chris SQL Server Reporting Services
Free Windows Admin Tool Kit Click here and download it now
November 9th, 2008 3:17am

I tried keeping all the columns and text boxes starting and stopping in the same place but still had merged cells. This solution will ALWAYS work if you only have one table in the report, which is usually the case when people want to export to Excel. 1. Add a parameter called "Header". Make it Boolean and Prompt should say "Show Header?". You can make a default to "No" if you think people will usually want to export to Excel. 2. Remove the Header (you can copy all the object to the body of the report temporarily). 3. Add a new row above the top row in the table. It will become your header. Merge all the cells. Drop a rectangle object into it and move all of your header objects on top of the rectangle. Adjust them as you like. 4. Click on the gray border of the table that is to the left of your new header row. Activate the properties pane. Expand Visibility. On the Hidden element, click the area to add a value and click the down arrow and click "Expression". Add the following to the expression: =IIF(Parameters!Header.Value = "True", False, True) Now a parameter will let users decide whether to see a header so they can have nice print output or only a table with no merged cells for excel. If your users aren't sure why you would want to remove the header you could make the prompt something like "Excel Friendly Output", just swap the True for False in the expression. This works great for us. Jon
February 9th, 2010 7:08pm

Thanks justintn...worked for me. I tried two things before I took your approach - 1.Make sure the left and right edges of all report items line up with one another. 2.Adjusting column widths. Both didn't work.
Free Windows Admin Tool Kit Click here and download it now
December 17th, 2010 2:09pm

check this site: http://msdn.microsoft.com/en-us/library/ms159836.aspx look at the part about 'SimplePageHeader Settings' to make the page header only visible when printing the excel sheet. hope that will help on the merging problem too, D
April 26th, 2011 5:13am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics