How to eliminate merged and hidden Cells issue from Excel Export of a SSRS report
It is a known product issue (bug Id –469197) that when SSRS report is exported to excel, sometimes the columns get merged or hidden. As shown in Fig. below when a SSRS report is exported to excel ,columns in excel get merged and cause issues while printing and exporting to pdf. In some other cases exporting leaves some hidden columns in the excel sheet. This behavior is unpredictable and is encountered quite often. Though the issues are trivial, many a times it is difficult to explain and convince the customer about the erratic behavior of export as due to merged cells, customer might face issues while sorting columns.However, as per the SSRS team based on the way that the SSRS Excel renderer lays out a report when it is being created, that behavior is currently by design. Thus, here are few techniques/ workarounds that you can try to counter issues of merging and hiding columns on excel export of SSRS report.
To resolve the issues of merged and hidden columns, follow the steps below.
1. Make sure that while designing the report in the report layout, all the report’s rows (header row as well data rows) should start at (0,0) location and there is no additional space on the left hand side of the report in the design layout. This can be
done by making the Left and Top values under the Location section in the properties window, 0
2. Ensure that the width of each column is a rounded figure.If you have a requirement to have a decimal figure as width then ensure that decimal has at the most ONLY 1 or 2 decimal places ,
for eg : 2.5 , 2.75 and not decimals like 1.734 or 2.5234.
3. Ensure the header row and the report’s data rows are allof same width as shown in the Fig Below.
4. Once you have made widths of columns as rounded figures,ensure that the overall width (sum of all the widths of the individual columns)of the report data row including header row is a rounded figure like 6 , 9 etc
.
This would also ensure that the Report header, column header row and data rows all are of same width as well, which is also needed to avoid merging /hiding of cells.
5. If the above steps don’t solve the merged and hidden cells issue, try making the width of all the data columns (Column header, in our example , shown in Fig below – “Service Title “need not be of same width as other data columns –CostHeadingsName and Total) equal in the report .The width can be set in the properties window under the size section.
Above workarounds should eliminate the merging and hidden columns issue on excel export of SSRS report.
Comments
Anonymous
May 20, 2013
The comment has been removedAnonymous
July 24, 2013
Thanks, by keeping the width of the header and the table same solved the problem of merged columns in excel......Anonymous
October 11, 2013
I had an issue of cells being merged because of the title. I followed the instructions and made sure the title was 6 inches long and started from the location was 0in, 0in and it worked like a charm. Thanks a lot.Anonymous
October 22, 2013
Microsoft should have bought crystal reports. ssrs just isn't of the same caliber of tool.Anonymous
November 05, 2013
The comment has been removedAnonymous
November 12, 2013
Hi, to be sure to eliminate merged and hidden cells when exporting your report, use de "pt" units instead of using "inc" or "cm".Anonymous
December 04, 2013
Agree with Mscheers steps above don't eliminate all hidden cells (especialy when more than 1 table in report) and only way I've found is to use "pt" units.Anonymous
December 12, 2013
Hi All, Thanks Richa for your blog, I have sorted the cells being merged issue after read your blog, but I got another issue, I have done same thing what you have mentioned above, but I have added header and footer to my report, and I got logo's in the header, that causing the issue in merging the cells, can you please help me in sorting the merging cell when exporting to excel. Thank you in advance ManojAnonymous
December 12, 2013
I have sorted it, I have aligned the logos to the column alignment, so, it works now. Thanks All.Anonymous
December 16, 2013
Thanks for your blog Richa. This content has helped me a lot in sorting the merge issue while exporting to excel.Anonymous
February 26, 2014
This doesn't solve the problem with the large cell created at the beginning of an outer group, so I have a group "Customer" and within that I have another group with four lines, sales, sales total, adjustments, totals plus adjustments. This creates a column at the left of my page which spans four lines. Any ideas how to make these lines discrete?Anonymous
March 28, 2014
This one really worked and your steps are clear. Thanks alot.Anonymous
April 06, 2014
I also removed the report header and footer which solved the merge/hidden cell issue. Report display was simplified right down to a single table, the size of which matched the overall width and height of the report.Anonymous
June 20, 2014
I removed the header and that resolved this merge cell issue. ThanksAnonymous
June 24, 2014
I have same issue, and finally found the reason, even for the matrix table which contains dynamic number of columns that we cannot predict how long the text controls outside of the table should be. The solution is, for the text controls outside of the table, its length must not in between any column of table, otherwise, it will use more than one columns when interpret to excel. This will cause merge columns at column 4 when export to excel | Header text control | | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | And following is to fix the issue, by increase the length of header text control to the ending of Col4 | Header text control | | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 |Anonymous
July 07, 2014
The comment has been removedAnonymous
November 04, 2014
==========================Thanks all for your post ===================================== Please follow the above five pts,, If all the above doesnt solve ur issue..Just try give some space between the two Tablixs..Anonymous
November 18, 2014
Thanks a lot . it helped me . i had titles and a text explanation at the beginning of the report that were too short compared with the size of the table. Now it is ok.Anonymous
December 16, 2014
Another root cause of this issue may be images or text boxes, above the data matrix, that have edges not in alignment with column edges. It appears that having an image or textbox edge ending in the middle of a column may cause the column to be split when exported to Excel.Anonymous
February 03, 2015
Thanks for the tips. I just had to relate to all controls mired DataGridView columns (Ex .: Instead of using 3 text controls, use 1 with markers linked positions between 1-3 columns but with no difference). Most of all the header and the LEFT and greater than 0 TOP location is what destroyed me rendering. Best regards.Anonymous
March 23, 2015
Thanks, by keeping the width of the header and the table same solved the problem of merged columns in excel......Anonymous
April 09, 2015
@Mscheers Great tip, when you've got multiple tables alignment and consistent sizing don't always seem to work, converting to PT worked first time! Thanks!Anonymous
June 15, 2015
This worked OK for me, set location to 0,0 , Thank you for postingAnonymous
October 26, 2015
Changing all widths to pt unit fixes most of the problems. (in causes problems)Anonymous
February 25, 2016
The article is very usefull. Thanks for sharing!