Modify SSRS Rendering Format In SharePoint-Integrated Mode

Problem

In a previous tip on Making Adjustments to SQL Server Reporting Services Configuration Files, we’ve seen how we can modify the rendering format of a Reporting Services report by making changes to the RSReportServer.config file. However, this does not work when Reporting Services is running in SharePoint-integrated mode. How can we accomplish this?

Solution

Since Reporting Services in both native and SharePoint-integrated mode have the RSReportServer.config file, we have gotten used to the fact that configuration changes are done thru this file in previous versions of SQL Server. With SQL Server 2012, Reporting Services in SharePoint-integrated mode has been changed from a Windows Service (like what we have in native mode) to a SharePoint service application. This also means that configuration changes to Reporting Services in SharePoint-integrated mode are now stored in the SharePoint service application database instead of the RSReportServer.config file (you will still see this file when you deploy Reporting Services in SharePoint-integrated mode.) A more detailed explanation of the changes in the architecture are available from Jaime Tarquino’s MSDN blog post.

Going back to the problem at hand, if we want to add or modify the rendering format in a Reporting Services report in native mode, the RSReportServer.config file is still the way to go as described in the previous tip. But for Reporting Services in SharePoint-integrated mode, this can be done by using the PowerShell cmdlets for Reporting Services SharePoint Mode, specifically, the New-SPRSExtension cmdlet. Unfortunately, the documentation for the PowerShell cmdlets for Reporting Services SharePoint Mode is not as extensive as the native PowerShell cmdlets. What I did was to use the example presented in Jaime Tarquino’s MSDN blog post on adding a data extension for the Access Service to add my custom rendering format. Since I have my RSReportServer.config file from the older version of Reporting Services, I took a snippet of the XML tags that define my custom rendering format.

<Extension Name="TXTspaceDelimited" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
 <OverrideNames>
  <Name Language="en-US">TXT (Space Delimited Text File)</Name>
 </OverrideNames>
 <Configuration> 
  <DeviceInfo>
   <FieldDelimiter xml_space="preserve">?</FieldDelimiter>
   <NoHeader>true</NoHeader>
   <FileExtension>txt</FileExtension>
   <ExcelMode>False</ExcelMode>
   <Encoding>ASCII</Encoding>
  </DeviceInfo>
 </Configuration>
</Extension>

I used this information to build my PowerShell command using the New-SPRSExtension cmdlet, passing the parameter values that I have in my XML tags.

New-SPRSExtension -identity <GUID ID value of the Reporting Services service application> -ExtensionType "Render" -name "TXTspaceDelimited" -TypeName "Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering" -ServerDirectives "<OverrideNames><Name Language='en-US'>TXT (Space Delimited Text File)</Name></OverrideNames>" -ExtensionConfiguration "<DeviceInfo><FieldDelimiter xml_space='preserve'>?</FieldDelimiter><NoHeader>true</NoHeader><FileExtension>txt</FileExtension><ExcelMode>False</ExcelMode><Encoding>ASCII</Encoding></DeviceInfo>"

I had to change the double quotes to single quotes in my XML tags because the parameter values used in the PowerShell cmdlet are surrounded by double quotes. This eliminates parsing errors when running the command. The GUID value comes from the ID value of the Reporting Services service application when you run the Get-SPRSServiceApplication PowerShell cmdlet.

Get-SPRSServiceApplication

Modify Reporting Services Rendering Format In SharePoint-Integrated Mode

If you have multiple Reporting Services service applications running in your SharePoint farm, you can either apply the rendering format on all of them, similar to the example on Jaime Tarquino’s MSDN blog post and use the foreach() loop or just apply it on a specific Reporting Services service applications by passing the GUID ID value like in the example below.

New-SPRSExtension -identity 56d8837f-c47c-471d-9017-6b01b8faf8d8 -ExtensionType "Render" -name "TXTspaceDelimited" -TypeName "Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering" -ServerDirectives "<OverrideNames><Name Language='en-US'>TXT (Space Delimited Text File)</Name></OverrideNames>" -ExtensionConfiguration "<DeviceInfo><FieldDelimiter xml_space='preserve'>?</FieldDelimiter><NoHeader>true</NoHeader><FileExtension>txt</FileExtension><ExcelMode>False</ExcelMode><Encoding>ASCII</Encoding></DeviceInfo>"

I had to change the double quotes to single quotes in my XML tags because the parameter values used in the PowerShell cmdlet are surrounded by double quotes.

You need to restart IIS on the application server running the Reporting Services service application for the changes to take effect. To verify, reload the Reporting Services report and check the list of Export options. The rendering format you’ve added should now be listed as an option.

Next Steps

Check out the following items:

Reporting Services Report Server (SharePoint Mode)
Jaime Tarquino’s MSDN blog post on SQL Server 2012 Reporting Services SharePoint integrated mode

Resources

Community

Subscribe