SSRS Report : Export To Excel - how to eliminate merged cells from excel

How to resolve the sorting issue with exported excel report by replacing report header with column header

1.     Background

The purpose of this article is to provide a way to resolve sorting issue in exported excel report due to improper report header. If report header is not properly aligned or having multiple textboxes may create sorting issue in the exported excel report. While exporting the report to excel, SSRS merges some of the cells of excel in order to maintain the report format.

Fig1. Showing excel report having merged cells

Due to this merging of cells, user will not be able to sort the column data and will get the below error:

2.     How to resolve the sorting issue with exported excel report?      

We can resolve this issue by removing the report header and add all the contents of report header to column header with proper alignment.

3.     Steps to resolve the issue

                    I.            Go to the report layoutè right click on left most part of column header of the tablixèInsert Rowè Above

                  II.            It will create a row above the column header:

                III.            Expand the width and height of the new column header as per report header contents and then merge all the columns of the newly created column header:

                IV.            Add the contents of report header to newly created column header including all text boxes. Try to adjust length & width of columns in such a way that it should change the look and feel of the report:

                  V.            Now preview the report:

 Report looks fine but we need to check whether we are able to sort the data or not in exported excel report.

                VI.            Export the report to excel and check whether we are able to sort the data or not:

 Exported Report:

Sort the data by Year:

Sort the data by region:

4.     Conclusion

Sometimes, due to report header layout, we experience sorting issue within the exported excel report. This issue can be resolve by replacing the report header to column header.