Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 13, 2008

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

By William Pearson

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:


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM