Parameterizing Grouping in an Analysis Services Report
Throughout many past
articles of the MSSQL Server Reporting Services series, we have
leveraged parameters within the context of MDX queries. Reporting
Services 2000, initially intended as a component of the MSSQL Server
2005 “BI Release,” was released to market early, as many of us are aware,
with resulting limitations in some of its capabilities (as most realistic
practitioners would expect). One of the challenges that faced many of us was
the absence of an MDX editor: while those of us who were comfortable
with MDX syntax were not impaired significantly (although we had to deal with
circumstances that accompanied parameterization in Reporting Services 2000,
such as the need to string our queries for passage from Reporting Services
to the Analysis Server, and the resulting inability to “test generate”
our datasets, once we had parameters in place within the MDX
queries), those who were already challenged with MDX as a language almost
certainly found no amusement in dealing with the added mechanics. Reporting
Services 2005 introduced the MDX Query Builder, a tool that
appeals, due to its “on-off” flexibility, to most practitioners who are
comfortable writing direct MDX queries, as well as the MDX- challenged among
those authors and developers who need to work with OLAP data sources. This
enhancement, unsurprisingly, changed the way that many of us had become
accustomed to writing the underlying MDX queries for our Analysis Services
reports.
Objective and Business Scenario
In this article, we will
perform a relatively straightforward examination of parameterization within a copy of an
existing sample Reporting Services 2005 report that we will create for
this purpose. Our focus will be to parameterize a row group that
we add to a report that already contains one row group, although the
procedure we undertake will work for column groups, as well. As a part
of meeting a hypothetical client need that we will detail below, we will concentrate
upon the steps we will need to take, and the objects we will need to modify /
add to the functional OLAP report to support parameterization of the
newly added group, as well as parameterizing the sorting of members
within that group. We will also cover, in passing, some of the general details
of setting up parameters and so forth in Reporting Services (we
cover the latter in more detail in various articles I have written for that
specific purpose).
To
provide a report upon which we can practice our exercises, we will begin with
the Sales Reason Comparisons sample report that, among other samples,
accompanies the installation of Reporting Services 2005, and which is
based upon the ubiquitous Adventure Works cube, contained within the
Analysis Services database named Adventure Works DW (which is
available to anyone installing Analysis Services 2005).
The Business Need
For
purposes of our practice procedure, we will assume that information consumers
within the Marketing office of the Adventure Works organization have
expressed the need for modifications to the existing Sales Reason
Comparisons report. The existing capabilities in the
report meet some of their analysis needs, but recent requirements for a more
flexible presentation of the data has resulted in our being called to assist
with creating a custom report that meets an immediate, specialized need in a
user-friendly manner.
In discussing their
requirements in detail, representatives of the Marketing department inform us
that the current report contains a filter that limits the data presented
to a couple of operating years. In addition to the “focus requirements” of
this lesson, they request that we open the “available periods” for reporting -
to the full range of operating data that is physically available within the
cube. The consumers request some additional changes to the report itself,
primarily that we remove the existing report parameter, as well as its
underlying support mechanisms, which allows us to filter products by Product
Category.
They also specify that
they would like to see more simplicity in the new report with regard to other
considerations: Instead of three measures in the column axis, we will
only display two, Internet Orders and Internet Sales Amount.
Moreover, as the report will apply to the Adventure Works organization
as a whole, and will not break out measures by Territory Group, we will
eliminate this existing grouping within the matrix data region. Finally, we
will (column) group the measures by Month, instead.
More to the primary
focus of this article, our client colleagues state that ongoing analysis
operations would be dramatically enhanced if they could simply specify, at
report run time, a “subgrouping” of the information displayed. Once the
report is modified to comply with the general requirements above, they say,
they would like it to generate a “subgroup” below the Sales Reason group.
Moreover, they tell us, they would like to be able to select, at run time, among
multiple group choices: to meet present needs, they want to be able to choose
between Product, Product Category, and Product Subcategory; Product
would be the most frequent selection, made by the largest number of information
consumers, in any given month, but the option to select, instead, Category
or Subcategory, would be useful, as a minority of consumers require
totals among these Product levels on a regular basis to perform various
analytical pursuits. Finally, the capability to perform choices at run time to
sort upon the members of any of the “subgroupings” (regardless of the
actual choice of subgroup itself) would, according to the client
representatives, be of utility to a couple of analysts, as well.
These changes will
result in a multi-purpose report, the “subgrouping” (and its respective sort
order) presented by which can be dictated on the fly by the easy, ad hoc selection
of the Product level upon which to group, as well as to sort,
each time the report is executed. This will, of course, allow the consumers to
rapidly meet differing conditions and analysis needs. As is often the case
with the addition of parameterization, the organization will ultimately
be able to accomplish more with fewer reports.
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. Then, having
grasped the stated need, and having confirmed our understanding with the
intended audience, we begin the process of modifying the Sales Reason
Comparisons report to satisfy the information consumers.
» 