How can I unmerge cells in ssrs when exporting to excel to try to sort

Question

I'm exporting an SSRS Report to Excel and when the user tried to sort it for some column. They got the message "This operation requires the merged cells to be identically sized.

How can I generate the report in SSRS with unmerged cells? I tried to get some property in the tablix, but I couldn't find

Thanks in advance

Answer 1

This is usually caused by having a header with different column widths than your data area - the renderer puts merged cells in your table to get the header text Excel columns to align with the Excel columns of the table cells. You can try to fix this by making sure your header items exactly align with your table cells.

However, the best way to deal with this is to eliminate the header when exporting to Excel so you just get the table cells. With no header, there are no alignment problems.

There are a few options here. For a permanent export option, you can check my previous answers about adding a new Excel report rendering option or for a couple of ways to do it in a more manual way you can output to Excel using Simple Page Headers.

Answer 2

• One way is to not output the troublesome parts of the report. You can go to the property pages of the element -> Visibility -> Show/Hide based on expression, then use this expression:

=(Globals!RenderFormat.Name = "EXCEL") and it won't show the part in the excel export.

• Another tip is to make sure everything lines up perfectly, and use point measurements not cm's for sizes.

References:

• http://blogs.msdn.com/b/robertbruckner/archive/2010/05/16/report-design-naming-excel-worksheets.aspx
• http://edspencer.me.uk/2010/10/23/how-to-avoid-cell-merging-when-exporting-to-excel-in-ssrs-2008/
• How to get named excel sheets while exporting from SSRS