Architect Bill Pearson conducts
hands-on practice in establishing interactive sort support in Reporting
About the Series ...
article is a member of the series MSSQL Server Reporting Services.
The series is designed to introduce MSSQL Server Reporting Services ("Reporting
Services"), with the objective of presenting an overview of its features, together
with tips and techniques for real-world use. For more information on the
series, please see my initial Database
Journal article, A New
Paradigm for Enterprise Reporting.
have stated since the charter article of the series, published about the time Reporting
Services was first publicly released, my conviction is that Reporting
Services will commoditize business intelligence, particularly in its role
as a presentation component within an integrated Microsoft BI solution.
Having been impressed from my first exposure to this exciting application, when
it was in early beta, my certainty in its destiny grows stronger by the day, as
I convert formerly dominant enterprise business intelligence systems, such as Cognos,
Business Objects, MicroStrategy, Crystal, and others, to the Reporting Services
architecture. I receive constant requests to conduct strategy sessions about
these conversions with large organizations in a diverse range of industries
the interest grows daily as awareness of the solution becomes pervasive. Indeed,
the five- to six-plus figures that many can shave from their annual IT budgets
represent a compelling sweetener to examining this incredible toolset.
Note: To follow along with the steps we
undertake, the following components, samples and tools are recommended, and
should be installed / accessible, according to the respective documentation
that accompanies MSSQL Server 2005:
Server 2005 Reporting Services
Server 2005 Database Services
AdventureWorks sample databases
Server 2005 Analysis Services
We will be using one of the AdventureWorks sample
reports in the practice section, to save time and focus for the subject matter
of the article. The AdventureWorks sample reports are a set of
prefabricated report definition files that use the AdventureWorks
databases (both relational and Analysis Services) as data sources. The
sample reports are highly useful to many new report authors and other
practitioners, for whom they serve as a tool to assist in learning the
capabilities of Reporting Services, as well as templates for designing
new reports. For this reason, we typically make a copy of any report(s) we
modify within our lessons.
The samples are not
automatically installed. Before we
can install the Reporting Services samples, we must have already copied
the sample installation program to the PC with which we are working, in
accordance with the instructions found in the SQL Server 2005 Books
Online and elsewhere. We then run the sample installation program
to extract and copy the reports (and other) samples to the computer. The sample
installation program also installs the AdventureWorks databases.
The samples come packaged within a Report Server
project file, which we will open and use in many lessons, rather than creating
a new project file. Please make sure that the samples and the project file are
installed before beginning the practice section of this article, to provide an
environment in which to complete the exercises effectively.
Note: Current Service Pack updates are assumed for the operating system, along
with the applications and components listed above and the related Books
Online and Samples. Images are from a Windows 2003
Server environment, but the steps performed in the articles, together with
the views that result, will be quite similar within any environment that
supports MSSQL Server 2005 and its component applications.
About the BlackBelt Articles ...
have stated in earlier BlackBelt articles, one of the greatest
challenges in writing tutorial / procedural articles is creating each article
to be a freestanding document that is complete unto itself. This is important,
because it means that readers can complete the lesson without reference to
previous articles or access to objects that may have been created elsewhere.
When our objective is the coverage of a specific technique surrounding one or
more components of a report, a given administrative function surrounding all
reports, and other scenarios where the focus of the session is not the creation
of reports, per se, challenges can arise because a report or reports often
has to be in place before we can begin to cover the material with which the
article concerns itself.
articles represent an attempt to minimize the setup required in simply getting
to a point within an article where we can actually perform hands-on practice
with the component(s) under consideration. We will attempt to use existing
report samples or other "prefabricated" objects that either come
along as part of the installation of the applications involved, or that are
readily accessible to virtually any organization that has installed the
application. While we will often have to make modifications to the sample
involved (we will actually create a copy, to allow the original sample to
remain intact), to refine it to provide the backdrop we need to proceed with
the object or procedure upon which we wish to concentrate, we will still save a
great deal of time and distraction in getting to our objective. In some cases,
we will still have to start from scratch with preparation, but my intention
with the BlackBelt articles will be to avoid this, if at all
more information about the BlackBelt
articles, see the
section entitled "About the BlackBelt Articles" in BlackBelt
Components: Manage Nulls in OLAP Reports.
A common request within
the implementation of enterprise reporting systems is to endow the reports of
our users with interactive features. While we have spent a great deal of time
upon parameterization in general (even discussing parameterized sorting in Black
Belt Components: Ad Hoc Sorting with Parameters), one feature of Reporting
Services 2005 that I expect to be quite popular is the new capability to
provide interactive sorting via column heading properties. To add to
the convenience that we find within the reporting component of what has become
known as the "BI Release" of MSSQL Server, we enjoy the option of
specifying sorting for multiple columns within the matrix, table or list within
which we are working. We can even extend the interactive sorting
features to grouped or nested data within our reports.
shall discover in this article, what we have had to accomplish with conditional
formatting in MSSQL Server 2000, as well as within many other enterprise
reporting applications over the years, such as Cognos Impromptu and PowerPlay,
Crystal Reports, and a host of others, we can now address with the quick
and easy setting of properties in our reports as part of their design. In this session, we will:
Create a clone
of an existing sample relational report, with which to perform our practice
changes to the clone report, based upon a sample SQL Server database, to meet
the business requirements of a hypothetical group of information consumers for interactive
report to ascertain the effectiveness of our solution;
results obtained with the development techniques that we exploit.
Interactive Sorting within Reporting Services
Objective and Business Scenario
As I mentioned in Black
Belt Components: Ad Hoc Sorting with Parameters, conditional formatting in
general is a popular topic in the forums and newsgroups of most enterprise
reporting applications, because it allows for more impact in our reports. My
first exposure to the concepts of conditional formatting with Cognos, and my
continued application of those concepts within Cognos, Crystal, Business
Objects, MicroStrategy, and a host of other, more specialized
applications, has given me a great appreciation for the opportunities that
exist in the business environment for effective conditional formatting. Ad hoc
formatting, including sorts, is really only a logical extension of conditional
formatting the distinction is in the condition itself, which is "whether
the user of the report wants it nor not." In most cases, it is easy to see
the value that ad hoc formatting can add in making our presentations more
focused and consumer-friendly.
we can conditionally format via expressions within Reporting Services to
achieve virtually any behavior we wish within our reports, many ad hoc
formatting capabilities are built into the more mature enterprise reporting
packages, and hence are automatically in place within any report we design. Reporting
Services 2005 witnesses the addition of several of these "standard"
capabilities, one of which is interactive sorting. As we shall see, we
can now provide interactive sorting to the organization's information
consumers by simply setting properties within the design of our reports. Then,
once we publish them, the sorting of the desired sections of our reports can be
managed by the consumers with ease.
following sections, we will perform the steps required to enable ad hoc
sorting within a relational report. To provide a report upon which we can
practice the steps of our hands-on exercise, we will begin with the Sales Order
Detail sample report, based upon the AdventureWorks database that is
available with the installation of the MSSQL Server 2005 samples. The Sales
Order Detail report is intended to present transactional sales information
to its intended audience, which for the purposes of our article, we will say
are information consumers within the Controller's Office of our client, the Adventure
illustrate the business need, let's say that the information consumers have expressed
the need for modifications of the existing Sales Order Detail report,
which we have prepared for them earlier in the year. The consumers have
expressed overall satisfaction with the report, but want to enhance it a bit to
add a touch of control over the display of the data returned. They wish to be
able to perform ad hoc sorts to the report by simply clicking a button
in the Item Number heading, with the resulting effect to be an ascending
or descending sort (to be determined at run time) of the line items
of the report, based upon the Item Total that appears for each line
item. The consumers feel that this capability will allow them to rapidly meet
differing conditions and analysis needs.
As part of our typical
business requirements gathering process, we listen attentively to the details,
formulating, in the background, an idea of the steps we need to take in
modifying a copy of the report to produce the desired results. Once we grasp
the stated need, and confirm our understanding with the intended audience, we
begin the process of modifying the Sales Order Detail report to satisfy
the information consumers.
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.
Create a Copy of the Sales Order Detail Report
have noted, we will begin with a copy of the Reporting Services 2005
Sales Order Detail report, which we will use for our practice exercise
in meeting the business requirements of the Adventure Works information
the Reports folder underneath the Shared Data Sources folder, in
the Solution Explorer.
--> Existing Item ... from the cascading context menus
that appear, as shown in Illustration 10.
Illustration 10: Adding
the Report to the Project ...
Existing Item AdventureWorks Sample Reports dialog appears.
the actual location of the sample
reports (we provided the default path earlier), if the dialog has not defaulted
example of the Add Existing Item AdventureWorks Sample Reports dialog dialog,
having been pointed to the sample Reports folder (which contains the Sales
Order Detail report we seek), appears as partially shown in Illustration 11.
the Sales Order Detail report inside the dialog.
from the context menu that appears, as depicted in Illustration 12.
somewhere in the white space inside the Add Existing Item AdventureWorks Sample Reports dialog.
from the context menu that appears, as shown in Illustration 13.
Illustration 13: Select
Paste within the New Folder ...
of the Sales Order Detail report appears within the dialog.
the new file.
from the context menu that appears.
following name in place of the highlighted existing name:
NOTE: Be sure to include the .rdl extension in the file name.
renamed copy of the Sales Order Detail sample report appears as depicted
in Illustration 14.
The New Report File, RS024_Interactive Sort.rdl
white space to the right of the file name, to accept the new name we have
new file by clicking it.
on the dialog box to add the new report to report project AdventureWorks
in the Reports folder, within the AdventureWorks Sample Reports project tree
in the Solution Explorer, as shown in Illustration 15.
15: The New Report Appears in Solution Explorer Report Folder
From the main menu in the
design environment, select File ---> Save All, as depicted in Illustration
16: Select File --> Save All to Save Our Work So Far ...
We now have a clone report file within our Reporting Services
2005 Project, with which we can proceed in the next section to add interactive
Procedure: Add Support for Interactive Sorting in a Relational
could, of course, be added to our report via the method we undertook in Black Belt Components: Ad Hoc Sorting with Parameters, However, as we have already
noted, our specific focus in
this section will be the addition of support for interactive sorting through
leveraging new properties settings.
Let's open our new report in Layout view and make the
necessary settings to support the interactive sorting capabilities
requested by the information consumers.
Sort.rdl in the Solution Explorer.
from the context menu that appears, as shown in Illustration 17.
Illustration 17: Opening
the New Report ...
Sort.rdl opens in
Layout view, and appears as depicted in Illustration 18.
preview the report, so as to get a feel for its general operation prior to
performing our enhancements.
Click the Preview
tab to execute RS024_Interactive Sort.
begins (the report initially executes with the default parameter setting).
report executes, and appears as shown in Illustration 19.
The Report Appears Default Parameter Selection ...
now positioned to enable the report to support interactive sorting. To
do so, we will return to Layout view, and then access the properties
settings for the Item Number textbox.
Click the Layout
the textbox containing the label Item Number (the heading for the Item
Number column in the report).
from the context menu that appears, as depicted in Illustration 20.
20: Accessing the Properties for the Item Number Column Heading ...
Properties dialog opens.
Select the Interactive
Sort tab of the dialog.
check box atop the tab labeled Add an interactive sort action to this
checkmark appears in the check box, indicating that interactive sort is
In the Sort
expression box that appears immediately underneath the check box, select
the following from the options that appear within the selector:
In the section
just below the Sort expression box, labeled Data region or grouping
to sort, click the radio button labeled Choose data region or grouping.
selector just below the radio button, select the following item:
radio button to the left of Detail scope, in the section labeled Evaluate
sort expression in this scope, at the default, selected setting.
leave the Detail scope selection in place as we intend only to sort the
repeating rows of the data region we have selected. Had the need arisen, we might
have specified another data region or group upon which to enact our sorts.
Sort tab of the Textbox Properties dialog appears as shown in Illustration
Illustration 21: The Interactive Sort tab...
to save our settings and to dismiss the Textbox Properties dialog.
to the Layout tab, where we are now ready to verify the effectiveness of
Verification: Preview the Report and Inspect Effectiveness of Interactive Sorting
Let's preview the report to inspect the results of our handiwork.
1. Click the Preview tab.
RS024_Interactive Sort executes, and returns the data for the default Sales Order Number indicated atop the report. We note this time, however, the appearance of upward / downward pointing arrows, alongside the Item Number column heading, as shown in Illustration 22.
Illustration 22: Interactive Sorting is Enabled ...
Recall that we have established sorting based upon the Line Totals for each of the Item Numbers in the report. Clicking the "up" arrow should, therefore, sort the Item Numbers listed, by Item Total, in ascending fashion (they are not sorted at present).
2. Click the upper of the two arrows.
The report reorders itself to sort as expected. We see the downward pointing arrow disappear, leaving the upper arrow in place to indicate that Item Number has been sorted. We also note that the Line Totals are, indeed, presented in ascending order, as depicted in Illustration 23.
Illustration 23: Ascending Sort Accomplished ...
We can just as easily sort in descending fashion, by clicking the upward pointing arrow that remains. The re-sort will occur, leaving the Item Numbers sorted by Item Total, this time in descending order.
We thus see that the interactive sorting capability accomplishes the intended ends, and allows us to meet the need as expressed by the information consumers. The flexibility of interactive sort support is extended in its allowance for selection of data region and scope for our sorts, as we have seen. Through this means we can meet varying needs of the organizations we support, limiting the desired sort actions to the data region containing the textbox upon which we enact the capability, or allowing us to extend the scope of our sorts to another set of data regions that we can choose as easily as we have done with the one in our practice example.
3. Select Save --> Save All to save our work to this point.
4. Select File --> Exit to leave Reporting Services, when ready.
In this article, we extended previous examinations of sorting capabilities to focus upon a popular feature within Reporting Services that supports a commonly encountered business need: the desire of information consumers to be able to perform ad hoc sorting for a given item or items within the report, based upon their own values or upon a corresponding value that appears in, say, another column of the report. As a part of preparing the backdrop for a practice exercise surrounding the setup of interactive sorting support, we created a copy of an existing sample report to leave the original intact for other uses.
The steps we took within the context of the clone report gave us a feel for what is involved in bringing the capability to perform interactive sorting to existing reports in our own environments. We emphasized that, with the introduction of interactive sorting, Reporting Services closes yet another of the few remaining gaps between itself and other, once dominant enterprise reporting solutions.
To set the stage for our discussion and practice session, we presented a business requirement based upon the needs of a hypothetical client. We prepared for our practice session by opening the Report Server project that contains the sample reports, and which, along with a set of other samples, ships with MSSQL Server 2005. From within the Business Intelligence Development Studio, where we opened the project, we then created a "clone" report, within which we performed the steps of our practice exercise.
Once we had aligned the underlying shared data source to our local sample database, we made the appropriate setting to support ad hoc sorting for a column within our report, based upon a value in another column. As we performed the necessary settings to enable interactive sorting within the report, we addressed the importance of scope within the properties. Finally, we verified the operation of our enhancements in a test of report operation.
» See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.