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), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.
About the BlackBelt Articles ...
As I state in BlackBelt Components: Manage Nulls in OLAP Reports and other articles of this subs-series, 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) or method(s) under consideration. I typically accomplish this by using existing report samples or other prefabricated objects that either come along as part of the installation of the applications involved, or that are otherwise readily accessible to virtually any organization that has installed the Microsoft business intelligence solution. While we will often have to refine the sample involved (we will typically create a copy, to allow the original sample to remain intact), to provide the specific 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 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.
In this article, we will get more hands-on exposure to providing an interactive sort capability for the information consumers we support. Our hands-on practice will specifically focus on the establishment of interactive sorting within a Matrix data region. We will discuss the general concepts, and then set up a scenario within which we work with a basic OLAP report to expose the steps involved. In establishing interactive sort capability for a Reporting Services Matrix data region, we will:
- Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared Analysis Services data source;
- Create a clone of an existing sample report, containing a Matrix data region, with which to perform our practice exercise;
- Make structural enhancements to the clone report, to meet the business requirements of a hypothetical group of information consumers for interactive sorting within a Matrix data region;
- Preview the report to ascertain the effectiveness of our solution;
- Discuss the results obtained with the development techniques that we exploit.
Interactive Sorts within a Matrix Data Region
Objective and Business Scenario
In Black Belt Components: Interactive Sorting within Reporting Services, we discussed the advent of interactive sorting with Reporting Services 2005, and then set about getting some exposure to providing this capability to the information consumers we support. While our practice exercise focused on the addition of interactive sorting within a Table data region, we mentioned that such sorting is possible within Matrix, Table or List data regions, according to the Books Online and other documentation.
Since the publication of Black Belt Components: Interactive Sorting within Reporting Services, I have become aware that some are finding it difficult to implement interactive sorting within a Matrix data region (Ive even read the pronouncement that interactive sorting is impossible in a matrix, and similar, in forums and elsewhere on the Web). In this article, I will treat this scenario specifically, in order to demonstrate that interactive sorting does, indeed, work within the Matrix data region, as well as to provide hands-on guidance in making this a straightforward process.
For more information about interactive sorting in general, and the specific steps involved in adding this capability within a Table data region, see my article Black Belt Components: Interactive Sorting within Reporting Services. As I noted in this earlier article, the new capability to provide interactive sorting via column heading properties will be popular, indeed. What we have had to accomplish with conditional formatting in Reporting Services 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 the following sections, we will perform the steps required to enable ad hoc sorting, from the perspective of the information consumer, within an OLAP report containing a Matrix data region. To provide a report upon which we can practice the steps of our hands-on exercise, we will begin with the Sales Reason Comparisons sample report, based upon the AdventureWorks DW Analysis Services database that is available with the installation of the MSSQL Server 2005 samples. The Sales Reason Comparisons report is intended to present comparative summary data for each of six classes of reasons behind the sales of Adventure Works products (the defined titles of the reasons for which sales are determined to have taken place are Manufacturer, On Promotion, Other, Price, Quality or Review). The data is presented for specific Product Categories (a parameter is in place to allow selection of the desired category at runtime), for each of the three Sales Territory Groups within the organization. For the purposes of our article, we will say that the intended audience for the report is a group of information consumers within the Controllers Office of our client, the Adventure Works organization.
To illustrate the business need, lets say that the information consumers have expressed the need for modifications to the existing Sales Reason Comparisons 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 Sales Reason 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 Sales Reason title that appears for each line item. (While sorting capabilities are, of course, more meaningful with longer lists, the requirement at hand will still illustrate the means of achieving our desired ends.)
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 Reason Comparisons report to satisfy the information consumers.
Our first objective is to create a copy of the Sales Reason Comparisons sample report, into which we can implement the new interactive sorting enhancements from the perspective of the SQL Server Business Intelligence Development Studio. The focus of our efforts is the addition of an interactive sort capability to a matrix data region within a report (the mechanics behind adding the capability, not the design of the report itself). Because of time limitations, we will be working with a simple, pre-existing sample report in reality, the business environment will typically require more sophistication. The process of setting up interactive sorting is the same in real world scenarios, with perhaps a more complex set of underlying considerations involved; I often encounter the need to add multiple sorts, subsorts, and the like, within client environments.
We will perform our practice session from inside the MSSQL Server Business Intelligence Development Studio. For more exposure to the Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my 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 Reason Comparisons 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 that forms 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
For purposes of our practice session, we will open the AdventureWorks Sample Reports project, which contains the sample reports that ship with the Reporting Services component of the MSSQL Server 2005 integrated business intelligence solution. We will complete our practice session within the sample project so as to save the time required to set up a development environment from scratch within the Business Intelligence Development Studio.
To open the AdventureWorks Sample Reports project, please see the following procedure in the References section of my articles index:
Ascertain Connectivity of the Shared Data Source
Lets ensure we have a working data source. Many of us will be running side-by-side installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone. (The default for the Adventure Works DW project samples connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default.)
If you do not know how to ascertain or modify connectivity of the Analysis Services data source, please perform the steps of the following procedure in the References section of my articles index:
Create a Copy of the Sales Reason Comparisons Report
We will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, which we will use for our practice exercise in meeting the business requirements of our Adventure Works colleagues. Creating a clone of the project means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Reporting Services (particularly an OLAP report using an Analysis Services data source), and other components of the Microsoft integrated business intelligence solution in general.
If you do not know how to create a copy of an existing report, please perform the steps of the following procedure in the References section of my articles index:
We now have a clone OLAP report file within our Reporting Services 2005 Project, with which we can proceed in the next section to add interactive sort capabilities within a Matrix data region.
Procedure: Add Support for Interactive Sorting in a Matrix Data Region
We might, of course, have used a conditional sorting approach to provide interactive sorting, much like we used in Black Belt Components: Ad Hoc Sorting with Parameters. Such an approach might form the only option in a Reporting Services 2000 scenario, and will still work in Reporting Services 2005. However, as we have already noted, our specific focus in this section will be the addition of support for interactive sorting through leveraging the new Analysis Services 2005 properties settings as we did for a Table data region in Black Belt Components: Interactive Sorting within Reporting Services only this time for a Matrix data region, within the context of an OLAP report.
Lets 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 DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.
2. Select Open from the context menu that appears, as shown in Illustration 1, as necessary.
Illustration 1: Opening the New Report ...
DBJ_OLAP_Report.rdl opens in Layout view.
We are now positioned to enable the report to support interactive sorting. We will do so from within the Layout view, where we will access the properties settings for the Sales Reason textbox.
3. Within the Layout view, right-click the textbox containing the label Sales Reason (the heading for the Sales Reason column in the report).
4. Select Properties from the context menu that appears, as depicted in Illustration 2.
Illustration 2: Accessing the Properties for the Sales Reason Column Heading ...
The Textbox Properties dialog opens.
5. Click the Interactive Sort tab of the dialog.
6. Click the check box atop the tab, labeled Add an interactive sort action to this textbox.
The checkmark appears in the check box, and the interactive sort settings become enabled.
7. In the Sort expression box that appears immediately underneath the check box, select the following from the options that appear within the selector (or simply type it in):
8. In the section just below the Sort expression box, labeled Data region or grouping to sort, ensure that the radio button labeled Current scope is selected.
9. In the bottom half of the tab, in the section labeled Evaluate sort expression in this scope, click the radio button to the immediate left of the label Choose data region or grouping.
10. In the dropdown selector that appears immediately underneath the Choose data region or grouping label, type the following:
We have selected the Sales Reason column label as the placement position for the sort action button, which, as we shall see, will be automatically created for us. By selecting current scope as the data region to sort, we have indicated that we wish to sort the Matrix data region, based upon our sort selection (ascending or descending, via a button that will appear) within the textbox that serves as the column heading. Finally, we have directed that we wish to sort based upon the contents of the Sales Reason group, established as matrix1_Sales_Reason in the pre-existing report. (We can obtain the group name by examining the Matrix Properties dialog Groups tab.)
Virtually every issue encountered, as communicated to me by client colleagues or readers, with the establishment of interactive sort capability within Matrix data regions in Reporting Services, has resulted from failure to grasp the concepts at work within the three sections of the Interactive Sort tab. The most common complaint I hear is that interactive sorts simply cannot be made to work within the Matrix, as the developer is experiencing inaction, or an error, once he / she attempts to use a sort put into place.
A common sort is on a Matrix row group, as we have seen in this example. The fact that we are using a textbox to trigger the sort is confusing to some, who initially try to place the interactive sort trigger upon a field within the group. For example, in our illustration, they might choose =Fields!Sales_Reason.Value within the lower selector of the tab (in the Evaluate sort expression in this scope section). Alternatively, they might select Detail scope within this same section, and not use the Choose data region or grouping selector at all. In either case, an attempt to then preview the report will result in the ascending / descending icon (an upward and downward pointing pair of carat symbols) appearing to the right, in our example, of the Sales Reason label. An attempt to then sort, using the upward or downward pointing carat, will result in either failure of the region to sort, or in an error (depending upon the settings erroneously chosen).
The Interactive Sort tab of the Textbox Properties dialog appears, with relevant settings circled, as shown in Illustration 3.
Illustration 3: Interactive Sort Tab Settings Textbox Properties Dialog
11. Click OK to save our settings and to dismiss the Textbox Properties dialog.
We return to the Layout view, where we are now ready to verify the effectiveness of our modifications.
Verification: Preview the Report and Inspect Effectiveness of Interactive Sorting
Lets preview the report to inspect the results of our handiwork.
1. Click the Preview tab.
DBJ_OLAP_Report.rdl executes, and returns the data for the default Product Category indicated within the parameter pick list in the upper left corner of the report. We note this time, however, the appearance of upward / downward pointing arrows, alongside the Sales Reason column heading, as depicted in Illustration 4.
Illustration 4: Interactive Sorting is Enabled ...
Recall that we have established sorting based upon the members of the Sales Reason group. Clicking the up arrow should, therefore, sort the Sales Reasons listed, in ascending fashion alphabetically, (this is actually their default sort).
2. Click the upper of the two arrows.
The report remains in an alphabetically ascending order, as expected. We see the downward pointing arrow disappear, leaving the upper arrow in place to indicate that Sales Reason has been sorted (and has only one remaining direction in which it can be re-sorted).
We can just as easily sort in descending fashion, by clicking the upward pointing arrow that remains. The re-sort will occur, leaving the Matrix data region sorted by Sales Reason, this time in alphabetically descending order (with a downward pointing arrow in evidence), as partially shown in Illustration 5.
Illustration 5: Descending Sort Accomplished ... (Partial View of Report)
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. We can see that interactive sorting is supported for the Matrix data region just as it is for the Table data region, as documented in the Books Online.
3. Select Save -> Save All to save our work to this point.
4. Select File -> Exit to leave Reporting Services, when ready.
In this article, we extended our previous examination of interactive sorting within Reporting Services to specifically focus on establishment of this capability for a Matrix data region. This was in an attempt to address difficulties experienced within the development community in supporting this capability for a Matrix. Our overall objective was to get hands-on exposure to employing interactive sorting to support 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 elsewhere within 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 Matrix data regions within existing reports in our own environments. As we performed the necessary settings to enable interactive sorting within the report, we addressed the importance of scope within the sort 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.