Wednesday, February 25, 2009

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

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


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:
2- Prerequisites
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.

4 - Tutorial

4.1 Step 1 - Create Report Document Library in a SharePoint Site
1 - Open the SharePoint Site we have created in the second post of these posts group

2 - Click "Site Actions", then click Create

3 - On "Create" page in "Libraries" column, click "Document Library"

4 - Type "Reports" as a name for the Document Library, and select "None" for Document Template, click "Create"

In the Quick Launch Menu of your SharePoint Site you notice a new link corresponding to the Document Library you have just created.

4.2 Step 2 - Create Report with the Wizard of Business Intelligence development Studio (BIDS)
1 - Open Visual Studio 2008.

In Start Menu, point All Programs, point Microsoft Visual Studio 2008, click Microsoft Visual Studio 2008.

2 - Choose a new project
In File Menu, point "New", Click "Project".

3 - Select "Report Server Project Wizard", click "OK"

4 - Pass Report Wizard Welcome dialog
click "Next"

5 - Edit Data Source
In Report Wizard "Select Data Source" dialog click "Edit"

6 - Choose and test Data Source
In "Server Name" drop down list, choose your server.
In "select or enter a database name" drop down list, select "AdventureWorksLT2008"
Click "Test Connection"
Click "OK" on the "Test results" message box.

7 - Close the "Connection Properties" windows.
Notice that the "Connection string" field is filled,
Click "Next"

8 - Open Query Builder On "Design the Query" page, click "Query Builder" button.

9 - Add a Data base Table.
In the Query Designer window, click the "Add Table" icon.

On the "Add Table" Pop Up select "Product (SalesLT)".

10 - Select the table columns
In the "Product (SalesLT)" table control, select the following columns:
  • Name
  • productNumber
  • Color
  • StandardCost
  • ListPrice
  • Size
  • Weight
Click OK

11 - Close Query Builder
Notice that the Transact SQL request has been generated and appears in the "Design the Query" dialog.
click Next

12 - Select the Report Type In "Select the Report Type" dialog, select "Tabular", click "Next"

13 - Pass "Design the Table" dialog
Click "Next".

13 - Choose the Table Style
In "Choose the Table Style" dialog select "Forest", click "Next".

14 - Choose the Deployment Location In "Choose the Deployment Location" dialog,
  • 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
Click Next

14 - Define the Report name
In "Completing the Wizard" dialog, type the report name (I let the default name).

15 - Click "Finish" to close the wizard, and go to the main window of BIDS.

16 - Preview the report in BIDS.
In BIDS click the Preview tab.

Click the Design Tab
4.3 StStep 3 - 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".

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.

4.4 Step 4 - Build report and deploy report in SharePoint 2007 with Business Intelligence development Studio (BIDS)
1- Launch Build action
In BIDS Top Menu, click build, then click Build report Project1.

2- Check Build action
In BIDS Output pane, check the Build result.

3- Launch Deployment
In BIDS Top Menu, click build, then click Deploy report Project1.

4- Check Deployment
In BIDS Output pane, check the Deployment result.

4.5 Step 5 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".

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

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

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 Consume report using SQL Server Reporting Services Report Viewer Web Part
1- Create a Web Part page
Click "Site Actions", then click Create

On "Create" page in "Web Pages" column, click "Document Library"

In "New Web Part Page" page, type the Name (TestReport), select any template, select "Shared Document" as the document library that will welcome your new web part page, then click "create".

The Web Part page is created and is opening in Edit Mode

2 - Add and Configure SQL Server Reporting Services Report Viewer Web Part

In the "TestReport" web part page opened in edit mode, click any "Add Web Part" button.

The Add Web Part Pop Up is opening, and notifies the zone where the Web Part will be added.
Scroll the Pop Up and locate SQL Server Reporting Services Report Viewer Web Part, select it and click "Add".

Notice that the Web Part is now embedded in you Web part page.
Click the "Click Here To open the tool pane" link.

In the tool pane, click the "..." browse button next to the "Report" input field.
The "Select an Item" Web Page Dialog is opening.

With the "Up" icon link navigate up to display the SharePoint site Document Libraries list, then click the "Reports" Document Library, select the "Report1" previously deployed report and click "OK" or double click the "Report1".

Notice that you can see the ".rdl" extension in the "Location (URL)" input field.
Now the "Report" input field of Web Part Tool Pane is filled with the Report URL.

Click "Apply" the report is being generated.

Click the "Exit Edit Mode" link to display the page in normal mode.

You can now see the second way of displaying a report in SharePoint, using SQL Server Reporting Services Report Viewer Web Part. You notice that in this way, you keep the possibility to navigate in the SharePoint site, while this is not possible with the RSViewerPage.aspx.

4.7 Step 7 - 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. ( 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:

Click the SharePoint Site URL

You will see the different Document Libraries of your SharePoint Site. (You should not see the "Data Connections" link if you have precisely followed these tutorial instructions).
Click the "Reports" Document Library link.

You will see the previously deployed "Report1.rdl" file.
Click that "Report1.rdl" link.

Your report has been generated for the third time, using the third way of displaying a report in SharePoint Integrated Mode.