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.
Illustration 1: Rough Draft
of the Proposed Presentation Layout Multiple Territories Selected
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.