Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1 - Page 2

Meet Business Needs with Matrix Dynamics

Objective and Business Scenario

The matrix data region, in my opinion, is one of the most valuable tools in the Reporting Services toolbox – certainly, when one is employing the application to generate rich presentations based upon OLAP cubes. The forehanded use of the matrix data region, as we have seen to be the case with many other Reporting Services objects within articles of my MSSQL Server Reporting Services series, can enable a report author or developer to accomplish many things that do not seem possible "out of the box," and certainly in ways that are impossible within other popular enterprise reporting applications.

In the following sections, we will illustrate uses for the matrix data region to achieve objectives that are beyond the limitations of the table 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 Adventure Works cube contained within the Analysis Services database, Adventure Works DW, which is available with the installation of the MSSQL Server 2005 samples. The Sales Reason Comparisons report is intended to present comparison summary data from the Adventure Works cube. For the purposes of our article, we will say that we are working with a team of information consumers within the Office of the Vice President - Marketing of our client, the Adventure Works organization.

To illustrate the business requirements of this client group, let's say that the consumers have expressed the need for modifications to the existing Sales Reason Comparisons report. They tell us that the report as it appears today, constructed by an individual who is no longer with the organization, would serve as an excellent basis for newly extended requirements, in that the columns and rows of the report are consistent with the objectives of the report they envision. The existing report, however, is a static report that depicts information for various territory groups of the organization in an inflexible manner. While the current presentation might have been adequate, they tell us, before the advent of the new portals that have gradually taken hold within the organization as a vehicle for distributing information, the need now is for this information to be presented in a manner that allows consumers to select one or more territories to view at runtime, rather than to see all territories together, as they appear anytime the existing report is executed.

In addition to having the territories parameterized, the consumers tell us that they want a complete "report" (axes and all) to appear for each of the territories selected. They tell us that this is because the report under consideration will appear in a portal window that we expect to only be large enough to present a single territory at a glance, but for which a scroll bar (or, alternatively, a paging mechanism) will appear when, say, multiple territories appear in the window, so that users can scroll (or page) over to see all as needed. Scrolling over from one territory's data to the next, either with the existing report or even a "standard" matrix report (which shares the row axis among dynamic columns), however, would mean that the row axis would not appear in the presentation for the territories that we brought into view by scrolling right. For this reason, among others where the report will be presented via other mechanisms, the consumers wish for multiple territories to be presented as multiple stand-alone report objects / views.

We see immediately, upon examination of the Sales Reason Comparisons report, that it consists of a matrix data region, used in rather "vanilla" way. We surmise that a matrix data region will, indeed, handle variable / multiple territory specifications at runtime, with minimal alterations to the report file as a whole. The flexibility of the matrix would especially present itself when, as the consumer specified, say, two territories, the number of columns that the matrix generated would increase to meet the requirement. Moreover, the need to present the data as standalone matrices, too, can be handled via the capabilities of the matrix data region, albeit in a manner that might not be readily intuitive. Having done similar things for other clients, we agree to leverage a procedure we have followed before to prove the concept with the Adventure Works team's data.

The Sales Reason Comparisons report, as it was originally created, appears as depicted in Illustration 1.

We work with the team to construct a rough draft that shows what the same report would look like within the scenario that they have requested we help them to accommodate. The draft, presenting the Sales Reason data for three territories, appears as depicted in the spreadsheet shown in Illustration 2.

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, within the Practice section that follows.