Parameterizing the LastPeriods() Function
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 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 an extended
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 specific MDX function, LastPeriods(), to demonstrate the process behind supporting parameterization of an
index value we can supply in this and similar functions at runtime. We
will concentrate, in this article, upon the passage of our selection of a value
to the function, as well as with 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).
Our initial objective
will be to review the basics surrounding the LastPeriods() function, and then to perform an
exercise within which we put the function to work to meet the stated business
requirements of a hypothetical client.
A Quick Overview of the LastPeriods() Function
The LastPeriods() function,
according to the Analysis Services Books Online, “returns a set of
members prior to and including a specified member.” Almost as simple as
it sounds, the function returns (in its typical use within the context of a
time dimension) the set of periods beginning with the period that
follows (or lags) the specified member by a specified index
value, less one, up to and including the specified member. (If the
member is not specified in the function, [Time].[Time
Hierarchy].CurrentMember is defaulted.) Indeed, LastPeriods()
resembles PeriodsToDate(), which we explore in
MDX Time Series Functions, Part I:
PeriodsToDate() and Kindred Functions), and which returns all of
the members back to the beginning of a specified period, although the LastPeriods()
function returns the number of members that is specified by an index
value.
The index
(minus one) that is specified tells the function how far to “look back” for
purposes of the returned dataset. If the index is positive, the function
returns the set of members whose range is headed up by the member “lagging”
one less than the index value from the specified member, and that ends with
the member itself. If the index is negative, then LastPeriods()
returns the set of members that begins with the specified member itself, and
ends with the member leading (or “ahead in time”) by the negative
index value, minus one, from the specified member. Finally, an empty set is
returned if the index value is zero.
As we noted
in MDX Time
Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions, a member of my MDX Essentials series, the LastPeriods()
function allows us to meet numerous common business needs, including (and
especially) those that require that we return (individually or for
accumulation) values from, for example, “several periods back,” up to, and
including, the specified period. That specified member might be, for example,
a specific quarter for which we would like monthly values over the range of,
say, two quarters back through the current quarter. A calculation can be driven
for a several month’s activities, as another example, whose range is determined
by the beginning and ending points that result from the index we provide
(such a case will be the focus of our practice example in this article).
I have
found the LastPeriods() function to
be highly useful in both statistical and financial reporting. The capability
to specify number of periods to “look back,” for example, in patient readings
(both aggregated – say by clinic or campus, etc. - and individual) can become
highly popular among, say, doctors who are tracking the effectiveness of new
treatments and the like, allowing them to extend the “review range” over a
variable number of months that they can specify at run time.
As we
discussed in MDX Time Series Functions, Part II: The OpeningPeriod
() and ClosingPeriod() Functions, balances are typically maintained up to the current
point in time / held at various points in time for many accounts whose
activity we might wish to analyze, such as Sales, Inventory, Purchases – not to
mention various other accounts that contain values besides dollar amounts, such
as shares outstanding, headcount, various quantities, and so forth, in our systems.
The values most
likely to be of interest in typical uses of the LastPeriods() function will
often likely relate to deltas over the given periods. (As an
illustration, the total sales over each of a given group of months - itself the
delta of “ending total sales” between the point in time that we establish as
the specified member within the function, and the total sales value that
existed, in this example, at the earlier point in the year to which we are
looking back, and which is index – 1 months back). LastPeriods()
is excellent for the derivation of “total activity for the last (whatever
number) periods back,” and, thus for a “to date” cumulative total of sorts.
Utilitarian beauty appears, again, in the use of the function with .CurrentMember,
etc. to gain a context sensitive calculation that can flex automatically as
time marches on. Running averages and other derivatives of the accumulated
totals are obvious easy byproducts, as well, given formulation within an
expression that includes LastPeriods().
Common
examples of scenarios where LastPeriods() might come in handy include
monthly totals / balances, over a few quarters, for the inventories of a group
of products we manufacture and sell, for perhaps comparison to the same amounts
for another group of our products, as an aid in determining whether to
discontinue production of a given item or items to allow us to compose a more
rapidly moving product mix. Another illustration might lie in meeting the need
to analyze activity, particularly over several summer months, in total monthly
quantity on hand, for a part that we stock in a Repair Parts account for use in
maintaining our air conditioning plant, to ascertain, perhaps, increased
demands for parts by an aging system.
MDX
provides an excellent means for accumulating these time-ranged activity values
over specified time frames. The LastPeriods() function affords us a
ready means to navigate to the first member of the range for which one endpoint
(the specified member) is defined and for which the opposite end of the range
is specified by the index. When we combine LastPeriods() with the
grouping capabilities found in the Reporting Services matrix data
region, we can extend its power into the flexible generation of “number of
periods look back” that we present within chart and summary reports –
reports for which we can dictate the “number of periods to look back”
at runtime.
Let’s
look at an illustration to further clarify the operation of LastPeriods().
Syntactically, the index and the specified member are placed
within the parentheses to the right of LastPeriods(), as shown in the
following illustration:
LastPeriods(«Index»[, «Member»])
The function returns the set
of members prior to (by «Index» - 1 “member-places”), and including, «Member». The following simple example
expression:
LastPeriods (11, [Time].[1998].[Q3].[8])
would return the
following set of month-members:
{[Time].[1997].[Q4].[10],
[Time].[1997].[Q4].[11],
[Time].[1997].[Q4].[12]
[Time].[1998].[Q1].[1]
[Time].[1998].[Q1].[2]
[Time].[1998].[Q1].[3]
[Time].[1998].[Q2].[4]
[Time].[1998].[Q2].[5]
[Time].[1998].[Q2].[6]
[Time].[1998].[Q3].[7]
[Time].[1998].[Q3].[8]}
As we
shall see, parameterization of the index within the LastPeriods() function
lies at the heart of the procedure we will undertake within our practice
session below. We will detail the steps within a sample report that we will
clone to save time, focusing largely upon setup of the parameter mechanism
within the report dataset, together with its linkage in the corresponding Report
Parameter.
Parameterizing LastPeriods()
I
often parameterize the LastPeriods() function within a Reporting
Services application I am developing as part of a larger implementation of
the integrated Microsoft Business Intelligence solution for a given client.
While this is only a tiny part of the overall structure we typically assemble
for a combined OLAP and relational reporting system, it will provide an
interesting glimpse of the much larger population of opportunities that I find
daily in working with these powerful analysis and reporting tools.
In the
following sections, we will perform the steps required to add parameterized “number
of periods of look back” to an OLAP report. 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, that comes along with an
installation of Analysis Services 2005.
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 state that
ongoing analysis operations would be dramatically enhanced if they could simply
specify, at report run time, the number of months displayed. That is, instead
of seeing the fixed number of months (thirty-seven total months’ data
is available, as we shall see), our client colleagues would like to be able to specify
the number of months for the report to “look back” from the most recent
operating month, July 2004.
Our colleagues inform
us that the current report contains a filter that limits the data
presented to a couple of operating years. In addition to the requirement that
we support flexible presentation of “number of months to look back,” 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.
The new report will
also be simpler 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 group the measures by Month, instead, so as to present
both measures for a given month or a range of months, depending upon our
selection of “number of months look back” at runtime, which we have
discussed earlier. We will add Year and Month report parameters,
which will allow information consumers to run the report “as of” any month –
not just the “latest” month for which values are stored within the Analysis
Services database.
These changes will
result in a multi-purpose report, the months presented by which can be dictated
on the fly by the easy, ad hoc input of varying “ranges of months to
look back,” as well as the “year / month from which to look back.” This
will allow the analysts 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.
» 
Comment and Contribute
