Step by Step process of Integrating SSRS to SharePoint 2010

Using Reporting Services in Sharepoint 2010.

What is SSRS ?

SQL Server Reporting Services 2008 (SSRS) is a feature included in the SQL Server 2008 product. SSRS is used to design, develop, test, and deploy reports. When SSRS is installed, there are two databases i.e. ReportServer$SQLSTANDARD and ReportServer$SQLSTANDARDTempDB created by default. You can also configure these databases manually using the Reporting Services Configuration Manager. Also a developer-centric tool called Business Intelligence Development Studio (BIDS) is installed automatically, which can be used through Visual Studio 2008. BIDS uses Report Designer, a developer tool for building complex reports. Report Builder 3.0 on the other hand provides a simpler user interface for creating reports, directed primarily at business users rather than developers.

Prerequisite to integrate SSRS with SharePoint 2010.

1) Download and Install the prerequisite SSRS 2008 R2 Add-in for SharePoint along with other required prerequisites before installing the Sql server 2008 R2. 

Note: Only a standard edition of Sql Server 2008 R2 or higher should be installed for integrating Reporting Services with SharePoint.

2) While installing Sql server 2008 R2, check Reporting Services, and Business Intelligence Development Studio on the Feature selection page. Also choose the option Install the SharePoint integrated mode default configuration on the Reporting Services Configuration page.

Note: Once the installation of Sql server 2008 R2 is completed for verification purposes, click on Start-> All Programs->Microsoft SQL Server 2008 R2->Configuration Tools->Reporting Services Configuration Manager. Enter the name of your server and the report server instance name and click Connect. The Report Service Status should show as started.

From the LHS click on Service account, choose use another account and enter the credentials of the windows domain user account to run the report service.

From the LHS click on Web Service Url and copy the url given at the bottom of the page.

Install the Sharepoint Server.

3)Go to Central Administration->General Application Settings->Reporting Services->Reporting Services Integration

 In the Report Server web service url textbox, paste the url that you copied earlier.Choose Windows Authentication in authentication mode. Enter the appropriate credentials and choose activate feature in all existing site collections and click Ok.

If everything has been configured correctly we will be shown a confirmation message that everything was successful.

4)Using Reporting Services in a Sharepoint site.

Go to your site -->Site Action->Site Settings->Site Collection Adminisration->Site Collection Features.Ensure that Report Server Integration Feature and Reporting Feature are Active.

Creating SSRS Reports in a Document Library

1)Create a new document library by name SSRS .

Click on Library Tab->Library Settings->General Settings->Advanced Settings->choose "Allow Management of Content Types". Click Ok.

In the Content Types section, click on the Add from existing site content types link. Select Report Server Content Types .Then select Report Builder Model, Report Builder Report, and Report data source content types from the Available Site Content Types list to be added, click on Add and then click Ok.

So under New Document Option of the Document tab under Library Tools we can see the options as seen in the below screenshot.

Now we are ready to create reports.

Example 1:- Using Report Builder 3.0 to create a report based on a Sharepoint List.

Create a List (Courses) with columns (CourseName, No of Students). Add items to the list.

Click on the Document Library (SSRS).

Go to Library Tools-->Documents->New Document->Report Builder Report.

This will open up Report Builder 3.0.

Choose New->Chart Wizard->Create a Dataset->Next. Click New.

In Select Connection type, from the Drop Down choose Microsoft Sharepoint List and type the connection string as the url of your site(ex: http://server:8000).

On the LHS, click on Credentials.

Choose "Use this username and password" and enter your credentials. Also check “Use as windows credentials”. Click Ok.

Click Test Connection and it will show succeeded. Click Next.

From the LHS expand Courses list and check on CourseName and No of Students.

Click Next. Click on Bar (i.e. Chart Type). Click Next.

From the list of Available fields drag CourseName under Categories and No of Students under Values. Click Next.

Select a style and click Finish. Click on Run. The report is displayed in the Design Mode.

Click on Design to switch back to Design View. You can change the Title of the Chart and also the Axis Titles.

Click on the Y Axis Title, right click->Choose Axis Title Properties and Change Title to Course Name.

Do the Same for X Axis Title and change the Title to No of Students.

Click on Save to save the report in the SSRS document library. The report generated will be similar to the below screenshot.

Example 2) Using Business Intelligence Development Studio (Visual Studio 2008) to create a report from a table present in Sql Server 2008 R2 and deploying it to Sharepoint 2010.

Create a Database by name demo and a Table by name Employee with the definition as shown below.

Column 1 – EmployeeName (varchar (10)), Not Null

Column 2-ProjectName (varchar(10)), Not Null

Add data to the table. Launch Visual Studio 2008.

Go to File->New Project->Report Server Project Wizard.

Type a Name and Location for your Report Project. Click Ok. The Report Wizard opens up. Click Next.

Give a name for the Datasource. In the Type box, choose Microsoft Sql Server.

Click on Edit. Enter Server Name (Ex: server\sqlstandard). Choose Windows Authentication.

Enter the Database name as demo, click on Test Connection and it will show succeeded.

Click on Ok. Click Next.

Type Query as

SELECT COUNT(*) AS 'Number of Employees', ProjectName

FROM    Employee

GROUP BY ProjectName

Click Next. Choose Tabular. Click Next.

From the List of Available Fields , drag Number of Employees under Group. Click on Finish.

Enter the Name of the Report and click on Finish.

Click on Build. After Build succeeds, click on Debug to see the Report.

Now the final step is to deploy the report into the Sharepoint.

Right click on the Project in the Solution Explorer->Properties. Change as given below:-

TargetDataSourceFolder--http://server:8000/SSRS

TargetReportFolder--http://server:8000/SSRS

TargetReportPartFolder--http://server:8000/SSRS

TargetServerUrl--http://server:8000

Click Apply and then OK. Click Build->Deploy.

If Deploy succeeds, you will see the report published under the document library SSRS in your site. The Report will look similar to the below screenshot.

Example 3- Use Report Viewer Web Part

Go to the Home Page of your top level site. Click on Edit-> Insert->WebPart.

From the Categories List, choose SQL Server Reporting and click on Add.

Click on the link “Click here to open the tool pane”.

In the Tool Pane on the RHS side, click on the Ellipses under Report to choose a report that you want to add as a webpart.

Navigate to the document library SSRS and select a report. Click Ok. Click Apply and click Ok once again.

Click on Save and Close. The Home Page will now show the selected Report as a webpart.