Implementing a custom Rendering Extension in SSRS in SharePoint integrated mode without coding

A rendering extension is a component or module of a report server that transforms report data and layout information into a device-specific format. SQL Server Reporting Services includes six rendering extensions: HTML, Excel, Word, CSV or Text, XML, Image, and PDF.
We can create additional rendering extensions to generate reports in other formats.

We can modify the rendering format of a Reporting Services report by making changes to the RSReportServer.config file located at :

In native mode the files are located at:
C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER

In SharePoint integrated mode the files are located at:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

More details can be found here

<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>

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 above.

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. The powershell details:

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>”

We have to change the double quotes to single quotes in XML tags because the parameter values used in the PowerShell cmdlet are surrounded by double quotes.
This eliminates parsing errors when running the command.

TheGUID value comes from the ID value of the Reporting Services service application when you run the Get-SPRSServiceApplication PowerShell cmdlet as follows:

Get-SPRSServiceApplication

We 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 we’ve added should now be listed as an option.

Reference 1

Reference 2

Advertisements

SharePoint List Attachments over 50MB need more than an increase in Maximum Upload Size – httpRuntime maxRequestLength

Reference

The SSRS report (SharePoint integrated mode) was giving error. While researching for this error came across the above article.

1.Connect to Central Admin
2.Navigate to Central Admin > Application Management > Web Application General Settings
3.Select your web application
4.Set the Maximum Upload Size value to “X” MB and hit OK.
5.Repeat steps 6-7 for all zones for your web application on all servers hosting the web application role
6.Open the web.config
7. Replace the following line:<httpRuntime maxRequestLength=”51200″ />with<httpRuntime maxRequestLength=”{X * 1024}” />

if you’re using SQL storage for your SharePoint content, the maximum useful value for the maxRequestLength should be 2097152 (2GB in kilobytes = 2 * 1024 * 1024). Keep in mind that other limits may affect large uploads, most notably:

SharePoint 2013 correlation id not found on SharePoint farm – Enable job Diagnostic Data Provider: Trace Log and query view ULSTraceLog

Today for one of the SSRS report (SharePoint integrated) the export to Excel failed and it gave the correlation id which we were unable to find on the various nodes of SharePoint farm.

So using Central Admin we enabled job “Diagnostic Data Provider: Trace Log” by navigating to Monitoring => Review Job definitions. On SQL server execute following query against view Logging.dbo.ULSTraceLog:

SELECT [MachineName],
[Area],
[Category],
[Level],
[EventId],
[Message],
[CorrelationId]
FROM ULSTraceLog
WHERE [CorrelationId] = ‘9990009d-cccc-xxxx-07xx-xx99f9999xx9’

Powershell script to list all the SharePoint 2013 integrated SSRS 2012 subscription details

MSDN reference

Using PowerShell script we can find all the SharePoint 2013 integrated  SSRS 2012 subscription details:

Script:

# Parameters
# server – server and instance name (e.g. myserver/reportserver or myserver/reportserver_db2)

Param(
[string]$server,
[string]$site
)

$rs2010 += New-WebServiceProxy -Uri “http://$server/_vti_bin/ReportServer/ReportService2010.asmx&#8221; -Namespace SSRS.ReportingService2010 -UseDefaultCredential ;
$subscriptions += $rs2010.ListSubscriptions($site); # use “/” for default native mode site

Write-Host ” ”
Write-Host “—– $server’s Subscriptions: ”
$subscriptions | select Path, report, Description, Owner, SubscriptionID, lastexecuted, Status

powershell c:\scripts\ListAll_SSRS_Subscriptions.ps1 "[server]/_vti_bin/reportserver" "http://[server]" 

Install SSRS 2016 Reporting Services Service Application for SharePoint 2016

There are 3 primary steps:

Step 1: Install Reporting Services Report Server in SharePoint mode

Step 2: Register and Start the Reporting Services SharePoint Service

Step 3: Create a Reporting Services Service Application

MSDN Reference

Step 1: Install Reporting Services Report Server in SharePoint mode

This step installs a Reporting Services report server in SharePoint mode and the Reporting Services add-in for SharePoint products. This comprises of 2 steps:
1.1 Apply Custom server Role
1.2 Install Reporting Services

1.1 Apply Custom server Role

For SharePoint 2016, the SharePoint server that Reporting Services will be installed on needs to have the Custom server role. The deployment of Reporting Services will succeed on a SharePoint server that is not in the Custom role, but during the next SharePoint maintenance window, MinRole will stop the Reporting Services service because it detects that Reporting Services in SharePoint-integrated mode does not indicate support for any of the other SharePoint server roles. The Reporting Services service application only supports the Custom role.

NOTE 1 : If you plan to install the Power Pivot service as well, on SharePoint 2016, install that prior to installing Reporting Services. The Power Pivot service cannot be installed on a SharePoint server in the Custom role. This will avoid having to switch roles multiple times.

NOTE 2 : This does not apply to SharePoint 2013.

  1. Log onto the SharePoint server that you plan to install SSRS.
  2. Launch the SharePoint 2016 Management Shell as an adminsitrator. You can right-click on the SharePoint 2016 Management Shell and select Run as adminsitrator.
  3. From the PowerShell command prompt, run the following command: Set-SPServer SERVERNAME -Role Custom.
  4. You should see a response that a timer job was scheduled. You will need to wait for the job to execute.
  5. Use the following command to verify the server’s assigned role: Get-SPServer SERVERNAME.
  6. The role should list Custom.

