SSRS Integration with SharePoint

By: Abin Jaik Antony | Comments | Related: > SharePoint

Problem

The scope of this tip is to explain the architecture, configuration & deployment of SSRS in SharePoint integration Mode.

Solution

Before going directly into the configuration, we can have a look into the architecture . This will help us to understand it deeply.

SSRS architecture in Native Mode and in SharePoint integration Mode.

The SQL Servers Reporting Services architecture have mainly 3 parts /layers

Data Layer
Application Layer
Client Layer

The Data layer consists of the Base Data Sources like data warehouse / transactional DB / Analysis Services Cube , Report Server Databases etc.

The Application layer consists of Reporting Services, Report Manager , Web Services etc.

The Client Layer can be any front end like SharePoint web parts or other front end tools.

After the deployment of reports from BIDS (Business Intelligence Development Studio) , the .rdl (report definition language) files and the shared data sources will be stored inside Report Server database. On the request for any report from a Client , the request passes to the Report Manager and is processed within Report Services from the fetched data of Data Sources. The generated report is returned to the Client from the Application Server as HTML/XML/PDF.

Basically there are two modes for configuring SSRS.

Native Mode
SharePoint Integration Mode.

The outline architecture for Native Mode is explained above. This article will review SharePoint integrated mode's configuration , architecture & deployment.

Before configuring the Report Server in SharePoint Integration Mode ,let's make sure that server is installed with the Report Services Add - in (on the SharePoint Server) and SQL Server SP2 (on database the server).

In SharePoint Integrated mode, the architecture is a little different from Native mode. The reports definition files and Data Sources are stored in the SharePoint Content database Also, there is no concept of "Report Manager" in SharePoint Integrated mode since it is deployed to the SharePoint Reports Library. Security trimming for reports is handled using standard SharePoint permisions.

In SharePoint Integrated mode, when a user makes a request for a particular report, the Report Server Proxy Endpoint will take care of the report processing from SharePoint along with Report Services to return the rendered result.

How to configure Report Server in SharePoint Integration Mode

For configuring the Report Server, go to the Reporting Services Configuration from the Start menu (see image below).

On the "Reporting Services Configuration Manager", connect to the proper report server instance on the machine and ensure that services is running.

Look at the vertical panel on the left hand side of the window. We have to configure each section one by one and apply the changes on each section.

Report Server Virtual Directory

Next we have to create a virtual directory for the Report Server where the report server web services and related processing application needs to deployed.

The Report Manager is not supported with SharePoint Integration mode because the purpose of the report manager will be replaced from the SharePoint end and reports are managed from SharePoint libraries.

Windows Service Identity

Windows Service Identity manages the security account under which the Report Server service runs. By default, this service runs under the NT Authority\Network Service account. You might want to use a domain account instead of a built-in account.

Web Service Identity Configuration

The Web Service Identity Configuration is set by default with the ASPNET Account. But we can configure this account by setting the identity account of the Application pool on the IIS.

Next is setting up the database for the Report Server. Here we need to set a database for storing report snapshots and rdl files (secondary storage for SharePoint Integrated mode). Also, set the Server Mode to SharePoint Integrated Mode.

After applying this step, all the required sections will be configured as shown in the above step.

After the configuration of reporting services in SharePoint Integrated Mode, we need to update the Reporting Services Integration in SharePoint Central Administration. On the Application management tab of Central Admin we can find the "Reporting Services" section. Under this section go to the "Manage integration settings" and give the Report Server URL .

Deployment of a SSRS Reports to a SharePoint Reports Library.

The deployment of SSRS reports will be published to a SharePoint Reports Library. The rdl files & shared data source files will be stored inside this library. Prior to the deployment of the SSRS Project, create a report library on the SharePoint Site and a folder named "DataSources" inside this library.

Follow the steps below for the deployment.

Build the SSRS Project. If the Build succeeded, proceed as follows:

Right Click on the SSRS Project and select "Properties".
On the properties window , enter the values for:

TargetServerURL - The URL to the SharePoint Site.
TargetReportsFolder - The URL to the "Reports" Library.
TargetDataSourceFolder.- The URL to the "DataSources" folder in the Reports Library.

Click OK and Deploy the Project.
After the deployment the Reports Library will be populated with rdl files created on the SSRS project.

Next Steps

Try to configure SSRS in SharePoint Integration, compare yourself the differences between Native Mode and SharePoint Integration Mode.
Deploy your sample SSRS Reports into a SharePoint Reports Library.
Try to view deployed SSRS Reports on SSRS ReportViewer Web Part that comes along with SharePoint Reporting Services Add-in.