SharePoint BI - How to fix SSRS Issues

SQL Server Reporting services can be used and configured in Integrated Mode and Native mode as well. There are some noticeable differences between both modes.

 

SSRS in SharePoint Integrated Mode:

 

•       Use SharePoint document management and collaboration features. A SharePoint site provides a unified portal for accessing and managing all documents in one place.

•       Use SharePoint permissions and authentication providers to control access to reports, models, and other items.

•       Manage reports, models, data sources, schedules, and report history in custom application pages on a SharePoint site. We can set properties, define schedules and subscriptions, and create and manage report history on a SharePoint site the same way you create and manage them from other tools in SQL Server.

•       Publish or upload reports, report models, resources, and shared data source files to a SharePoint library, including Report Center in Office SharePoint Server.

•       Use Report Designer, Model Designer, and Report Builder to create reports and data sources to be published directly to a SharePoint library. We can also use the Upload action on a SharePoint site to add any report definitions and report models to a SharePoint library.

•       Any report that we can run in a native mode report server can run on a report server that is configured for SharePoint integrated mode.

•       Subscribe to and deliver reports to a SharePoint library using a new SharePoint delivery extension. We can also deliver reports through e-mail or to a shared folder. The report server delivery extensions are used to deliver reports. You can create data-driven subscriptions for large-scale report distribution using subscriber data queried at run time.

•       Using a Report Viewer Web Part we can add to SharePoint pages to view a report inside a SharePoint Page. The Web Part includes page navigation, search, print, and export features.

 

SSRS in Native Mode:

•       In native mode, a report server is a stand-alone application server that provides all viewing, management, processing, and delivery of reports and report models. This is the default mode for report server instances.

•       Reports are being hosted in a separate server in Native Mode and report URLs are added in to the SharePoint Pages or Web Parts.

•       When we open a report by clicking the link in SharePoint page, SharePoint redirected to the Report Server URL.

•       When we open a report through SharePoint Web Part, it renders the report by from the link/Report URL provided in SharePoint page

 

In case of some recent patches or for some other reason if we cannot run the reports or are getting errors regarding rendering, encryption of some unknown errors, we may have the check the Reporting services DLLs (Native Mode) or Reporting Services Applications (Integrated Mode) and see if they are working properly. As the SSRS native error fixes are related to SQL Server, lots of the issues can be identified and fixed through SSRS Report server.

But, in case of SSRS in SharePoint integrated mode we have to look into SSRS Service and SSRS service Application. We have to check if the SQL Reporting Service is in running state in Manage Services on Server in SharePoint Administrator.

 

 

Then, we have to check the SSRS Service Application settings (like: execution account, settings etc.). In case of total failure we may have to delete the SSRS service Application and re-create it without deleting the SSRS Database. The steps are as follows:

 

Step 1: Export the Encryption Key to a file

 

Go to SSRS Service Application and select Key Management

Then select Backup Encryption Key

Enter a password

And Save it in a secured location.

 

Step 2: Delete SSRS Service Application

 

Delete existing SSRS Service Application without deleting the SSRS Database (i.e. ReportService_SharePoint). Go to the Application Properties and remember the Application Pool and the SSRS Database Name (Take a snapshot before deleting)

 

Then delete the SSRS service application

 

Step 2: Create SSRS Service Application

 

Create a new SSRS Service Application pointing to the existing SSRS Database (i.e. ReportService_SharePoint).

N.B. Use the previously created application pool with same Service Account.

 

Step 3: Import Encryption Key

 

1.       Import previously exported Encryption Key to SharePoint SSRS service

 

Step 4: Reset Execution Account and IISRESET

 

2.       Reset the Execution Account for SSRS

3.       Do an IISRESET

 

Boom !!!! done…..everything will be back online…..