Sharepoint Advanced Tips: Create a Report from an Analysis Services 2008 Database and deploy it in SharePoint 2007

Creating and deploying a Reporting Services 2008 report bound to an SQL
Server 2008 Analysis Services Data Source in SharePoint 2007

style="FONT-SIZE: 16px; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #333; PADDING-TOP: 10px">Introduction

style="PADDING-RIGHT: 5px; PADDING-LEFT: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 5px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">The
goal of this tutorial is to show how to use an SQL Server 2008 Analysis Services
Database to generate an SQL Server 2008 Report and how to deploy it in
SharePoint 2007.

So we are going to use together three of the most famous Microsoft products at the moment:

style="MARGIN-TOP: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; MARGIN-BOTTOM: 5px; COLOR: gray; FONT-FAMILY: arial; TEXT-ALIGN: justify">SQL Server Analysis Services 2008

style="MARGIN-TOP: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; MARGIN-BOTTOM: 5px; COLOR: gray; FONT-FAMILY: arial; TEXT-ALIGN: justify">SQL Server Reporting Services 2008

style="MARGIN-TOP: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; MARGIN-BOTTOM: 5px; COLOR: gray; FONT-FAMILY: arial; TEXT-ALIGN: justify">SharePoint 2007

style="PADDING-RIGHT: 5px; PADDING-LEFT: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 5px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">
I have written SharePoint 2007 on purpose since this tutorial is usable both for MOSS 2007 and Windows SharePoint Services 3.0.
As we use SQL Server Reporting Services 2008 AND SharePoint 2007, SSRS 2008 has of course been installed in SharePoint Integrated Mode.

style="FONT-SIZE: 16px; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #333; PADDING-TOP: 10px">Prerequisites

style="MARGIN-TOP: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; MARGIN-BOTTOM: 5px; COLOR: gray; FONT-FAMILY: arial; TEXT-ALIGN: justify">You
have a complete environment to generate and deploy reports with SQL Server
Reporting Services 2008 in SharePoint Integrated Mode, so including SQL Server
Reporting Services 2008, SharePoint 2007 and BIDS.

style="MARGIN-TOP: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; MARGIN-BOTTOM: 5px; COLOR: gray; FONT-FAMILY: arial; TEXT-ALIGN: justify">You
have installed SQL Server Analysis Services 2008 on this environment

style="MARGIN-TOP: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; MARGIN-BOTTOM: 5px; COLOR: gray; FONT-FAMILY: arial; TEXT-ALIGN: justify">You
have installed SQL Server 2008 Databases Samples Adventure Works

style="PADDING-RIGHT: 5px; PADDING-LEFT: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 5px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">
If you miss any of these features you can find within that blog, articles describing the required steps with screen shots and references to MSDN and Technet documentation to install and configure them either in a single or a multiple server environment.

(See previous posts January, February, March 2009. this is the first article of the serie: Integrating Reporting Services 2008 with SharePoint 2007 Step 1 - SQL Server 2008 Installation )

style="FONT-SIZE: 16px; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #333; PADDING-TOP: 10px">Tutorial
overview

style="PADDING-RIGHT: 5px; PADDING-LEFT: 5px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 5px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">The
first step of this tutorial is quite long because you have to perform the several operations
described in the href="http://technet.microsoft.com/en-us/library/ms170208.aspx">Technet Analysis
Services Tutorial.
Then using the BIDS Report Wizard you will create a
Report bound to an SQL Server 2008 Analysis Services datasource.
You will then deploy it in
SharePoint 2007 and check the deployment within a SharePoint site.

The
required steps are the following:

Step 1 Analysis Services
Tutorial
Step 2 Analysis Services Tutorial checking
Step 3 Creating
Reporting Services Project
Step 4 Selecting the data Source
Step 5
Designing the Analysis Services Query
Step 6 Passing through next 3 Wizard
dialogs
Step 7 Defining Deployment Locations
Step 8 Closing the
Wizard
Step 9 Report changes and preview in BIDS
Step 10 Defining the
report Datasource location in BIDS
Step 11 Deploying the report in SharePoint
2007 with BIDS
Step 12 Checking the report deployment in SharePoint
2007

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
1 Analysis Services Tutorial

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">Using
your environment including the 2008 Databases Samples Adventure Works, do the href="http://technet.microsoft.com/en-us/library/ms170208.aspx">Technet Analysis
Services Tutorial.
You can stop at the end of Step 3 (browsing the
Deployed Cube) that is a good initiation to SSAS 2008 and is enough to generate
a significant report and deploy it in SharePoint.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
2 Analysis Services Tutorial checking

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">
At
the end of the step 3 of the tutorial you should have:

The following view
in the Analysis Services database.

The following items
in the project folder.

