Configuring SQL Reporting Services 2005 with Windows SharePoint Services 2003

Introduction

This article explains how to integrate SQL Reporting Services with Windows SharePoint Services 2.0.
The PreBuild Microsoft's Reporting Services WebPart is used to be configured to display SQL Reports
which is to be fetch data from SQL Reporting Services.

Introduction to SQL Reporting Services

SQL Server 2005 Reporting Services is a server-based reporting platform that you can use to create and
manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional
data sources. The reports that you create can be viewed and managed over a World Wide Web-based connection.

Configuring SQL Reporting Services 2005 with WSS 2003

Prerequisites

1. SQL 2005 Reporting Services
2. RSWebParts.cab RsWebParts Setup: Execute the following commands in shell to install Reporting Services web part to Windows SharePoint services sites.

stsadm.exe -o addwppack -force –filename "C:\Program Files\Microsoft SQL Server\80\Tools\Reporting Services\SharePoint\RSWebParts.cab"

Creating SQL Report

Steps:

1. Open SQL Server Business Intelligence Visual Studio 2005.
2. Create new Report Server Project.

3. In Solution explorer Right click Shared Data sources and select add new data source.

4. Enter the name of the data source and select the type of data source.
Now the SharePoint list is retrieved through web service. So its xml type.

5. Enter the web service URL as connection string for the data source.

6. Right click the Reports in solution explorer and select add new report.

7. Follow the Reports wizard and choose the data source.

8. Connection string is to be in the xml soap format.

<Query>
    <SoapAction> <a style="COLOR: blue; TEXT-DECORATION: underline; text-underline: single" href="http://tempuri.org/GetEmpInfo">http://tempuri.org/GetEmpInfo</a> </SoapAction> 
</Query>

9. Choose the report fields from the data columns.

10. Save the Report.

Deploying Reports in SQL Reporting Services

Steps

1. Right click the project and choose properties

2. Enter the Report server URL in the TargetServerURL

(For e.g. http://10.230.207.40/ReportServer$SQL2005)

2. Right click the solution and select Build then Deploy the solution.

Integration with Windows SharePoint Services

Steps

1. Create New Web part Page in WSS.

2. Insert the Report Viewer Web Part form Virtual Server Gallery.

3. Select Modify Shared web part and Enter the report URL.

Report Preview

SQL Report Successfuly Generated in SharePoint WebPart

I hope this article would be helpful to the Developers working on SharePoint integration with SQL Reporting Services 2005.