MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters

Tuesday Apr 19th 2005 by William Pearson
Share:

Parameterize sorts within a tabular report. MSAS Architect Bill Pearson provides hands-on practice in the parameterization of sorting at run time 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 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.

Overview

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:

  • Make structural changes to a copy of a sample report, based upon a sample database, to meet the business requirements of a hypothetical group of information consumers;
  • Create parameters, with which we will associate conditional sorting, to provide information consumers the ad hoc capability to dictate sorting criteria;
  • Marry the parameters we create with conditional logic in the sorting properties of the report to support parameterized sorting by the information consumers;
  • Preview the report to ascertain the effectiveness of our solution;
  • Discuss the results obtained with the development techniques that we exploit.

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.


Illustration 1: Current Territory Sales Drilldown Report - Partial View

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).

Hands-On Procedure

Preparation

Create a Reporting Services Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.

2.  Navigate to the Microsoft Visual Studio .NET 2003 shortcut in the Programs group, as appropriate.

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

Click for larger image

Illustration 2: Beginning in Microsoft Visual Studio .NET 2003 ...

3.  Select File --> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 3.

Click for larger image

Illustration 3: Selecting a New Project

The New Project dialog appears. Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services.

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Project in the Templates list.

7.  Type the following into the Name box, leaving other settings at default:

RS016

8.  Navigate to a location in which to place the Report Project files.

The New Project dialog appears, with our additions, as depicted in Illustration 4.


Illustration 4: The New Projects Dialog, with Addition

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 5.


Illustration 5: The New Project Appears in the Solution Explorer

Having created a Report Project, we are ready to proceed with creating the new report clone.

Enhancing the Territory Sales Drilldown Report to Include Ad Hoc Sorting

In this section, we will copy the existing Territory Sales Drilldown report, and then open it in Reporting Services' Report Designer, where we will begin the modifications that the information consumers have requested.

Set up a Data Connection for the Territory Sales Drilldown Report

Our first step is to set up a Data Connection. As we have noted numerous times throughout our series, Reporting Services can connect with, and create the DataSets it needs from virtually any ODBC or OLE DB-compliant data source, (in addition to the obvious MSSQL Server and MSAS data stores). .NET-based API's add the potential for other data sources, assuming that you have a legacy, or otherwise "alternative," scenario on your hands.

Let's set up a Connection to support the DataSet within our practice example.

1.  Right-click the Shared Data Sources folder underneath the new RS016 project tree in the Solution Explorer.

2.  Select Add New Data Source from the context menu that appears, as depicted in Illustration 6.


Illustration 6: Select Add New Data Source from the Context Menu

The Data Link Properties dialog appears, defaulted to the Connection tab.

3.  Click the Provider tab, to select it.

4.  Select Microsoft OLE DB Provider for SQL Server from the Provider list box, as shown in Illustration 7.


Illustration 7: Selecting Microsoft OLE DB Provider for SQL Server - Providers Tab

5.  Click Next, to move to the Connection tab.

6.  In the Data Source box, select the name of the server upon which the AdventureWorks2000 database resides (mine is MOTHER1 in the illustration that follows).

7.  Provide the appropriate authentication information to access the AdventureWorks2000 database on the server you have selected.

I am using Windows NT Integrated Security, as this is not a production environment. For a production environment, selections here require careful consideration.

8.  Select AdventureWorks2000 in the list of data sources that appear when we click the down-arrow selector button at the next box (and thus receive confirmation that our server / authentication information is adequate to display the sources) at the top of the Data tab).

The completed Data Link Properties - Connection tab appears as depicted in Illustration 8.


Illustration 8: Data Link Properties - Connection Tab

9.  Click the Test Connection button to confirm connectivity.

The Microsoft Data Link message box appears, indicating a successful test, as shown in Illustration 9.


Illustration 9: Testing Positive for Connectivity ...

10.  Click OK to close the message box.

11.  Click OK to accept the settings we have made, and to close the Data Link Properties dialog.

The new shared data source appears in the Solution Explorer pane. Let's change the name of the source to make it work with the previously created sample report we are about to clone.

12.  Right-click the new AdventureWorks2000 shared data source.

13.  Select Rename from the context menu that appears, as depicted in Illustration 10.


Illustration 10: Select Rename from the Context Menu ...

The file name becomes "editable" in Windows Explorer.

14.  Take the "2000" out of the name of the .rds file, modifying it to simply "AdventureWorks.rds"

15.  Click outside the editor, in the white space within the Solution Explorer pane, to "set" the changes to the file name.

The shared data source appears as shown in Illustration 11.


Illustration 11: The Renamed Shared Data Source in the Solution Explorer

Having established the data source, we are now ready to create a copy of the Territory Sales Drilldown sample report, which we will then modify to meet the expressed requirements of the information consumers.

Create a Copy of the Territory Sales Drilldown Report

As we have noted, we will be working with a copy of the Territory Sales Drilldown report, to keep the original sample intact for easy access and use at another time.

1.  Right-click the Reports folder underneath the newly created shared data source, in the Solution Explorer.

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


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

The Add Existing Item - RS016 dialog appears.

When we installed Reporting Services, the default installation point for the sample report files was the Samples folder within the Reporting Services program folder. A common example of this default path is as follows:

C:\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Reports

3.  Using the Add Existing Item - RS016 dialog, navigate to the location of the sample reports in your own environment.

An example of the Add Existing Item - RS016 dialog, having been pointed to the Samples folder (which contains the Territory Sales Drilldown report we seek), appears as shown in Illustration 13.


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

4.  Right-click the Territory Sales Drilldown report inside the dialog.

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


Illustration 14: Performing a Quick Copy of the FoodMart Sales Report

5.  Within the Add Existing Item - RS016 dialog, navigate to the RS016 folder we created earlier.

6.  Right-click somewhere in the white space inside the RS016 folder, within the dialog.

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


Illustration 15: Select Paste to Complete Copying ...

A copy of the Territory Sales Drilldown 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:

AdHocSort_Territory Sales Drilldown.rdl

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

The renamed copy of the Territory Sales Drilldown sample report appears as depicted in Illustration 16.


Illustration 16: The New Report, AdHocSort_Territory Sales Drilldown.rdl

11.  Select the new file by clicking it, if necessary.

12.  Click Open on the dialog box to add the new report to report project RS016.

AdHocSort_Territory Sales Drilldown.rdl appears in the Reports folder, within the RS016 project tree in the Solution Explorer, as shown in Illustration 17.


Illustration 17: The New Report Appears in Solution Explorer - Report Folder

Having created a copy of the functional report, we are now ready to make the enhancements requested by the AdventureWorks information consumers.

Enhance the Territory Sales Drilldown Report Structure to Meet Business Requirements

Let's open the new clone of the Territory Sales Drilldown report, and begin our alterations, focusing upon the conditional sorting considerations that form the nucleus of our session. We will first create the parameter structures to support the required ad hoc sorting capabilities, and then we will make modifications to the affected data region of the report itself to complete our enhancements.

1.  Double-click AdHocSort_Territory Sales Drilldown.rdl, within the Reports folder in Solution Explorer, to open it in Report Designer.

The report opens, displaying the report on the Layout tab.

2.  Select Report --> Report Parameters from the main menu, as depicted in Illustration 18.


Illustration 18: Select Report --> Report Parameters from the Main Menu

The Report Parameters dialog appears.

3.  Click the Add button in the lower left corner of the dialog.

A new parameter with default name appears.

4.  For the dialog boxes shown in Table 1, type the corresponding items (replacing defaults where required):

In this Dialog Box:

Type the following:

   
 

Name:

SortSelection

   
 

Prompt:

Sort Upon:

 
 

Data type:

String

   

Allow Blank Value:

Unchecked

   

Available Values:

 

Non-queried

   

Label Row 1:

Total Value

Value Row 1:

TotalDue

Label Row 2:

Sales Order No.

Value Row 2:

SalesOrderNumber

Default Values:

Non-queried

   

Value:

TotalDue

   

Table 1: Sort Selection Parameter Settings

The completed Report Parameters dialog for the sort selection parameter appears as shown in Illustration 19.


Illustration 19: Completed Report Parameters Dialog for Sort Selection

This provides us with the parameter we need for the sort selection. Next, we need to create a parameter whereby we can specify the sort direction. We will do this with the following steps:

5.  Click the Add button in the lower left corner, once again.

A new parameter with default name appears.

6.  For the dialog boxes shown in Table 2, type the corresponding items (replacing defaults where required):

In this Dialog Box:

Type the following:

   
 

Name:

SortAscOrDesc

   
 

Prompt:

Ascending or Descending?

 
 

Data type:

String

   

Allow Blank Value:

Unchecked

Available Values:

 

Non-queried

   

Label Row 1:

Ascending

Value Row 1:

Asc

Label Row 2:

Descending

Value Row 2:

Desc

Default Values:

Non-queried

   

Value:

Desc

   

Table 2: Sort Direction Parameter Settings

The completed Report Parameters dialog for the sort direction parameter appears as depicted in Illustration 20.

Click for larger image

Illustration 20: Completed Report Parameters Dialog for Sort Direction

7.  Click OK to accept input and close the dialog, returning to the Layout tab.

We now have parameters in place to support the two sort considerations for the information consumers. Let's modify the report to leverage our new structures and bestow the capability to control sorting on an ad hoc basis.

8.  Select the Preview tab, to begin a quick check of connectivity and overall report operation.

The report executes, and appears as depicted in Illustration 21.

Click for larger image

Illustration 21: The Report Executes and Appears in Initial "Rolled Up" View

The report appears in its initial "rolled up" state, with the Total Sales summarized at the Territory level.

9.  Expand the Northwest territory row, by clicking the "+" sign to its left.

10.  Expand Pamela Ansman-Wolfe, which appears under the expanded Northwest row.

Ms. Ansman-Wolfe's sales order information appears, as partially shown in Illustration 22.


Illustration 22: "Drilled Down" View of the New Report

We will be able to evaluate the effectiveness of our enhancements best from this level, as we will be working with the Order Number and Total Sales values on the Layout tab in the following steps.

11.  Click the Layout tab to move to the Layout view.

12.  Click the "cell" containing the words "Order Number," within the table data region to "activate" the region, enabling us to see the border with gray selection handles around the table, as depicted in Illustration 23.


Illustration 23: Border and Handles Appear

13.  Right-click the upper-left-corner handle of the region.

14.  Select Properties from the context menu that appears, as shown in Illustration 24.


Illustration 24: Right-Click the Top Left Handle, and Select Properties ...

NOTE: The gray handles seem to disappear, with a semi-transparent border appearing in their place, when we perform the right-click. Selecting Properties from the context menu still gets us where we need to be, assuming the right-corner was selected when performing the right-click.

The Table Properties dialog appears, opening on the General tab.

15.  Click the Sorting tab within the Table Properties dialog.

16.  Input the values shown in Table 3 into the Sort on table:

Expression:

Direction:

   

=IIF(Parameters!SortAscOrDesc.Value="Asc",Fields(Parameters!SortSelection.Value).Value,0)

Ascending

=IIF(Parameters!SortAscOrDesc.Value="Desc", Fields(Parameters!SortSelection.Value).Value,0)

Descending

   

Table 3: Sort On Table Entries for the Sorting Tab, Table Properties Dialog

The Sort on table, on the Sorting tab of the Table Properties dialog, appears as depicted in Illustration 25.


Illustration 25: Our Entries in the Sort On Table, Sorting Tab of the Table Properties Dialog

17.  Click OK to save the entries, and to close the Table Properties dialog.

We return to the Layout view of the Report Designer. We will test our enhancements by again previewing the report.

18.  Click the Preview tab to execute the report.

The report executes, using the parameter defaults. We will rerun it with new settings.

19.  Ensure that Total Value is selected in the Sort Upon parameter selector.

20.  Select Ascending in the selector labeled Ascending or Descending?

The selectors in the report appear as shown in Illustration 26.

Click for larger image

Illustration 26: Parameter Selectors Prior to Executing Report

21.  Click View Report to execute the report.

The report executes and returns data.

22.  Expand the Northwest territory row, by clicking the "+" sign to its left, as we did before.

23.  Expand Pamela Ansman-Wolfe, appearing under the expanded Northwest row, once again.

Ms. Ansman-Wolfe's sales order information appears, sorted by sales value in ascending order, as partially depicted in Illustration 27.


Illustration 27: Sorting by Total Value in Ascending Order

We thus see that our solution has proven effective, as the data is sorted, based upon our parameter selections. Let's try another sort to confirm our evaluation. This time, we will sort by Sales Order Number, descending. (We note that the Order Numbers are not currently sorted in descending order.)

24.  Select Sales Order No. in the Sort Upon parameter selector.

25.  Select Descending in the selector labeled Ascending or Descending?

The selectors in the report appear as shown in Illustration 28.


Illustration 28: Parameter Selectors Prior to Executing Report

26.  Click View Report to execute the report.

The report executes and returns data.

27.  Expand the Northwest territory row, by clicking the "+" sign to its left, as we did before.

28.  Expand Pamela Ansman-Wolfe, appearing under the expanded Northwest row, once again.

Ms. Ansman-Wolfe's sales order information appears, sorted by Sales Order Number in descending order, as partially depicted in Illustration 29.


Illustration 29: Sorting by Total Value in Ascending Order

We thus see that our solution once again produces the desired results, and meets the business requirements expressed by the information consumers.

29.  Select File --> Save All from the main menu to save all work to the present.

30.  Exit Visual Studio when ready.

Conclusion ...

In this article, we continued our exploration of conditional formatting, once again in a basic application, and this time focusing upon the creation of parameterized conditional sorting to allow users to control sorting in an ad hoc manner at report runtime. After making structural changes to a copy of a sample report, based upon the sample AdventureWorks database (both of which accompany an installation of Reporting Services), we set about to meet the business requirements of a hypothetical group of information consumers.

We created a couple of report parameters, which provided the basis for conditional sorting. We associated the parameters with the sorting conditions in the data region of the report under consideration through conditional logic we put into place in the sorting properties of the report. Once we married the conditions of sorting with the parameters we created, we previewed the report to ascertain the effectiveness of our solution. Finally, in our testing of the ad hoc sorting capabilities we had added, we discussed the results obtained with the development techniques that we had exploited.

» 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