Interactive Sorting Within Reporting Services
December 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 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.
As I 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:
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 ...
As I 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.
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.
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.
As we 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: