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.
While
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.
In the
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
Works organization.
To
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.
» 