Practice
Our
first objective is to create three basic reports with which to conduct our
working conditional drillthrough sample. Keep in mind that the focus of
our efforts is creating the relationships between the reports – the linkages
that make them work – and not the reports themselves. Because of time
limitations, we will be working with very simple reports – in reality, the
business environment will typically require significantly more sophistication.
The process of setting up conditional drillthrough is essentially the
same in the real world, with perhaps a more complex set of underlying
expressions involved, and a greater number of parameters in place.
We
will perform our practice session from inside the MSSQL Server Business Intelligence Development
Studio. For more exposure to the Business Intelligence
Development Studio itself, and the myriad design, development and other
evolutions we can perform within this powerful interface, see other articles in
this series, as well as within my Database Journal series Introduction
to MSSQL Server Analysis Services.
In this article, we will be commenting only on the features relevant to our
immediate practice exercise, to allow us to get to the focus of the article
more efficiently.
Preparation:
Create a Basic Set of Reports within the Reporting Services Development
Environment
We will create a
single "launch" report, and one "target" report, from
scratch. In addition, we will "borrow" the design of a sample report
to hasten the creation of a second "target" report. We will begin by
opening the sample Report Server Project that ships with Microsoft
Reporting Services 2005, to save more preparation time (you can create a
new project if appropriate to your local environment).
Open
the Sample Report Server Project and Ascertain Connectivity of the Shared Data
Source
To
begin, we’ll launch the SQL
Server Business Intelligence Development Studio.
1.
Click Start.
2.
Navigate to,
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
The
equivalent on my PC appears as depicted in Illustration 1.
Illustration 1:
Launching SQL Server Business Intelligence Development Studio
We
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio opens at the Start page.
3.
Close the Start
page, if desired.
4.
Select File
-> Open from the main menu.
5.
Click Project
/ Solution ... from the cascading menu, as shown in Illustration 2.
Illustration 2:
Selecting a Project ...
The Open
Project dialog appears.
6.
Browse to the AdventureWorks
sample reports.
The reports are installed, by default (and, therefore,
subject to reside in a different place on our individual machines), in the following
location
C:\Program
Files\Microsoft SQL Server\90\Samples\Reporting Services\Report
Samples\AdventureWorks Sample Reports
7.
Select the AdventureWorks
Sample Reports.sln file within the sample reports folder, as depicted
(circled) in Illustration 3.
Illustration 3: The Open
Project Dialog, with Our Selection Circled ...
The AdventureWorks
Sample Reports solution opens, and we see the various objects within appear
in Solution Explorer, as shown in Illustration 4.
Illustration 4: The
Solution Opens within BI Development Studio ...
Let’s
first ensure we have a working shared 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.
9.
Double-click AdventureWorksAS.rds,
within the Shared Data Sources folder seen in Solution Explorer.
The Shared
Data Source dialog opens, and appears with default settings as
depicted in Illustration 5.
Illustration 5: The
Shared Data Source Dialog with Default Settings ...
10. Click the Edit button on
the Shared Data
Source dialog.
The Connection
Properties dialog opens, and appears with default settings shown in Illustration
6.
Illustration 6: The
Connection Properties Dialog with Default Settings ...
We note that the default Server name is "local."
While this might prove an adequate setting for a PC with only MSSQL Server
2005 installed (default instance), in the case of many of our
installations, the requirement here is for the server / instance
combination that correctly identifies the correct MSSQL Server 2005
instance. (Clicking the Test Connection button at this point will
provide confirmation whether we need to make this change).
11. If appropriate, type the correct server
/ instance name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\MSSQL2K5,
as depicted in Illustration 7.)
Illustration 7: Example
Connection Properties Dialog with Corrected Settings ...
12. Ensure that authentication
settings are correct for the local environment.
13. Click the Test Connection
button.
A
message box appears, indicating that the Test connection succeeded,
assuming that our changes (or lack of same, as appropriate) are appropriate.
The message box appears as shown in Illustration 8.
Illustration 8: Testing
Positive for Connectivity ...
14. Click OK to dismiss the
message box.
15. Click OK to accept changes,
as appropriate, and to dismiss the Connection Properties dialog.
The Shared
Data Source dialog appears, with our modified settings, similar to
that depicted in Illustration 9.
Illustration 9: Example
Shared Data Source Dialog with Modified Settings ...
16. Click OK to close the Shared Data Source dialog, and to return to the development
environment.
We are now ready to create the reports we have mentioned, and
to proceed with the practice exercise.
» 