Black Belt Components: Interactive Sorts within a Matrix Data Region

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.


Overview


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 (I’ve 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 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 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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles