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 27, 2008

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

By William Pearson

About the Series ...

This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services (“Reporting Services”), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.

About the Mastering OLAP Reporting Articles ...

One of the first things that become clear to “early adopters” of Reporting Services is that the “knowledgebase” for Analysis Services reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts (where I treated the subject of cascading parameters for Reporting Services 2000), the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, even taking into consideration the release of several books surrounding Reporting Services 2005 in recent months, continues to represent a serious “undersell” of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible for everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, we can rest assured that the OLAP potential in Reporting Services will contribute significantly to the inevitable commoditization of business intelligence, via the integrated Microsoft BI solution.

For more information about the Mastering OLAP Reporting articles, see the section entitled “About the Mastering OLAP Reporting Articles” in my article Ad Hoc TopCount and BottomCount Parameters.


As I noted in Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I, we have focused, in several recent articles of this series, upon various aspects of parameterization within the Reporting Services environment. In some cases we have supported parameterization from structures completely contained within Reporting Services, and in others we have created parameter (predominantly picklist) support from within other layers of the integrated Microsoft business intelligence solution. As many of us are aware, enterprise reporting applications typically allow for parameterization (via what are sometimes known as “prompts” or “parameter prompts”) to enable information consumers to quickly find the information they need from a report. These parameters, whose values are physically passed to an axis specification or a slicer in the dataset query, often act to put filters into place “on the fly;” the filters are thus enacted when the consumer types or selects a value, or a series of values, at run time.

We emphasized, in Part I, that, because they allow information consumers to assume a role in guiding the delivery of information – and add a “self-serve” component to the reporting experience - parameterization in general is a popular topic in the forums and newsgroups of most enterprise reporting applications. My continued application of the underlying concepts over many years within Cognos, Crystal, Business Objects, MicroStrategy, and a host of other, more specialized applications, has given me a great appreciation for the opportunities that exist in the business environment for effective parameterization. Whether the reports are to be printed, displayed on screen, or any of the other options for production / deployment, it is challenging to overstate the value that parameterization can add in making the selection and delivery of enterprise data more focused and consumer-friendly.

While I have extended parameterization concepts into many arenas, none have captured my attention as much as their deployment within the integrated Analysis Services / Reporting Services pairing. These applications work together to provide business intelligence in a way that is powerful and highly flexible. As I have noted in Part I, I often advise clients who are attempting to locate a consultant to implement the integrated Microsoft BI solution (composed of MSSQL Server, MSSQL Server Analysis Services, and Reporting Services) to seek a “multidimensional architect” - a consultant who has a good working knowledge of each of the components, and who can determine where, among three or more possible “logical layers,” to place which components so as to optimize the system as a whole.

NOTE: For details surrounding hands-on approaches (as you will see, they are Legion) to the mechanics behind supporting parameterization, (including the generation of picklists) in Reporting Services, see these articles in MSSQL Server Reporting Services series here at Database Journal:

Throughout these articles, as well as elsewhere, we have generated simple lists to provide virtually all we need to support parameterization within Reporting Services and other enterprise reporting applications. In this article, we will continue to pursue the objective we stated in Part I: We will perform a more detailed examination of the mechanics behind parameterizing an MDX function – or more precisely, for parameterizing the variable argument within such a function. We will illustrate the process using the popular LastPeriods() function, which we introduced in MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions (a member of my MDX Essentials series at Database Journal), but the same logic can be extrapolated to many other similar MDX functions, as I have noted, and will continue to note, in my articles.

In this article, we will continue to get hands-on exposure to parameterizing LastPeriods() within the sample OLAP report that we cloned in Part I. Beginning with the general concepts, in Part I, we began our practice session by setting up a scenario within which to work with a basic OLAP report in exposing the steps involved in parameterizing the LastPeriods() function specifically. We began by opening the sample Report Server project, AdventureWorks Sample Reports, and ascertaining connectivity of its shared Analysis Services data source. Next, we created a clone of an existing sample report, containing a matrix data region, with which to perform our practice exercise.

Once we had made structural modifications to the clone report, to further prepare for our practice exercise session, we performed a brief overview of the MDX LastPeriods() function, discussing details of the use we intended for the function to perform in support of the stated reporting needs of a hypothetical client, as well as touching upon general concepts surrounding the parameterization of MDX functions in general, and the LastPeriods() function specifically.

In this, the second part of our article, we will:

  • Ensure the adequacy of the datasets, created automatically when we added query parameters as a last step in Part I, to support report parameters and meet business requirements;
  • Add syntax to the Month dataset query to enforce cascading, based upon the selection made for the Year parameter by an information consumer at runtime;
  • Leverage the MDX LastPeriods() function, containing index and Month parameter placeholders;
  • Discuss the interaction of the various components in supporting the runtime parameter that the end consumer sees;
  • Discuss the results obtained with the development techniques that we exploit.

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