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.