first objective is to create a copy of the Sales Order Detail sample report, into which we can implement the new interactive sorting
enhancements from the perspective of the powerful SQL Server Business Intelligence
Development Studio. We
will perform this, and the other steps of our practice session, from inside the
BI Development Studio, which makes its home within Visual Studio .NET
NOTE: For more exposure to the MSSQL Server Business
Intelligence Development Studio itself, and the myriad design, development
and other evolutions we can perform within this powerful interface, see articles
in this and my other 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 Clone Report within the Reporting Services Development Environment
For purposes of our
practice session, we will create a copy of the Sales Order Detail report, one of several samples
that are available with (and installable separately from) the Microsoft SQL
Server 2005 integrated business intelligence solution. Creating a "clone" of the report means we can make
changes to our report while retaining the original sample in a pristine state
perhaps for other purposes, such as using it to accompany relevant sections of
the Books Online, and other documentation, in learning more about Reporting
Services in general.
modifications, and then making the enhancements to the report to add the
functionality to support the subject of our lesson, can be done easily within
the Studio environment. Working with a copy of the report will allow us
the luxury of freely exploring our options, and leave us a working example of
the specific approach we took, to which we can refer in our individual business
the Sample Report Server Project and Ascertain Connectivity of the Shared Data
begin, we will launch the SQL Server Business Intelligence Development Studio.
and click, the SQL
Server Business Intelligence Development Studio, as appropriate.
equivalent on my PC appears as depicted in Illustration 1.
Launching SQL Server Business Intelligence Development Studio
briefly see a splash page that lists the components installed on the PC, and
then Visual Studio .NET 2005 opens at the Start page.
Close the Start
page, if desired.
--> Open from the main menu.
/ Solution ... from the cascading menu, as shown in Illustration 2.
Selecting a New Project
Project dialog appears.
Browse to the AdventureWorks
The reports are installed, by default (and, therefore,
subject to be installed in a different location on our individual machines), in
the following location
C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports
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 ...
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 ...
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, which means that our installation of the latter
will need to be referenced as a server / instance combination, versus a server
within the Shared Data Sources folder seen in Solution Explorer.
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
Properties dialog opens, and appears with default settings shown in Illustration
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 us, 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\M1MSSQL2K5,
as depicted in Illustration 7.)
Illustration 7: The Connection
Properties Dialog with Corrected Settings ...
12. Ensure that authentication
settings are correct for the local environment.
13. Click the Test Connection
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
15. Click OK to accept changes,
as appropriate, and to dismiss the Connection Properties dialog.
Data Source dialog appears, with our modified settings, similar to
that depicted in Illustration 9.
Illustration 9: The Shared
Data Source Dialog with Modified Settings ...
16. Click OK to close the Shared Data Source dialog, and to return to the development
We are now ready to "clone" a sample report and
proceed with the practice exercise.