TechBubbles Microsoft Technology BLOG

SQL Server Reports Integration with SharePoint 2007

This post explains how to Deploy the SQL server Reports to Office SharePoint Server 2007. SQL Server Reporting Services can operate in two modes, either in Native Mode or Share Point Integration Mode.

 

Determining which mode is ideal for Reports deployment

Native Mode

In this mode Reports, Data Sources and Report Models are all stored in Report Server Database. The published reports are executed solely in this environment.

Share Point Integration Mode

The following features are only available in when you deploy your reports in Integration Mode:

    • Now you can use Document Libraries can be used to store reports, data sources and report models.
    • You can use SharePoint Security Model to give permissions to access reports, models and other items.
    • You can get the workflow capabilities around dash board reports.
    • You can build Enterprise Dashboards with rich reports.

It is better to use SharePoint Integration Mode if your organization is already using SharePoint Server 2007.

There is no straight forward mechanism to deploy your existing native mode reports to SharePoint Server Integration Mode.

Pre-Requisites

Integrating Reporting Services with SharePoint server requires following components needs to be installed:

If you are using SQL server 2005 Reporting Services then install the following

  • SQL server 2005 Reporting Services- Hosts the reports and communicate with Office SharePoint Server 2007 using Web Services interface.
  • SQL server 2005 Service Pack2– Enables Office SharePoint Server 2007 and Reporting Services integration
  • Office SharePoint Server 2007
  • Microsoft SQl server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies.

If you use SQL server 2008 then install the SQL server 2008 reporting services for SharePoint.

After installing the above components Both SharePoint Server and Report Server.

Report Server Configuration in SQL server 2005

1. Open the Report Services Configuration tool

image

2. In Report Configuration screen

image

Machine Name could be name of the server where report server instance is installed. For Instance Name, Select the SQL Server Reporting Services instance that you want to configure and then say connect.

3. Specifying the Virtual Directory for Report Server

image

4. Report Manager Virtual Directory will be obsolete after installing SharePoint add in for reporting services.

5. Windows Service Identity – The Report Server Windows service runs under the account that you specified in when installing SQL Server Reporting Services.

image 

6. Web Service Identity- You can specify the application pool where your reporting web service is running on.

image

7. Database Setup – Upgrade the database to suite for SharePoint Integration Mode and change the server mode from Native Mode to SharePoint Integrated Mode as shown below

image

8. SharePoint Integration- If you install the SQL server Reporting Services Add-in for SharePoint Server then the following screen will appear.

image

After completion of the Report Server configuration then you need to configure the SharePoint Server.

SharePoint Server Configuration

1. Open the SharePoint Central Administration Screen and notice the Reporting Services Section Section under Application Management.

image

2. Click the Manage Integration Settings link and configure the details as shown

   below

image

After completion of above SharePoint Server configuration deploy the report as follows

Deploying the Reports to SharePoint Reports Document Library

1. Open your Reporting Services Project and click the properties

image

Specify the TargetServer URL as :/">http://<SharePointServerName>:<port>/

TargetReportFolder as http;//<SharePointServerName>:<port>/<sitename>/<documentLibraryname>   

TargetDataSource Folder can be left blank, By default if you don’t mention the name it automatically takes documentlibrary as datasource.

You can read this BLOG for configuring the SQL server 2008 reporting services on SharePoint Server 2007.

Share this post :

About the author

Kalyan Bandarupalli

My name is kalyan, I am a software architect and builds the applications using Microsoft .NET technologies. Here I am trying to share what I feel and what I think with whoever comes along wandering to Internet home of mine.I hope that this page and its contents will speak for me and that is the reason I am not going to say anything specially about my self here.

Add Comment

TechBubbles Microsoft Technology BLOG

Follow me

Archives

Tag Cloud