MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
April 19, 2005
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.
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:
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).