Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II - Page 2

Parameterizing the LastPeriods() Function (continued ...)

Throughout many past articles of the MSSQL Server Reporting Services series, we have leveraged parameters within the context of MDX queries. To reiterate my introduction in Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I, 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. 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.

As we discussed in Part I, Reporting Services 2005 introduced the MDX Query Builder, a tool that satisfies, due to its “on-off” flexibility, 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 Part I, we stated our objective to perform an extended examination of parameterization within a copy of an existing sample Reporting Services 2005 report that we created for this purpose. Our focus, we stated, was and is 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 stated that we would concentrate, in this two-part article, upon the passage of our selection of a value to the function, more than the details of setting up parameters and so forth in Reporting Services. (We cover the latter in detail in various articles I have written for that specific purpose), although we do touch upon most aspects of setting up parameters in general.

In Part I, we reviewed the basics surrounding the LastPeriods() function, and then we began to prepare to perform an exercise within which we would put the function to work to meet the stated business requirements of a hypothetical client.

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 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. (We will work within a simple “months” scenario in our practice session, but keep in mind that LastPeriods() can certainly flex to various levels of a dimensional hierarchy).

I emphasized in Part I of this article that 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 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.

We noted that the values most likely to be of interest in typical uses of the LastPeriods() function will often 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 [specified number] periods back,” and, thus for a “to date” cumulative total of sorts. Further power can be gained, again, in the use of the function with .CurrentMember, etc. to derive 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().

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 dynamic 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 review an illustration I used in Part I 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 noted in Part I of this article, and as we shall see in our practice sections below, parameterization of the index within the LastPeriods() function lies at the heart of the procedure we shall undertake within our procedural example below. We will detail the steps within a sample report, which we have cloned to save time, focusing largely upon setup of the parameter mechanism within the primary report dataset, together with its linkage to the corresponding Report Parameter and elsewhere.

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 typically assembled 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 to practice our exercises, we began in Part I 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.

To restate the scenario we posed in Part I, we will assume that information consumers within the Marketing office of the Adventure Works organization have expressed the need for modifications of the existing Sales Reason Comparisons report. The existing capabilities in the report meet most 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 have stated 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 told us that they 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 informed us, again as we summarized in Part I, that the original report needed some basic changes (removal of a filter that limited the data presented to a couple of operating years, so as to open all “available periods” within the cube for reporting; removal of a pre-existing report parameter and its underlying support objects; etc.), which we accomplished within the preparation section of Part I. We also streamlined and regrouped the report in a couple of ways, to allow grouping of fewer measure totals, by months instead of by Sales Territories. Grouping the two remaining measures of interest by month, we noted, would allow us 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. Finally, we added year and month report parameters, to 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.

The modifications we made in Part I, and that we will continue to make in this half of the article, 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,” allowing 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.

For more information on the LastPeriods() function, as well as for the original specification of the needs as expressed by our client colleagues, see Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I.