Remove headers in Excel exports to fix column alignment

Reports with headers in Reporting Services works great as long as you are just rendering the reports in HTML. If you would try to export the report to Excel you might stumble into some problems.

As long as the header design follows the rest of the design when it comes to columns you could be fine. However, often headers contain very different information and design than the actual content of the report and column alignment might in these cases become a problem. You might find merged columns here and there which could be hard to work around when working with the exported Excel file.

There is however a quite easy fix for this, but it has its drawbacks.

You can configure exports to Excel in Reporting Services so that headers will not be displayed in the cell grid and will instead be saved in the actual header section of the Excel sheet. The file you need to edit is the following: C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config

Scroll down in the config file and you will find a part containing the <Render> tag. Here you can do some configuration for the exports which the users can use in the web GUI when rendering a report. The default configuration for the Excel export will look something like this:

<span class="s1"><extension name="EXCEL" type="Microsoft.ReportingServices.Rendering.
    ExcelRenderer.ExcelRenderer,
    Microsoft.ReportingServices.ExcelRendering">
</extension></span>

To remove headers (or move them from cells to actual header) you can add SimplePageHeaders to the tag:

<extension name="EXCEL" type="Microsoft.ReportingServices.Rendering.
    ExcelRenderer.ExcelRenderer,
    Microsoft.ReportingServices.ExcelRendering">
        <configuration>
            <deviceinfo>
                <simplepageheaders>
                True
                </simplepageheaders>
            </deviceinfo>
        </configuration>
</extension>

There is a couple of problems when configuring this (especially in a production environment). Since the configuration is a server config this will affect all reports. If you still want to be able to export headers on some reports and not on others you will probably have to redesign the ones you want headers in to include them in the body instead.

The second problem could be easily planned for but still worth mentioning: It requires a restart of Reporting Services and if you are running SSRS 2005 you might have to restart IIS as well. Note that the header section of the Excel sheet has a limit on 256 characters, including markup.

Another option is to hide the possibility for the users to export to excel. Set visibility to false and the option will go away.

<span class="s1"><extension name="EXCEL" type="Microsoft.ReportingServices.Rendering.
    ExcelRenderer.ExcelRenderer,
    Microsoft.ReportingServices.ExcelRendering" visible="false">
</extension></span>

- Written by John Roos on December 14, 2011