Sharepoint Advanced Tips: Integrating Reporting Services 2008 with SharePoint 2007 Step 6

Step 6 - Report Creation and Deployment in SharePoint 2007 with
Business Intelligence Development Studio (BIDS)

Introduction

This post is the last of a group showing how to use SQL
Server 2008 Reporting Services with SharePoint 2007 aka in SharePoint integrated
mode. It is a tutorial that shows how to create and deploy a SQL Server 2008
Report from Business Intelligence Development Studio (BIDS) and how to consume
this Report.

There are three ways of consuming it:

You can consume it from the report Server Web Site
You can consume it from a SharePoint 2007 site using two ways.
- using RSViewerPage.aspx
- using SQL Server Reporting Services Report Viewer Web Part

In this tutorial, I will show how to create a simple tabular
report, deploy it to a SharePoint site, and consume it using the three
previously mentioned ways. I have precisely detailed operations performed in the
SharePoint environment in order to allow SQL Server and Reporting Services
Specialists that does not know SharePoint very well to be initiated to the
product.

The previous steps are:

href="http://mosshowto.blogspot.com/2009/01/integrating-sql-server-2008-reporting.html">Step
1 - SQL Server 2008 with reporting Services Installation and Configuration
href="http://mosshowto.blogspot.com/2009/01/reporting-services-2008-sharepoint-2007.html">Step
2 - SharePoint Installation
href="http://mosshowto.blogspot.com/2009/01/sharepoint-report-server-2008.html">Step
3 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 1 :
Operations performed with Reporting Services Configuration tool
href="http://mosshowto.blogspot.com/2009/01/integrating-ssrs-2008-sharepoint-2007.html">Step
4 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 2:
Operations performed in SharePoint Central Administration (includes Microsoft
SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint
Technologies Installation)
href="http://mosshowto.blogspot.com/2009/01/adventure-works-2008-installation-sql.html">Step
5 - installing SQL Server 2008 samples: Adventure works

2- Prerequisites

You have installed and configured SQL Server 2008 with reporting services
on the virtual machine with a Windows 2003 Server OS. if not you can refer to
the step 1 of these articles group:
href="http://mosshowto.blogspot.com/2009/01/integrating-sql-server-2008-reporting.html">Step
1 - SQL Server 2008 with reporting Services Installation and Configuration
You have downloaded Windows SharePoint Services 3.0 with Service Pack 1
and have installed it.
If not you can refer to
href="http://mosshowto.blogspot.com/2009/01/reporting-services-2008-sharepoint-2007.html">Step
2 - SharePoint Installation
You have configured report server 2008 for SharePoint integrated Mode with
Report Server Configuration tool.
If not you can refer to
href="http://mosshowto.blogspot.com/2009/01/sharepoint-report-server-2008.html">Step
3 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 1 :
Operations performed with Reporting Services Configuration tool
You have installed Reporting Services Add In for SharePoint and configured
Reporting Services for SharePoint 3.0 Integration in SharePoint Central
Administration
If not you can refer to
href="http://mosshowto.blogspot.com/2009/01/integrating-ssrs-2008-sharepoint-2007.html">Step
4 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 2:
Operations performed in SharePoint Central Administration
You have installed SQL Server Sample 2008 Adventure Works
If not you
can refer to
href="http://mosshowto.blogspot.com/2009/01/adventure-works-2008-installation-sql.html">Step
5 - installing SQL Server 2008 samples: Adventure works

3 - Tutorial Overview

This tutorial is compatible both, with Windows
SharePoint Services 3.0 and MOSS 2007, but in MOSS 2007 you have much more
offered functionalities like data connection library.

As often in most
of my tutorials, the goal is to give user the quicker way of experimenting a
product or a products combination, and thus, I will use BIDS Report Wizard to
create a report as quickly and simply as possible.

Step
1 - Create Report Document Library in a SharePoint Site
href="#Create-Report-with-the-Wizard-of-Business-Intelligence-development-Studio-(BIDS)-">Step
2 - Create Report with the Report Wizard of Business Intelligence development
Studio (BIDS)
href="#Define-Report-properties-to-prepare-deployment-in-Business-Intelligence-">Step
3 - Define Report properties to prepare deployment in Business Intelligence
Development Studio (BIDS)
Step 4
- Build report and deploy report in SharePoint 2007 with Business Intelligence
Development Studio (BIDS)
Step 5 - Check
report deployment in SharePoint 2007 and consume it using
RSViewerPage.aspx
href="#Consume-report-using-SQL-Server-Reporting-Services-Report-Viewer-Web-Part-">Step
6 - Consume report using SQL Server Reporting Services Report Viewer Web
Part
Step 7 -
Consume report using ReportServer Web Site

4 - Tutorial

4.1 Step 1 - name=Create-Report-Document-Library-in-a-SharePoint-Site->Create Report
Document Library in a SharePoint Site

4.2 Step 2 - name=Create-Report-with-the-Wizard-of-Business-Intelligence-development-Studio-(BIDS)->Create
Report with the Wizard of Business Intelligence development Studio (BIDS)

Name
productNumber
Color
StandardCost
ListPrice
Size
Weight

for "Report Server" field, type the URL of your SharePoint Site where
you plan to deploy the report
for "Deployment Folder" field, type the URL of the "Reports" document
Library that we have previously created in the SharePoint site

4.3 StStep 3 - name="Define-Report-properties-to-prepare-deployment-in-Business-Intelligence-">Define
Report properties to prepare deployment in Business Intelligence development
Studio (BIDS)

1 - Define project properties
In BIDS Solution Explorer
pane, right click Report Project1, click "properties".

href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEja7DUM5F4TwtE6-Yy-SoN94y4ELy99FDeR85ulwJUwKJhhpWV8wQS4fSSb6mviBTrSCurOvQDrsyRRsaXirTrm1duivCcM-XRBF3jfeove7eiqefefxKTY2rFmMg6HqAMP4DnDhY8S9ORG/s1600-h/SSRS+CreateReport_24_ReportProperties.JPG">id=BLOGGER_PHOTO_ID_5306862327987093218
style="MARGIN: 0px 10px 10px 0px; WIDTH: 221px; CURSOR: hand; HEIGHT: 320px"
alt=""
src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEja7DUM5F4TwtE6-Yy-SoN94y4ELy99FDeR85ulwJUwKJhhpWV8wQS4fSSb6mviBTrSCurOvQDrsyRRsaXirTrm1duivCcM-XRBF3jfeove7eiqefefxKTY2rFmMg6HqAMP4DnDhY8S9ORG/s320/SSRS+CreateReport_24_ReportProperties.JPG"
border=0>

In "Report Project Property Pages" dialog, in Deployment
Section, fill the following fields that way:

TargetDataSourceFolder: Use the same URL that the one of the Report
Document Library. In MOSS 2007 it would be different, because you can
define special document libraries especially shaped for Data Sources.
TargetDataSourceFolder: It is the URL of the Report Document Library as
defined with the Report Wizard.
TargetServerURL: It is the URL of the SharePoint Site where was created
the "Reports" Document Library, as defined with the Report Wizard.

href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgN9syTUJROB1ymmvrF6lW3PaL6GHs6d4ZlbzbC-WULz8GN2Si82GaGEAj-jgKOFynphHVKoza2Jbft4EIAt1Qffq2QsQv5NIN5srYChUJI1bsSJT_dbbl8HYzfkaLrFR3uI54T1GxvDvsp/s1600-h/SSRS+CreateReport_25_ReportProperties2.JPG">id=BLOGGER_PHOTO_ID_5306862332571934626
style="MARGIN: 0px 10px 10px 0px; WIDTH: 320px; CURSOR: hand; HEIGHT: 210px"
alt=""
src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgN9syTUJROB1ymmvrF6lW3PaL6GHs6d4ZlbzbC-WULz8GN2Si82GaGEAj-jgKOFynphHVKoza2Jbft4EIAt1Qffq2QsQv5NIN5srYChUJI1bsSJT_dbbl8HYzfkaLrFR3uI54T1GxvDvsp/s320/SSRS+CreateReport_25_ReportProperties2.JPG"
border=0>

4.4 Step 4 - name=Build-report-and-deploy-report-in-SharePoint-2007>Build report and
deploy report in SharePoint 2007 with Business Intelligence development Studio
(BIDS)

4.5 Step 5 name=Check-report-deployment-in-SharePoint-2007>Check report deployment in
SharePoint 2007 and consume it using RSViewerPage.aspx

1 - Open the
SharePoint "Reports" document Library.
Go to your SharePoint site, on the
home page in the left Quick Launch Menu click "Reports".

href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwMXIWqFtbrR9w8DeGAUKR3PkAoyh4BALJh5BJkvMH9ki3ZVn6L2HNxbRn5KJLiRMkvP_HBjmnVpo2GWymkyXOoIRtXwjrMUQGelqtBJj_EH_NW2jxsScV3oD0Sxs4W3LB1D4eOdOQU1aE/s1600-h/SSRS+CreateReport_05_CreateDoclibReports.JPG">id=BLOGGER_PHOTO_ID_5306858018679377682
style="MARGIN: 0px 10px 10px 0px; WIDTH: 320px; CURSOR: hand; HEIGHT: 122px"
alt=""
src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwMXIWqFtbrR9w8DeGAUKR3PkAoyh4BALJh5BJkvMH9ki3ZVn6L2HNxbRn5KJLiRMkvP_HBjmnVpo2GWymkyXOoIRtXwjrMUQGelqtBJj_EH_NW2jxsScV3oD0Sxs4W3LB1D4eOdOQU1aE/s320/SSRS+CreateReport_05_CreateDoclibReports.JPG"
border=0>

The "Reports" SharePoint Document Library welcome page is
opening.
You notice that a file corresponding to the .rdl file deployed with
BIDS is present:
Report1

href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYoGk1TwcMKfXQ0RT3FE_iFkC9jl0Rm1XtOBBJWCvlQC4_i2xbnkuVQj7KKFR2oa6VuY867j5Ltc33ZN3BrV52pcCWMQbds2zhc9nJn0dFMZb3jfZIpsXYYjpk8JFph1MPK7-k1ETB4PZa/s1600-h/SSRS+CreateReport_30_CheckDeployment.JPG">id=BLOGGER_PHOTO_ID_5306865070541366594
style="MARGIN: 0px 10px 10px 0px; WIDTH: 320px; CURSOR: hand; HEIGHT: 171px"
alt=""
src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYoGk1TwcMKfXQ0RT3FE_iFkC9jl0Rm1XtOBBJWCvlQC4_i2xbnkuVQj7KKFR2oa6VuY867j5Ltc33ZN3BrV52pcCWMQbds2zhc9nJn0dFMZb3jfZIpsXYYjpk8JFph1MPK7-k1ETB4PZa/s320/SSRS+CreateReport_30_CheckDeployment.JPG"
border=0>

2 - Consume Report1 using RSViewerPage.aspx
Double
click the Report1 link or file icon.
The RSViewerPage.aspx is opening and
the report is generated.

href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjd0P9gMl1bbKrQ1QgTG1d5wbpIW17WTvVuFwFOXdWL3NH1pmuOs6wxxp7kVuL7qLHNlKV-uue_vLgDUedpctEr83IgDkRLGo8A40MbpVXlLwcEBPe9n5qjyvlFS4rFH-xKpBkPt5s0iqiK/s1600-h/SSRS+CreateReport_30_ViewReport.JPG">id=BLOGGER_PHOTO_ID_5306865073046958290
style="MARGIN: 0px 10px 10px 0px; WIDTH: 320px; CURSOR: hand; HEIGHT: 280px"
alt=""
src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjd0P9gMl1bbKrQ1QgTG1d5wbpIW17WTvVuFwFOXdWL3NH1pmuOs6wxxp7kVuL7qLHNlKV-uue_vLgDUedpctEr83IgDkRLGo8A40MbpVXlLwcEBPe9n5qjyvlFS4rFH-xKpBkPt5s0iqiK/s320/SSRS+CreateReport_30_ViewReport.JPG"
border=0>

Do not close the page, but navigate backward to go
back to the SharePoint site. You can do it with you browser, or using the bread
crumb.
The RSViewerPage.aspx has unfortunately not clear navigation controls
linked to the SharePoint site except the bread crumb but you can customize it to
fix this lack.

            Customizing the RSViewerPage.aspx Page

4.6 Step 6 name=Consume-report-using-SQL-Server-Reporting-Services-Report-Viewer-Web-Part->Consume
report using SQL Server Reporting Services Report Viewer Web Part

4.7 Step 7 - name=Consume-report-using-ReportServer-Web-Site->Consume report using
ReportServer Web Site

We are now going to use Report Server Web Site to
consume the same report we have deployed in SharePoint.

1 - Opening the
Report Server Web Site
Open the Report Server Web Site, the URL of which we
defined earlier with Reporting Services Configuration Manager in the third post
of these posts group. (href="http://mosshowto.blogspot.com/2009/01/sharepoint-report-server-2008.html">
Integrating Reporting Services 2008 with SharePoint 2007 Step 3 - Configuring
Reporting Services for SharePoint 3.0 Integration - Part 1)
It should
correspond to this pattern:

                 http;//yourmachinename/ReportServer

Sharepoint Advanced Tips

Wednesday, February 25, 2009

Integrating Reporting Services 2008 with SharePoint 2007 Step 6 - Report Creation and Deployment

No comments:

Post a Comment