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

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

SQL Server Reporting Services Integration with SharePoint


By:   |   Read Comments   |   Related Tips: > 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.

ssrs architecture diagram

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.

  1. Native Mode
  2. 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.

SharePoint Integrated mode

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

configuration tools

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

configure report server

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.

configure report server

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.

configure report server

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.

web service identiy

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.

database connection

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 .

reporting services

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.

  1. 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.
      adventure works
  2. Click OK and Deploy the Project.
  3. After the deployment the Reports Library will be populated with rdl files created on the SSRS project.
reports
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.


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Abin Jaik Antony Abin Jaik Antony

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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools