Interactive Sorting Within Reporting Services

Monday Dec 19th 2005 by William Pearson
Share:

Architect Bill Pearson conducts hands-on practice in establishing interactive sort support in Reporting Services.

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:

Server Requirements

  • Microsoft SQL Server 2005 Reporting Services

  • Microsoft SQL Server 2005 Database Services

  • The AdventureWorks sample databases

  • Microsoft SQL Server 2005 Analysis Services

  • The AdventureWorks OLAP cube

Client Requirements

  • Microsoft Internet Explorer 6.0 with scripting enabled

  • Business Intelligence Development Studio (optional)

Sample Files

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.

Overview

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:

  • Create a clone of an existing sample relational report, with which to perform our practice exercise;
  • Make structural changes to the clone report, based upon a sample SQL Server database, to meet the business requirements of a hypothetical group of information consumers for interactive sorting;
  • Preview the report to ascertain the effectiveness of our solution;
  • Discuss the results obtained with the development techniques that we exploit.

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.

Practice

Our first objective is to create a copy of the Sales Order Detail sample report, into which we can implement the new interactive sorting enhancements from the perspective of the powerful SQL Server Business Intelligence Development Studio. We will perform this, and the other steps of our practice session, from inside the BI Development Studio, which makes its home within Visual Studio .NET 2005.

NOTE: For more exposure to the MSSQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see articles in this and my other Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to get to the focus of the article more efficiently.

Preparation: Create a Clone Report within the Reporting Services Development Environment

For purposes of our practice session, we will create a copy of the Sales Order Detail report, one of several samples that are available with (and installable separately from) the Microsoft SQL Server 2005 integrated business intelligence solution. Creating a "clone" of the report means we can make changes to our report while retaining 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 Services in general.

Making preparatory modifications, and then making the enhancements to the report to add the functionality to support the subject of our lesson, can be done easily within the Studio environment. 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, to which we can refer in our individual business environments.

Open the Sample Report Server Project and Ascertain Connectivity of the Shared Data Source

To begin, we will launch the SQL Server Business Intelligence Development Studio.

1.  Click Start.

2.  Navigate to, and click, the SQL Server Business Intelligence Development Studio, as appropriate.

The equivalent on my PC appears as depicted in Illustration 1.


Illustration 1: Launching SQL Server Business Intelligence Development Studio

We briefly see a splash page that lists the components installed on the PC, and then Visual Studio .NET 2005 opens at the Start page.

3.  Close the Start page, if desired.

4.  Select File --> Open from the main menu.

5.  Click Project / Solution ... from the cascading menu, as shown in Illustration 2.


Illustration 2: Selecting a New Project

The Open Project dialog appears.

6.  Browse to the AdventureWorks sample reports.

The reports are installed, by default (and, therefore, subject to be installed in a different location on our individual machines), in the following location

C:\Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports

7.  Select the AdventureWorks Sample Reports.sln file within the sample reports folder, as depicted (circled) in Illustration 3.


Illustration 3: The Open Project Dialog, with Our Selection Circled ...

8.  Click Open.

The AdventureWorks Sample Reports solution opens, and we see the various objects within appear in Solution Explorer, as shown in Illustration 4.


Illustration 4: The Solution Opens within BI Development Studio ...

Let's first ensure we have a working shared data source. Many of us will be running "side-by-side" installations of MSSQL Server 2000 and MSSQL Server 2005, which means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone.

9.  Double-click AdventureWorks.rds, within the Shared Data Sources folder seen in Solution Explorer.

The Shared Data Source dialog opens, and appears with default settings as depicted in Illustration 5.


Illustration 5: The Shared Data Source Dialog with Default Settings ...

10.  Click the Edit button on the Shared Data Source dialog.

The Connection Properties dialog opens, and appears with default settings shown in Illustration 6.


Illustration 6: The Connection Properties Dialog with Default Settings ...

We note that the default Server name is "local." While this might prove an adequate setting for a PC with only MSSQL Server 2005 installed (default instance), in the case of many of us, the requirement here is for the server / instance combination that correctly identifies the correct MSSQL Server 2005 instance. (Clicking the Test Connection button at this point will provide confirmation whether we need to make this change).

11.  If appropriate, type the correct server / instance name into the Server name box of the Connection Properties dialog. (Mine is MOTHER1\M1MSSQL2K5, as depicted in Illustration 7.)


Illustration 7: The Connection Properties Dialog with Corrected Settings ...

