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

Meet Business Needs with Matrix Dynamics – Continued ...

Objective and Business Scenario

Expressing my opinion in Part One that the matrix data region is one of the most valuable tools in the Reporting Services toolbox, I further opined that this is often nowhere more true than when one is employing the application to generate rich (and clever) 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 often in ways that are impossible within other popular enterprise reporting applications.

In the following sections, we will continue the illustration we began in Part One of how we might artfully employ the matrix data region to achieve objectives that are beyond the limitations of the vanilla matrix or table data regions. Recall that, to provide a report upon which we could practice the steps of our hands-on exercise, we began with a copy of the Sales Reason Comparisons sample report, whose data source was 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 sample Sales Reason Comparisons report is intended to present comparison summary data from the Adventure Works cube.

For the purposes of our two-part article, we created a setting within which we were 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, we said that the consumers had expressed the need for modifications to their existing Sales Reason Comparisons report, informing us that the report as it appeared at the time of our dialog, would serve as an excellent basis for newly extended requirements. The columns and rows of the report were consistent with the presentation objectives of the report they next envisioned. Among the limitations that made the existing report less than adequate was its nature as a static report. While its inflexible depiction of information for various territory groups of the organization might have been adequate, they told us, before the advent of the new portals that have gradually become the engines for information distribution within Adventure Works, the new need is for this information to be presented in a manner that allows analysts and other 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 parameterization, the consumers tell us that they want an even more innovative feature: they want "complete reports" (axes and all) to appear for each of the territories selected. This, they have told us, 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 group 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.

In Part One, we worked with the team to construct a rough draft that represented the way that their then-current report would look within the scenario that they had requested we help them to accommodate. The draft we constructed, providing an example of the Sales Reason data for three territory groups, is replicated, once again, in the spreadsheet shown in Illustration 1.

Having grasped the stated need, and confirmed our understanding with the intended audience, we began the process of modifying the Sales Reason Comparisons report to satisfy the information consumers. We will resume where we left off in Part One, within the Practice section that follows.