Wednesday, November 13, 2013

SSRS Exporting Options for Excel

When exporting to excel, sometimes SSRS can behave in a very unpredictable ways.  This little post has a few pointers when configuring excel-friendly SSRS reports.

One frequently requested feature is remove the report headers when exporting to excel.  To do that, you must add some code to the SSRS configuration file to tell SSRS to not export headers.  To do that, locate the rsreportserver.config file by navigating to C:/Program Files/Microsoft SQL Server/MSRS[Version].[Instance]/Reporting Services/ReportServer.

Then add the following code to the <Render> tag.

<Extension Name="EXCEL (No Header)" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
<OverrideNames>
   <Name Language="en-AU">Excel (No Header)</Name>
</OverrideNames>
<Configuration> 
<DeviceInfo>
   <SimplePageHeaders>True</SimplePageHeaders> 
</DeviceInfo> 
</Configuration> 
</Extension>

To get SSRS to pick up the changes, you'll have to go into report server configuration tool and restart the service.

When you navigate to a report on that server, and you click on the export icon, you should see the new option of "Excel (No Header)".


As you save the file off, you'll notice that the headers are not exported.  If you have blank rows or columns before your data begins, then you need to align your tablix of data such that there is no empty space between the tablix and the header or report edge. I find the best way to accomplish this is to open the tablix properties and set the location to 0,0.


7 comments:

  1. Your post was helpful! Thanks very much.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Can we remove it for an specific report

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. I struggled a lot till I found this article. This is amazing!!. Thanks so much for this post

    ReplyDelete
  6. When I omit the OverrideNames node it works fine, however when I include this the service errors of start-up.

    ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: , Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ---> System.NullReferenceException: Object reference not set to an instance of an object.

    Has anyone else experienced this?

    ReplyDelete