12.  Ensure that authentication settings are correct for the local environment.

13.  Click the Test Connection button.

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 8.


Illustration 8: Testing Positive for Connectivity ...

14.  Click OK to dismiss the message box.

15.  Click OK to accept changes, as appropriate, and to dismiss the Connection Properties dialog.

The Shared Data Source dialog appears, with our modified settings, similar to that depicted in Illustration 9.


Illustration 9: The Shared Data Source Dialog with Modified Settings ...

16.  Click OK to close the Shared Data Source dialog, and to return to the development environment.

We are now ready to "clone" a sample report and proceed with the practice exercise.

Create a Copy of the Sales Order Detail Report

As we have noted, we will begin with a copy of the Reporting Services 2005 Sales Order Detail report, which we will use for our practice exercise in meeting the business requirements of the Adventure Works information consumers.

1.  Right-click the Reports folder underneath the Shared Data Sources folder, in the Solution Explorer.

2.  Select Add --> Existing Item ... from the cascading context menus that appear, as shown in Illustration 10.


Illustration 10: Adding the Report to the Project ...

The Add Existing Item – AdventureWorks Sample Reports dialog appears.

3.  Navigate to the actual location of the sample reports (we provided the default path earlier), if the dialog has not defaulted thereto.

An example of the Add Existing Item – AdventureWorks Sample Reports dialog dialog, having been pointed to the sample Reports folder (which contains the Sales Order Detail report we seek), appears as partially shown in Illustration 11.

Click for larger image

Illustration 11: Navigating to the Sample Reports Folder ...

4.  Right-click the Sales Order Detail report inside the dialog.

5.  Select Copy from the context menu that appears, as depicted in Illustration 12.

Click for larger image

Illustration 12: Performing a Quick Copy of the Sales Order Detail Report

6.  Right-click somewhere in the white space inside the Add Existing Item – AdventureWorks Sample Reports dialog.

7.  Select Paste from the context menu that appears, as shown in Illustration 13.


Illustration 13: Select Paste within the New Folder ...

A copy of the Sales Order Detail report appears within the dialog.

8.  Right-click the new file.

9.  Select Rename from the context menu that appears.

10.  Type the following name in place of the highlighted existing name:

RS024_Interactive Sort.rdl

NOTE: Be sure to include the .rdl extension in the file name.

The renamed copy of the Sales Order Detail sample report appears as depicted in Illustration 14.


Illustration 14: The New Report File, RS024_Interactive Sort.rdl

11.  Click the white space to the right of the file name, to accept the new name we have assigned.

12.  Re-select the new file by clicking it.

13.  Click Add on the dialog box to add the new report to report project AdventureWorks Sample Reports.

RS024_Interactive Sort.rdl appears in the Reports folder, within the AdventureWorks Sample Reports project tree in the Solution Explorer, as shown in Illustration 15.


Illustration 15: The New Report Appears in Solution Explorer – Report Folder

14.  From the main menu in the design environment, select File ---> Save All, as depicted in Illustration 16.


Illustration 16: Select File --> Save All to Save Our Work So Far ...

We now have a clone report file within our Reporting Services 2005 Project, with which we can proceed in the next section to add interactive sort capabilities.

Procedure: Add Support for Interactive Sorting in a Relational Report

Conditional sorting could, of course, be added to our report via the method we undertook in Black Belt Components: Ad Hoc Sorting with Parameters, However, as we have already noted, our specific focus in this section will be the addition of support for interactive sorting through leveraging new properties settings.

Let's open our new report in Layout view and make the necessary settings to support the interactive sorting capabilities requested by the information consumers.

1.  Right-click RS024_Interactive Sort.rdl in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 17.


Illustration 17: Opening the New Report ...

RS024_Interactive Sort.rdl opens in Layout view, and appears as depicted in Illustration 18.

Click for larger image

Illustration 18: Our Report Opens in Layout View ...

Let's preview the report, so as to get a feel for its general operation prior to performing our enhancements.

3.  Click the Preview tab to execute RS024_Interactive Sort.

Execution begins (the report initially executes with the default parameter setting).

The report executes, and appears as shown in Illustration 19.


Illustration 19: The Report Appears Default Parameter Selection ...

We are now positioned to enable the report to support interactive sorting. To do so, we will return to Layout view, and then access the properties settings for the Item Number textbox.

4.  Click the Layout tab.

5.  Right click the textbox containing the label Item Number (the heading for the Item Number column in the report).

6.  Select Properties from the context menu that appears, as depicted in Illustration 20.


Illustration 20: Accessing the Properties for the Item Number Column Heading ...

The Textbox Properties dialog opens.

7.  Select the Interactive Sort tab of the dialog.

8.  Click the check box atop the tab labeled Add an interactive sort action to this textbox.

The checkmark appears in the check box, indicating that interactive sort is enabled.

9.  In the Sort expression box that appears immediately underneath the check box, select the following from the options that appear within the selector:

=Fields!LineTotal.Value

10.  In the section just below the Sort expression box, labeled Data region or grouping to sort, click the radio button labeled Choose data region or grouping.

11.  In the selector just below the radio button, select the following item:

SalesOrderDetail

12.  Leave the radio button to the left of Detail scope, in the section labeled Evaluate sort expression in this scope, at the default, selected setting.

We leave the Detail scope selection in place as we intend only to sort the repeating rows of the data region we have selected. Had the need arisen, we might have specified another data region or group upon which to enact our sorts.

The Interactive Sort tab of the Textbox Properties dialog appears as shown in Illustration 21.


Illustration 21: The Interactive Sort tab...

13.  Click OK to save our settings and to dismiss the Textbox Properties dialog.

We return to the Layout tab, where we are now ready to verify the effectiveness of our modifications.

Verification: Preview the Report and Inspect Effectiveness of Interactive Sorting

Let's preview the report to inspect the results of our handiwork.

1.  Click the Preview tab.

RS024_Interactive Sort executes, and returns the data for the default Sales Order Number indicated atop the report. We note this time, however, the appearance of upward / downward pointing arrows, alongside the Item Number column heading, as shown in Illustration 22.


Illustration 22: Interactive Sorting is Enabled ...

Recall that we have established sorting based upon the Line Totals for each of the Item Numbers in the report. Clicking the "up" arrow should, therefore, sort the Item Numbers listed, by Item Total, in ascending fashion (they are not sorted at present).

2.  Click the upper of the two arrows.

The report reorders itself to sort as expected. We see the downward pointing arrow disappear, leaving the upper arrow in place to indicate that Item Number has been sorted. We also note that the Line Totals are, indeed, presented in ascending order, as depicted in Illustration 23.


Illustration 23: Ascending Sort Accomplished ...

We can just as easily sort in descending fashion, by clicking the upward pointing arrow that remains. The re-sort will occur, leaving the Item Numbers sorted by Item Total, this time in descending order.

We thus see that the interactive sorting capability accomplishes the intended ends, and allows us to meet the need as expressed by the information consumers. The flexibility of interactive sort support is extended in its allowance for selection of data region and scope for our sorts, as we have seen. Through this means we can meet varying needs of the organizations we support, limiting the desired sort actions to the data region containing the textbox upon which we enact the capability, or allowing us to extend the scope of our sorts to another set of data regions that we can choose as easily as we have done with the one in our practice example.

3.  Select Save --> Save All to save our work to this point.

4.  Select File --> Exit to leave Reporting Services, when ready.

Conclusion ...

In this article, we extended previous examinations of sorting capabilities to focus upon a popular feature within Reporting Services that supports a commonly encountered business need: the desire of information consumers to be able to perform ad hoc sorting for a given item or items within the report, based upon their own values or upon a corresponding value that appears in, say, another column of the report. As a part of preparing the backdrop for a practice exercise surrounding the setup of interactive sorting support, we created a copy of an existing sample report to leave the original intact for other uses.

The steps we took within the context of the clone report gave us a feel for what is involved in bringing the capability to perform interactive sorting to existing reports in our own environments. We emphasized that, with the introduction of interactive sorting, Reporting Services closes yet another of the few remaining gaps between itself and other, once dominant enterprise reporting solutions.

To set the stage for our discussion and practice session, we presented a business requirement based upon the needs of a hypothetical client. We prepared for our practice session by opening the Report Server project that contains the sample reports, and which, along with a set of other samples, ships with MSSQL Server 2005. From within the Business Intelligence Development Studio, where we opened the project, we then created a "clone" report, within which we performed the steps of our practice exercise.

Once we had aligned the underlying shared data source to our local sample database, we made the appropriate setting to support ad hoc sorting for a column within our report, based upon a value in another column. As we performed the necessary settings to enable interactive sorting within the report, we addressed the importance of scope within the properties. Finally, we verified the operation of our enhancements in a test of report operation.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved