Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Modify SQL Server Reporting Services Rendering Format In SharePoint-Integrated Mode


By:   |   Read Comments (1)   |   Related Tips: > Reporting Services Configuration

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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.

You need to restart IIS on the application server running the Reporting Services service application for the changes to take effect.
Next Steps

Check out the following items:



Last Update:


signup button

next tip button



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, October 29, 2014 - 1:47:16 AM - Nitin Back To Top

sir,

i want to create a database for pay scales of school mgmt system. please help me sir how I create database procedure for this


Learn more about SQL Server tools