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:
The previous steps are:
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
The previous steps are:
- Step 1 - SQL Server 2008 with reporting Services Installation and Configuration
- Step 2 - SharePoint Installation
- Step 3 - Configuring Reporting Services for SharePoint 3.0 Integration - Part 1 : Operations performed with Reporting Services Configuration tool
- 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)
- 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:
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
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
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
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
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
- Step 2 - Create Report with the Report Wizard of Business Intelligence development Studio (BIDS)
- 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
- Step 6 - Consume report using SQL Server Reporting Services Report Viewer Web Part
- Step 7 - Consume report using ReportServer Web Site
4 - Tutorial
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.
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:
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,
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
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:
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.
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:
Report1
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. 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.
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.
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.
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
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
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.
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:
Report1
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. 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.
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:
http://yourmachinename/ReportServer
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.
