다음을 통해 공유

Merge Ahead

  • 아티클

One issue that continually vexes users of the SQL Server Reporting Services Excel rendering extension is that of merged cells in the Excel output.  Merged cells can be a problem because Excel's sort functionality is very particular about the way cells have to be merged in order to work properly.  Excel requires that ranges of merged cells be identically sized in order to be sorted.

So, why does the renderer merge cells?  It's important to keep in mind that the Excel renderer is primarily a layout renderer.  Its goal in life is to replicate your defined report layout as closely as possible as an Excel workbook.  At that task, it does a really good job.  A consequence of this design goal is that in striving to preserve the layout, cells need to be merged on the worksheet surface.

Consider this example.  This is a very common case that I see a lot of users running into.  Say you have a report with a single Table and a Textbox above it acting as a header that is laid out like this:

When rendering to Excel, in order to preserve the layout defined above (in particular, the dimensions of each Textbox relative to one another), the worksheet cell grid is configured like this:

If you want to sort the Table data in Excel you cannnot because the first Table column consists of merged columns A and B and the second is column C.  Excel's sort function cannot handle this case.

If exporting to Excel with the ability to sort is important to you, here are some tips to help you reduce the amount of merged cells in your workbooks:

  1. Make sure the left and right edges of all report items line up with one another.  This is the #1 cause of merged cells.  Referring to the example above, if the header Textbox's left and right edges lined up precisely with the width of the first table column, the merge would not be necessary.  This technique should solve this problem for the majority of cases.
  2. Even if you do line up everyting precisely, you may find in some rare cases that there are still some columns merged.  This could be due to internal rounding and unit conversion issues when we lay out the Excel worksheet at render time.  Report Definition Language allows you to specify position and size values in a number of different measurement units such as inches, pixels, centimeters, and points.  But, internally the Excel format wants everything to be in points.  To minimize the amount of conversion that we have to do at render-time to change your inches and centimeters to points, consider specifying all of your measurments in points for the most direct results.  One inch is 72 points.  This is a much rarer case, so consider following this step if the technique described in #1 still does not solve your problem.  This will be substantially improved in the next version of Reporting Services.
  3. Use the third-party report design and rendering tool SoftArtisans OfficeWriter.  Using OfficeWriter, reports are designed using Excel as the authoring tool and the Excel document itself becomes the report layout definition.  Because you are both designing and delivering in Excel, you can achieve precise Excel layout.

I hope this helps clear up some of the questions surrounding this issue. Let me know if you have any feedback.

Comments

  • Anonymous
    March 01, 2007
    If you need a title for your report -but that title causes the cursed merge cells problems - then you may try the following work around:Create a bitmap image that contains the report title. Within BIDS choose the Report menu then Embedded Images. Add your bitmap to the list. Add a header to the report (from the Report menu again). Select the header and set the background image property to Source=Embedded, Value=The name of you embedded image.The image will display when the report is rendered in the browser but will not appear if you save the results to an Excel spreadsheet.
  • Anonymous
    March 09, 2007
    It inevitably happens when building SQL Server Reporting Services reports that someone will have desire
  • Anonymous
    May 21, 2007
        I also had similar issues related to merged cells.   Merged cells were present mostly in the regions which is interfering with left and right edges of report items in report header.    So i tried to keep the header into excel header. This can be done by including SimplePageHeaders=true in device info. Another tips is to remove the blank columns by adding RemoveSpace item in device info.A sample of reconfiguring device info thro Reportserver.config is pasted below.
            <Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">            <Configuration>                <DeviceInfo>                    <SimplePageHeaders>true</SimplePageHeaders>                    <RemoveSpace>0.012in</RemoveSpace>                </DeviceInfo>            </Configuration>        </Extension>
  • Anonymous
    October 16, 2007
    PingBack from http://conservativgeek.wordpress.com/2007/08/30/reporting-frameworks-part-3-%e2%80%93-using-sql-server-2005-reporting-services/
  • Anonymous
    November 13, 2008
    It inevitably happens when building SQL Server Reporting Services reports that someone will have desire to export the beautifully created report out of SSRS and into some other format. And sometimes that format just happens to be Excel.Presto Change-OAnd
  • Anonymous
    March 12, 2009
    If you add the <SimplePageHeaders>true</SimplePageHeaders> the header section will be placed in the header of the excel sheet and the user will see it only when he takes print out or select print preview.I think the better option is to add multiple header rows in the grid which is shown in the detail section and move the text you want to show in the header section to these rows. You can have merged cells also here and excel wont prevent you from sorting in this case also.Bimal
  • Anonymous
    June 08, 2009
    The tips given above really work.Point 1 is the basis.Points 2 and 3 are very true. There might be cases when measurements are same but still excel export has merged cells. This problem can be rectified without doing much of the math. Lemme take an example by stating what I did in my reports. The basic elements in my reports were tables(usually two). I had created the tables from scratch. I did all the math and made sure the cell border alignment in the two tables were same. But excel exports still had merged cells issues. Just to mention the merged cells were very small. So instead of creating two different tables from scratch I created a single table and then made a copy of that table. Hence reusing one table.
  • Anonymous
    June 12, 2009
    PingBack from http://dedirahmansianturi.com/2009/06/13/my-reporting-service-problem-in-excel-output/
  • Anonymous
    April 27, 2010
    It is possible to Merge cells together. Try this method. http://www.itjungles.com/reporting-services/reporting-services-merge-cells
  • Anonymous
    June 20, 2013
    Thanks for sharing your knowledged. It helped me in getting rid of a hidden column at the end of a exported report.
  • Anonymous
    February 24, 2014
    Im using RDLC in asp.net website. im facing same issue but i have a web.config file. where should i put these configurations.<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering"><Configuration><DeviceInfo><SimplePageHeaders>true</SimplePageHeaders><RemoveSpace>0.012in</RemoveSpace></DeviceInfo></Configuration></Extension>
  • Anonymous
    July 01, 2014
    This trick simply save my life. Great.