A Complete Guide for Installing SQL Server Reporting Services (SSRS) 2008 in SharePoint 2010 Integrated Mode

This article can be a complete reference for a ground-up approach on installing SQL Server Reporting Service (SSRS) 2008 in SharePoint Integrated mode for SharePoint 2010.

We can make a report server handshake with SharePoint product and use the collaboration and centralized document management features of SharePoint for reports and other Reporting Service content types. This SharePoint integration requires Reporting Services add-in on the SharePoint Web Front Ends.

To start with, download the SharePoint Add-in (rsSharepoint.msi) for SSRS 2008 (http://www.microsoft.com/en-in/download/details.aspx?id=793)

Open the command prompt with ‘Run as administrator’ privilege and run rsSharepoint.msi to start the installation wizard. This wizard would lead you through Welcome page, Software license terms and proceeds by copying the files to SharePoint 2010 folders in %program files%\common files\Microsoft Shared\.

After installing rsSharepoint.msi, let us start configuring SQL Reporting Services

Go to Microsoft SQL Server 2008 R2 à Configuration Tools à Reporting Services Configuration Manager

Web Service URL

Click on Connect, this will display the Reporting Services Configuration Manager window. Click on Web Service URL to configure the IIS depending on the virtual directory name.

Report Server Database

Click on Database, and choose Change Database.

If you already have a report server database, choose the Choose an existing report server database. For this article we will create a new database by selecting Create a new report server database

Type the Server Name, the server where the Report Data needs to reside. Fill the corresponding Authentication Type based on the chosen server.

Click on Next, and give a Database Name. In Report Server Mode, choose SharePoint Integrated Mode. This option would configure the server to store the report files in SharePoint and not in report database.

Click on Next, and select the Authentication Type and it’s corresponding credentials. Authentication Type can also be Windows Authentication

Click on Next and review the data that was entered.

Click on Next and watch the Report Server Database being configured.

SharePoint – Reporting Services Integration

Go to SharePoint 2010 Central Administration à General Application Settings and choose Reporting Service Integration

Copy the Web Service URL from Reporting Services Configuration Manager and paste it in Report Server Web Service URL textbox. In this screen there is an option to activate Reporting Services Feature for all site collections or for specific site collections.

On click of OK, activation confirmation message is displayed.

Integrate Report Server

In General Application Setting page, select Add a Report Server to the Integration. Input the Report Server name and click on OK. This completes the SharePoint integration.

Confirm Report Server Integration Feature Activation

In Site Actions, select Site Settings. In Site Settings page, select Site Collection Features

The status would be Active. This gives a confirmation that the integration was a smooth sail.

Upload RDL & RSDS to SharePoint Document Library

Let us start by creating a new Document Library by selecting Site Actions à More Options. In Create panel, filter by Library and select Document Library. In the textbox at right side, enter the library name.

In Library tab, select Library Settings. Click Advance Settings, select Yes for Allow management of content types and click on Ok.

· Under Content Types section, click on Add from existing site content types.

· In Select site content types from dropdown, select Report Server Content Types.

· Select Report Builder Report and Report Data Source and click on Add.

· Close this screen by clicking on Ok

· Under Content Types section, click on Document in Content Type column.

· In the new screen, Select Delete this content type and accept the confirmation message

· Now click on the newly created Document Library & Upload / Create RSDS & RDL files

From the Document Library, users will be able to manage the data source and RDL files. While publishing the reports from BIDS, RDS files are converted to RSDS files. Uploading RDS files will give error.

Upload can be used after publishing it in SharePoint server. For instance, we can upload the document published in Development Server to QA and Production Server

On selecting the corresponding ECB menu, we will be able to preview the report directly by clicking on it or configure the report or the data source.

And this confirms our SSRS installation.

Shortly I would be releasing articles on Manage Subscriptions and Manage Parameters, few good brownies in SSRS. Till then happy development!!!