Preparation
Preparation: Create a Clone Report within the Reporting Services Development Environment
For purposes of our
practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples
that are available with (and installable separately from) the MSSQL Server
2005 integrated business intelligence suite. Making preparatory
modifications, and then making the enhancements to the report to add the
functionality that forms the subject of our lesson, can be done easily within
the Business Intelligence Development Studio environment.
Working with a copy of the report will allow us the luxury of freely exploring
our options, and will leave us with a working example of the specific approach
we took, to which we can refer in our individual business environments.
Open
the Sample Report Server Project
For purposes of our
practice session, we will open the AdventureWorks Sample Reports
project, which contains the sample reports that ship with the Reporting
Services component of the MSSQL Server 2005 suite. We will complete
our practice session within the sample project so as to save the time required
to set up a development environment from scratch within the Business
Intelligence Development Studio.
To open the AdventureWorks
Sample Reports project, please see the following procedure in the References
section of my articles index:
Ascertain Connectivity of the Shared Data Source
Let’s ensure we have a
working data source. Many of us will be running “side-by-side”
installations of MSSQL Server 2000 and MSSQL Server 2005. This
means that our installation of the latter will need to be referenced as a server
/ instance combination, versus a server name alone. (The default for
the Adventure Works DW project sample’s connection is localhost,
which will not work correctly in such a side-by-side installation, as MSSQL
Server 2000 will have assumed the identity of the local PC by default.)
If you do not know how to ascertain or
modify connectivity of the Analysis Services data source, please perform
the steps of the following procedure in the References section of my
articles index:
Create a Copy of the Sales Reason Comparisons Report
We will begin with a copy
of the Reporting Services 2005 Sales Reason Comparisons OLAP report,
which we will use for our practice exercises. Creating a “clone” of the report means we can make
changes to select contents (perhaps as a part of later exploration with our
independent solution), while retaining the original sample in a pristine state
for other purposes, such as using it to accompany relevant sections of the Books
Online, and other documentation. We can, therefore, use the original as a
part of learning more about Reporting Services (particularly an OLAP
report using an Analysis Services data source), and other
components of the Microsoft integrated business intelligence solution, in
general.
If you do not know how
to create a copy of an existing report, please perform the steps of the
following procedure in the References section of my articles index:
We now
have a clone OLAP report file within our Reporting Services 2005 Project,
with which we can proceed, in the next section, to make modifications for our
subsequent practice session.
Preparation: Add the Analysis Services Database to the Project
We will
continue our preparation by adding the Analysis Services database – with
which we ascertained connectivity in the “Ascertain Connectivity
of the Shared Data Source” section above - within
our newly created project in the Business Intelligence Development Studio.
I typically like to set up a lab environment for each of my client or research
projects where I have both the respective UDM and reports involved with
the engagement within an integrated solution in Visual Studio. This
ensures ease in testing cube modifications through to the report layer
from a single, central location, as well as providing the advantage of
effective source control, among numerous other conveniences. For example, in
this particular case, I will have both a copy of the sample Adventure Works
DW and the AdventureWorks Sample Reports projects added into a
single solution within the Business Intelligence Development Studio,
where I can access all member objects from one point, the Solution Explorer.
Continuing
within our newly created project in the Business Intelligence Development
Studio, take the following steps:
1.
Select File
-> Open from the main menu.
2.
Click Analysis
Services Database ... from the cascading menu, as shown in Illustration 1.
Illustration 1:
Selecting an Analysis Services Database into the Project ...
The Connect
to Database dialog appears.
3.
Ensure that
the radio button to the immediate left of Connect to existing database
(atop the dialog) is selected.
4.
Type the
appropriate name within the Server input box.
5.
Select the
appropriate name within the Database selector (the Analysis Services
database with which we ascertained connectivity of our report clone above),
just underneath the Server input box.
6.
Click the
radio button to the immediate left of Add To Solution (in the bottom
section of the dialog), to select this option.
The Connect to Database dialog appears similar to that
depicted in Illustration 2.
Illustration 2: The
Connect to Database Dialog, with Our Input
7.
Click OK to
accept our input, and to dismiss the dialog.
The Reading database from the server... message box appears briefly, as
shown in Illustration 3.
Illustration
3: Reading the Database from the Server ...
The Adventure
Works DW Analysis Services project opens, and we see the various
associated objects appear within Solution Explorer, as depicted in Illustration
4 (with Dimensions folder collapsed).
Illustration
4: The Adventure Works DW Analysis Services Project Joins the Solution ...
We can
now access our sample report and its underlying Analysis Services
database, and thus test cube enhancements through to the report layer, from a
single, central development environment.
» 