Report Server Settings in SQL Server 2012 SharePoint Integration Mode

Well, rules have changed. As Jaime Tarquino from the SSRS team explained in his blog, “SQL Server 2012 Reporting Services SharePoint Integrated Mode”, there is no rsreportserver.config file anymore moving to Reporting Services 2012 in SharePoint integration mode (there are no configuration changes with native mode). Instead, the configurations sections are now saved in the SharePoint configuration database.

Problem: A customer plans to upgrade to SQL Server 2012 and SharePoint integration mode. They had the following custom renderer registered in the rsreportserver.config file that uses device information settings to customize the Excel renderer to suppress report headers and use Excel headers instead:

<Extension Name=“EXCELNOHEADER“ Type=“Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering“>

    <OverrideNames>

        <Name Language=“en-US“>Excel (no Header)</Name>

    </OverrideNames>

    <Configuration>

        <DeviceInfo>

            <SimplePageHeaders>true</SimplePageHeaders>

            <RemoveSpace>0.012in</RemoveSpace>

        </DeviceInfo>

    </Configuration>

</Extension>

The customer claimed that this configuration is so important that there is no way they could upgrade if this doesn’t work in SQL Server 2012. And after ensuring them that this is an easy fix, I’ve found myself spending hours to understand what needs to be done. On the positive side, I’ve picked up a few PowerShell skills along the way which may come handy given that PowerShell is omni-present nowadays.

Solution: In SQL Server 2012 (SharePoint integrated mode), you need to make such changes with PowerShell. The PowerShell Cmdlets topic in BOL is a good writeup about the SSRS cmdlets. The following script gets the job done:

Add-PSSnapin Microsoft.SharePoint.PowerShell

$apps = Get-SPRSServiceApplication

foreach ($app in $apps)

{

New-SPRSExtension -identity $app -ExtensionType “Render” -name “EXCELNOHEADER” -TypeName “Microsoft.ReportingServices.Rendering.ExcelOpenXmlRenderer.ExcelOpenXmlRenderer,Microsoft.ReportingServices.ExcelRendering” -ServerDirectives “<OverrideNames><Name Language=’en-US’>Excel (no Header)</Name></OverrideNames>” -ExtensionConfiguration “<DeviceInfo><SimplePageHeaders>true</SimplePageHeaders><RemoveSpace>0.012in</RemoveSpace></DeviceInfo>”

}

The script starts by importing the Microsoft.SharePoint.PowerShell snapi (you don’t need it if you run the remainder of the script in the SharePoint 2010 Management Shell). Then, the Get-SPRSServiceApplication cmdlet returns all the Reporting Services Service Applications available in the farm. For each application, I call the New-SPRSExtension to register the new renderer. To run the script:

1. Remote to the SharePoint server. You must have SharePoint Farm Administrator rights to run the script.
2. Open SharePoint 2010 Management Shell from the Microsoft SharePoint 2010 Products program group. Or, if you have installed the Windows PowerShell ISE feature, you can open Windows PowerShell ISE from the Accessories program group but make it sure to run it as administrator (right-click on it and click Run As Administrator).
3. Copy and paste the script, and hit Enter to run it. The figure below shows the script in Windows PowerShell ISE:
4. Do iisreset to apply the configuration changes.

For a full list of the New-SPRSExtension cmdlet parameters, you can execute the following command: 

Get-help New-SPRSExtension – full 

To verify that the changes are applied you could use one of the following two approaches:

1. Execute the following script to output the configuration of all extensions:
   $apps = Get-SPRSServiceApplication

foreach ($app in $apps) {

    $extensions = Get-SPRSExtension -identity $app

    foreach ($extension in $extensions) {

echo $extension.ExtensionType $extension.Name

echo $extension | select -ExpandProperty ConfigurationXml }

} 

2. In SSMS, connect to the SharePoint config database and run the following query. Examine the content of the Properties column to make sure that the custom extension is registered.

SELECT * FROM [dbo].[Objects] WHERE properties like ‘%excelnoheader%’     

I was tempted to try modifying directly the Properties column in the Objects table and add the new extension section but I realized I had better use of my time than troubleshooting failed SharePoint installations so I decided to stick with the supported way.

Special thanks to Prash Shirolkar from the Reporting Services team for shedding light in dark places. His blog has valuable insights about Reporting Services and SharePoint.