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.