The following in
BIDS.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
3 Creating Reporting Services Project

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">
Open
BIDS, in File menu point "New “, click “Project"
Choose a Report Server
Project Wizard and name this AnalysisServicesBasedReport

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
4 Selecting the data Source

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">
On
"Select the Data Source" windows, choose Microsoft SQL Server Analysis Services
as the data source type.

Then click the "EDIT" button to
display the "Connection Properties" modal dialog.
In the "server name" text
box type the name of your database server where your Analysis Services 2008
database is located.
When the complete name is defined, you will see in the
"Select or enter a database name" drop down list the name of the Analysis
Services 2008 database corresponding to the Analysis Services Tutorial:
"Analysis services Tutorial".
Select this database and optionally test the
connection.

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">Click
OK to close the modal dialog and you will see appear your connection string in
the corresponding field of the main dialog.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
5 Designing the Analysis Services Query

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">
Click
"Next" to display the "Design the Query" dialog.

Click the
"Query Builder..." button to open the Query Builder.
Notice that you are now
within a SQL Analysis Services 2008 environment since you can see a cube with
its measures, KPIs and dimensions.

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">On
the Metadata pane expand Customer dimension and drag and drop the Full Name
field in the right pane.
Do the same with the "Product name" field of the
"Product" dimension.

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">Then
end by drag and dropping the "Sales Amount" item of the "Internet Sales" folder
located beyond the Measures node of the "Analysis Services Tutorial" cube.

You will notice that the values will be automatically populated.

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">We
are now going to filter the Data using the Dimensions.
In Customer Dimension
expand "Location" folder and drag and drop State-province to the Top Right
pane
Then expand the "Filter Expression" drop down list, and check the
checkbox for "Oregon".
You will notice that a first filtering operation is
automatically performed.

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">Then
in the "Order Date" dimension, expand "Order Date English Month Name", then
expand English Month Name and drag and drop "February 2002" just bellow
the "State-province" Filter row. As before, the filtering operation runs
automatically and you will see only 6 rows left.

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">Click
OK to close the "Design the Query" dialog.
You are taken back to the "Design
the Query" dialog and can see the Query string generated by the Query
Builder.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
6 Passing through next 3 Wizard dialogs

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
7 Defining Deployment Locations

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">
On
the "Choose the Deployment Location" dialog, as we are planning to deploy the
report based on the SQL Server 2008 Analysis services database, in a SharePoint
2007 document library, we are in the case of an SQL Server 2008 Report Server
running in SharePoint integrated mode and we have to type the following in the
two input boxes:

Report server:
The SharePoint Site. Notice that if,
as in my screenshot your site is the root web site of a root site collection the
URL is the same than the Web Application Url

--> href="http://yourMachineName:portNumber">http://yourMachineName:portNumber

Deployment folder:
We have to type the URL of the document library
where we plan to deploy the report. For example if your document library root
folder name is Adventure Works Reports

--> href="http://yourMachineName:portNumber/adventure">http://yourMachineName:portNumber/adventure
works Reports

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
8 Closing the Wizard

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">and
after having clicked "Finish" the usual BIDS environment is opening and we can
see the report in design mode.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
9 Report changes and preview in BIDS

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
10 Defining the report Datasource location in BIDS

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">We
have now to deploy the report but before deploying it we have to define the
datasource location in SharePoint.
So, open the project properties dialog by
right-clicking the project name and clicking properties.

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">In
the opening "AnalysisServicesBasedReport Property Pages" dialog, in the
TargetDataSourceFolder, copy and paste the URL present in the TargetReportFolder
field.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
11 Deploying the report in SharePoint 2007 with BIDS

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">
Then
in the BIDS Solution Explorer, right click the project name again and click
"Deploy".
In the BIDS Output Pane, you can check the deployment status.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 13px; FONT-WEIGHT: bold; PADDING-BOTTOM: 10px; MARGIN: 1px 0px; COLOR: #e9ab17; PADDING-TOP: 10px">Step
12 Checking the report deployment in SharePoint 2007

style="PADDING-RIGHT: 5px; PADDING-LEFT: 18px; FONT-WEIGHT: normal; FONT-SIZE: 10pt; PADDING-BOTTOM: 18px; MARGIN: 1px 0px; COLOR: gray; PADDING-TOP: 5px; FONT-FAMILY: arial; TEXT-ALIGN: justify">And
finally click the .rdl in order to display the SQL Server Reporting Services 2008 Report based on an SQL Server
Analysis Services 2008 Datasource in the RSViewerPage.aspx page within a Sharepoint 2007 site.

Sharepoint Advanced Tips

Saturday, June 13, 2009

Create a Report from an Analysis Services 2008 Database and deploy it in SharePoint 2007

No comments:

Post a Comment