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

Logic

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.

Stiegler

I had read and tested you solution but SimplePageHeaders doesn't work in SSRS 2008 R2. even if I restarted the service. That was someone wrote in one of your anwser.. – Logic

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

Nefertiti

Answer 3

This is usually caused by header/columns/textboxes/footer with unaligned alignment. Even 1 point or 0.5 inches off would cause this merged cells when being exported to Excel. Make sure that everything is aligned correctly. Encountered this issue multiple times in multiple projects and it's always the same root cause.

Spitsbergen

I've just fixed this issue and as you say, you must be sure that everything (logos, titles, tables, footer,...) are well aligned so most left items start at same position (0.0 in my case) and end also in the same position. To identify where the problem is, export to excel and look for empty columns in the sequence – Crescent

This is exactly what caused the issue for me. I had a header across the top of the report which spanned many columns, but whose end was right in the middle of one report column. I just adjusted that cell to snap inline with the end of the column it was bisecting and that fixed the issue. – Quarrelsome

Answer 4

Please ensure that your header text box and tablix are having same width. This will eliminate the chances of merging cells. If both are not having same width, then it will merge cells of excel, where your header edges are.

1. Ensure all of your unbound Textboxes are sized to match you tablix width.
2. Ensure all unbound textboxes fit within a tablix column.

Reference - How to Eliminate Excel Column Merging in Exported SSRS Reports

Figueroa

Answer 5

you can also modify rsreportserver.config found if C:\Program Files\Microsoft SQL Server(SSRS Installation Folder)\Reporting Services\ReportServer and replace:

 <Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"> <Extension Name="EXCELOPENXML_NoHeader" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"/>

with

 <Extension Name="EXCELOPENXML_NoHeader" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering"> <Extension Name="EXCELOPENXML_NoHeader" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering">                <OverrideNames>                        <Name Language="en-US">Excel With No Header                        </Name>                </OverrideNames>                <Configuration>                    <DeviceInfo>                        <SimplePageHeaders>True</SimplePageHeaders>                    </DeviceInfo>                </Configuration>            </Extension>            <Extension Name="EXCELOPENXML" Type="Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering">                <OverrideNames>                    <Name Language="en-US">Excel With Header                    </Name>                </OverrideNames>            </Extension>

it will give you the option to export reports to excel with or without the header. This is a one time change and no need to modify all reports. No need to restart SSRS service. Tested with SSRS 2014, 2016, 2017

Downstroke