MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters

About the Series …

This
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 many tips and techniques for real-world use. For more
information on the series, as well as the hardware / software requirements
to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

Basic
assumptions underlying the series are that you have correctly installed
Reporting Services, including Service Pack 1, along with the applications
upon which it relies, and that you have access and the other rights /
privileges required to complete the steps we undertake in my articles. For
details on the specifics of the adjustments necessary to quickly allow full
freedom to complete the exercises in this and subsequent articles, as well as
important assumptions regarding rights and privileges in general, please see
earlier articles in the series, as well as the Reporting Services Books
Online.

About the BlackBelt Articles …

As we
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 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.

The BlackBelt
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
possible.

For
more information about the BlackBelt articles, see the section
entitled "About the BlackBelt Articles" in BlackBelt
Components: Manage Nulls in OLAP Reports.

Overview

As I
stated in my article Black Belt
Components: Ad Hoc Conditional Formatting for OLAP Reports, I
have found many uses for conditional formatting in reports over the
years I have spent implementing enterprise Business Intelligence applications.
One component of formatting is sorting, and many clients have expressed
an interest in being able to dictate sorts in their reports at runtime. That
is, they have asked, once they became aware of parameterization in the
application involved, that they could request sorting upon a given column in
the report, as well as to dictate that sorts be enforced in ascending or
descending order.

As we
noted in the same article, in Reporting Services, like any of those other
enterprise reporting packages on the market, we create what is conceptually a condition,
or conditions, to which we assign rules of behavior, consisting most often of a
presentation style, but certainly not limited to that. When a value, or other
report object, meets the criteria of the condition(s) we define, Reporting
Services applies the formatting or other behavior to the object.

In
this article, we will introduce conditional formatting, once again, in a basic
application – this time a sort in each of the two "directions," and practice
the creation of parameterized conditional sorting that will allow users to make
runtime decisions surrounding the sorting of their reports. While we will
deal in this article with a relational report, ad hoc conditional formatting
can be accomplished in similar ways in reports based upon OLAP sources. In this session, we will:

• Make
  structural changes to a copy of a sample report, based upon a sample database,
  to meet the business requirements of a hypothetical group of information
  consumers;

• Create parameters,
  with which we will associate conditional sorting, to provide information
  consumers the ad hoc capability to dictate sorting criteria;

• Marry the
  parameters we create with conditional logic in the sorting properties of the
  report to support parameterized sorting by the information consumers;

• Preview the
  report to ascertain the effectiveness of our solution;

• Discuss the
  results obtained with the development techniques that we exploit.

Ad Hoc Conditional Sorting with Parameters

Objective and Business Scenario

Because it allows for
more impact in our reports, conditional formatting in general is a
popular topic in the forums and newsgroups of most enterprise reporting
applications. 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. Whether
the reports are to be printed, displayed on screen, or any of the other options
for production / deployment, it is easy to see the value that conditional
formatting can add in making our presentations more focused and
consumer-friendly.

As I
have mentioned in other articles of the series, I have used conditional
formatting / other properties to perform a wide range of activities. One of
those uses has been to enable conditional sorting of fields that appear in a
given report, then subjecting the condition to parameterization. In the following sections, we will
perform the steps required to add conditional sorting to a relational report.
To provide a report upon which we can practice our conditional formatting exercises,
we will begin with the Territory Sales Drilldown sample report, based
upon the AdventureWorks2000 database that is available with the
installation of Reporting Services.

For
purposes of our practice procedure, we will assume that information consumers within
the Controller’s Office of the AdventureWorks organization have expressed the need
for modifications of the existing Territory Sales Drilldown report,
which drills down to the Salesperson level, as partially shown in Illustration
1.

The consumers have
expressed overall satisfaction with the report, but they 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 select sort criteria for
the details presented for each salesperson, to make their analytical functions
easier. First, they wish to be able to sort by Sales Order totals,
controlling whether ascending or descending, to allow them to see largest – to
– smallest, and vice versa, sales for various reviews that they perform with
this report. They also want to be able to use the same report for another
recurring activity that is easier to accomplish if they can sort by Sales
Order Number. They wish to be able to make this a multi-purpose report
whose presentation is dictated on the fly by the ad hoc selection of
parameters. Prompts for this information, the consumers conclude, would
make the ad hoc input of multiple criteria quick and easy, allowing them
to rapidly meet differing conditions and analysis needs. The objective is
ultimately to be able to accomplish more with fewer reports.

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 Territory Sales Drilldown report to satisfy the information
consumers.

Considerations and Comments

Before we can work
with a clone of the Territory Sales Drilldown sample report, we need to
create a Reporting Services project in the Visual Studio.Net 2003 Report
Designer environment. Making the enhancements to the report to add the
requested functionality can be done easily within the Reporting Services Report
Designer environment, and 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. This approach also preserves 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 design in general. If you already have a
project within which you like to work with training or development objects, you
can simply skip the Create a Reporting Services Project section.

If the sample AdverntureWorks2000
sample database was not created as part of the initial Reporting
Services installation, or was removed prior to your beginning this article,
please see the Reporting Services Books Online or other
documentation for the procedure to put the database in place, together with the
sample reports. As of this writing, a copy of the samples can be obtained from
the installation CD or via download from the appropriate Microsoft site(s).