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.