1.2 Install Reporting Services

    1. Run the SQL Server Installation Wizard (Setup.exe).
    2. Select Installation in the left side of the wizard and then select New SQL Server stand-alone installation or add features to an existing installation.
    3. If you see the Product Key page, type your key or accept the default of the ‘Enterprise Evaluation’ edition.

      Select Next.

    4. If you see the License terms page, review and accept the license terms. Microsoft appreciates you agreeing to send feature usage data to help improve product features and support.

      Select Next.

    5. It is recommended that you select Use Microsoft Update to check for updates (recommended). This is optional.

      Select Next.

    6. On the Instal Setup Files page, depending on what is already installed on your computer, you might see the following message:
      • “One or more affected files have operations pending. You must restart your computer after the setup process is completed.”
      • Select Next.
    7. If you see the Install Rules page. Review any warnings or blocking issues. Then select Next.
    8. Select the following on the Feature Selection page:
      • Reporting Services – SharePoint
      • Reporting Services add-in for SharePoint Products.
          • You could optionally also select Database Engine Services for a complete environment, however you should have a SQL Server Database Engine instance that is hosting the SharePoint databases.

          Select Next.

          rs_SetupFeatureSelection_SharePoint_with_circles

    9. If you selected the Database Engine services, accept the default instance of MSSQLSERVER on the Instance Configuration page and click Next.

      noteThe Reporting Services SharePoint service architecture is not based on a SQL Server “instance” as was the previous Reporting Services architecture.

    10. If you see the Server Configuration page type appropriate credentials. If you want to use the Reporting Services data alerting or subscription features, you need to change the Startup Type for SQL Server Agent to Automatic. You may not see the Server Configuration page, depending on what is already installed on the computer.

      Select Next.

    11. If you selected the Database Engine services, you will see the Database Engine Configuration page, add appropriate accounts to the list of SQL Administrators and select Next.
    12. On the Reporting Services Configuration page you should see the Install only option is selected. This option installs the report server files, and does not configure the SharePoint environment for Reporting Services.
      1. ssRS-2016-setup-configuration
      2. Review any warnings and then select Next on the Feature Configuration Rules page if you stop on this page.

On the Ready to Install page, review the installation summary. The summary will include a Reporting Services SharePoint Mode child node that will show a value of SharePointFilesOnlyMode. Select Install.

  1. The installation will take several minutes. You will see the Complete page with the features listed and the status of each feature. You may see an information dialog indicating the computer needs to be restarted.

Step 2: Register and Start the Reporting Services SharePoint Service

NOTE 2.1 : If you are installing into an existing SharePoint farm, you do not need to complete the steps in this section. The Reporting Services SharePoint service is installed and started when you ran the SQL Server installation wizard as part of the previous section of this document.

You can also start the service from SharePoint central Administration rather than running the third PowerShell command. The following steps are also useful to verify that the service is running.

  1. In SharePoint Central Administration, click Manage Services on Server in the System Settings group.
  2. Find SQL Server Reporting Services Service and click Start in the Action column.
  3. The status of the Reporting Services service will change from Stopped to Started. If the Reporting Services service is not in the list, use PowerShell to install the service.

The following steps guide you through opening the SharePoint Management Shell and running PowerShell cmdlets:

  1. Select the Start button
  2. Select the Microsoft SharePoint 2016 Products or Microsoft SharePoint 2013 Products group.
  3. Right-click SharePoint 2016 Management Shell, or SharePoint 2013 Management Shell, select Run as administrator.
    System_CAPS_ICON_note.jpg Note
    The SharePoint commands are not recognized in the standard Windows PowerShell window. Use the SharePoint Management Shell.
  4. Run the following PowerShell command to install the Reporting Services SharePoint service. A successful completion of the command displays a new line in the management shell. No message is returned to the management shell when the command completes successfully:
    Install-SPRSService  
    
    
  5. Run the following PowerShell command to install the Reporting Services service proxy. A successful completion of the command displays a new line in the management shell. No message is returned to the management shell when the command completes successfully:
    Install-SPRSServiceProxy  
    
    
  6. Run the following PowerShell command to start the service or see the following notes for instructions on how to start the service from SharePoint Central administration: get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance


Step 3: Create a Reporting Services Service Application

  1. In SharePoint Central Administration, in the Application Management group, select Manage Service Applications.
  2. In the SharePoint ribbon, select the New button.
  3. In the New menu, select SQL Server Reporting Services Service Application..
  4. In the Create SQL Server Reporting Services Service Application page, enter a name for the application. If you are creating multiple Reporting Services service applications, a descriptive name or naming convention will help you organize your administration and management operations.
  5. In Application Pool section, create a new application pool for the application (recommended). If you use the same name for both the application pool and the services application, it can make ongoing administration easier. This can also be affected by how many service applications you will create and if you need to use several in a single application pool. See the SharePoint Server documentation on recommendations and best practices for application pool management.

    Select or create a security account for the application pool. Be sure to specify a domain user account. A domain user account enables the use of the SharePoint managed account feature, which lets you update passwords and account information in one place. Domain accounts are also required if you plan to scale out the deployment to include additional service instances that run under the same identity.

  6. In the Database Server, you can use the current server or choose a different SQL Server.
  7. In Database Name the default value is ReportingService_<guid>, which is a unique database name. If you type a new value, type a unique value. This is the new database to be created specifically for the services application.
  8. In Database Authentication, the default is Windows Authentication. If you choose SQL Authentication, refer to SharePoint documentation for best practices on how to use this authentication type in a SharePoint deployment.
  9. In the Web Application Association section, select the Web Application to be provisioned for access by the current Reporting Services Service Application. You can associate one Reporting Services service application to one web application. If all of the current web applications are already associated with a Reporting Services service application, you see a warning message.
  10. Select OK.
  11. The process to create a service application could take several minutes to complete. When it is complete, you will see a confirmation message and a link to a Provision Subscriptions and Alerts page.