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

BI Architect Bill Pearson continues his hands-on demonstration of an approach to the parameterization of the highly useful LastPeriods() function.

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.

Overview

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.

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.

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.

Practice – continued ...

Recall that we left off working, in Part I, with the copy we had created of the Sales Reason Comparisons sample report, after we had accomplished the preparatory changes noted above. We had progressed the report through the earliest procedures for adding – and parameterizing – a single MDX function, LastPeriods(), in combination with taking the initial steps to parameterize the Date dimension itself (both Year and Month), so as to allow our information consumers to declare an “as of” date within the report (from which to “look back”, as it were), rather than forcing them to rely upon “current month” (or last month with activity within the cube) as the “starting point” from which they could “look back”. We concluded Part I with the addition of the required query parameters to support both date and function parameterization.

We stated that we would resume our practice session by reviewing the two datasets that were created, primarily to ensure their adequacy to support the associated date-related report parameters in meeting the business requirements of our client colleagues. We will resume our practice session in the sections that follow.

Ensure Adequacy of Automatically Created Datasets to Support Report Parameters and Meet Business Requirements

Let’s take a brief look at the two datasets whose automatic creation by Reporting Services was triggered by our addition of the TimeYear and TimeMonth query parameters earlier. Our focus here will be to understand their construction and to ascertain their fitness to support the Year and Month report parameters.

When we looked at the Report Parameters that were created within our last section, we may have noted that each referenced its respectively named, underlying dataset. Let’s return to the Report Parameters dialog to inspect the resulting relationship further.

We recall that we had previously removed all Report Parameters, as part of our preparation for the practice exercise. Yet we note, within the Report Parameters dialog that has appeared (as we did in Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I), the presence of three new Report Parameters, called TimeYear, TimeMonth, and LookBackSpan. These Report Parameters, as we have learned, were created automatically when we created the associated rows within the Query Parameter dialog on the Data tab.

Another important observation we might make for the TimeYear (and all observations we make here for TimeYear apply to the TimeMonth parameter, within the context of its own supporting dataset, as well) parameter is that the From query setting within the Available values section of the Report Parameters dialog is active (the radio button is selected), and that the setting references a Dataset (and the associated Value and Label fields) with the same name as the new Report Parameter - a Dataset, that has also been automatically created. The purpose of this Dataset is to support the selection checklist for the new Report Parameter.

Finally, we note that the MDX qualified name for Fiscal Year 2005 ([Date].[Fiscal].[Fiscal Year].&[2005] ), is referenced as the Default Value for the Report Parameter. (Even though we use the MDX qualified name within the Default Value here, as at any other time in the Report Parameter dialog when we set a default, the “label” name - a “consumer-friendly” name, generally - is what actually appears within the parameter selector at runtime.)

We will now return to the Data tab, to examine one of the datasets which have been automatically created (among other reasons, to populate the parameter picklist at report run time) – the dataset (and dataset query, of course) specifically created to support the new TimeYear report parameter. (The columns of the TimeYear dataset are identical to those of the TimeMonth dataset, and so we will examine only the former at present.)

The dataset query appears, presenting the MDX syntax in Query view. This is the query half of the query / dataset pair that have been automatically created to generate the parameter picklist (as well as the MDX qualified name that is passed to Reporting Services, based upon the selection made at runtime, among other items) to support the new TimeYear report parameter.

The query executes, and the data grid becomes populated, as shown in Illustration 3.

An examination of the query and the data it returns reveals that, in addition to retrieving data directly from the cube, the automatically generated query creates additional derived columns whose sole purposes are the support of the report parameter, from whose picklist information consumers make the selections at runtime, and from which MDX-qualified values are passed to Reporting Services to generate the associated report. The derived columns are:

• ParameterCaption
• ParameterValue
• ParameterLevel

All three fields are products of calculated members created via MDX syntax (using the WITH MEMBER keyword combination), within the dataset query. As we discover within other articles of the series, there are multiple ways to approach picklist support (For example, I very often construct members such as the above at the Analysis Services level, to afford central maintenance and reusability, among other, perhaps less obvious, advantages). This is simply the mechanism through which Reporting Services accomplishes support of the automatically generated report parameter. As we saw earlier, the Value and Label references within the report parameter dialog reference the ParameterValue and ParameterCaption fields, respectively, within this Dataset.

As I note repeatedly throughout the extended examination of parameters that I perform within this and numerous other articles throughout the MSSQL Server Reporting Services series, it is important to realize how Reporting Services automatically creates both the report parameter and the dataset that supports the parameter at runtime. As we venture into custom parameterization within various practice sessions elsewhere, I urge my readers to keep in mind how the parts knit together within this basic context, so as to understand the relationships that we need to establish manually.

Having discussed the components of the two automatically created datasets, TimeYear and TimeMonth, we are ready to make a single modification to each. If we examine the TimeYear dataset at this point, we can see that both the ParameterCaption and ParameterValue derived columns include a great deal more than year-level members. For example, we see not only a caption for FY 2002 itself, but captions for FY 2002 semesters (such as H1 FY 2002), quarters (such as Q1 FY 2002), months (such as July 2001), and individual dates (such as July 1, 2001). These occurrences are to be expected, based upon the MDX syntax used within the system-generated dataset query, but the varying granularity of the captions and values poses a problem when we consider that these columns exist to support single-level parameter picklists within the report at runtime. This means, for example, our TimeYear dataset needs to provide only the Year (or, more specifically in our case, Fiscal Year) level members to the ultimate picklist.

We can filter out all except the Year level members by using the ParameterLevel column for this purpose. As we can see, the Year level members of the dataset comprise ParameterLevel 1. All we have to do is filter by this level, as we shall do in the following steps:

The Dataset dialog appears, defaulted to the Query tab, as shown in Illustration 5.

=1

The Dataset dialog, along with our settings, appears as shown in Illustration 7.

Next, we will add a similar filter to the TimeMonth dataset. The steps are almost identical as those we took for the TimeYear dataset; the only material difference is that the ParameterLevel for the Month level within the Date dimension is identified by the number “4”.

The Dataset dialog appears, once again.

=4

The Dataset dialog, along with our settings, appears this time as depicted in Illustration 8.

Let's take a quick look at the picklists for the date-related parameters, to ascertain the effectiveness of our dataset filters, as well as to verify their satisfactory operation.

The report runs, and then displays the drop-down parameters as specified in the preceding steps, including the defaults requested.

The parameter picklists appears, displaying months as partially shown in Illustration 9.

As we can see, our filter has been effective in populating the parameter picklist with months only. (If we click the downward pointing arrow to the right of the Year parameter selector, we can see that there, too, the correct parameter level only is displayed.) But while our dataset filters appear to be effective, we note in the Month parameter picklist that the months displayed are not restricted to FY 2005. This indicates that the Month parameter picklist is not cascading appropriately.

As is probably obvious, we must perform some additional steps before the new Month parameter picklist cascades correctly. Moreover, even though the parameter picklists in general appear to function at the Preview tab level, we will need to perform the steps to make them actually work (to make the “look back” capability actually restrict the presentation to the number of “look back” months specified, etc.). These steps lie within the realm of the underlying dataset query, within which we will be working in the next section.

Before we do that, let’s return to the report parameters dialog to conclude this section with a couple of cosmetic changes.

Year:

The Report Parameters dialog, including our setting changes, appears as depicted in Illustration 11.

Month:

The Report Parameters dialog, including our setting changes, appears as shown in Illustration 12.

Finally, let’s make some settings for the new LookBackSpan parameter. As we noted in Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I, the LookBackSpan parameter will differ from the other two automatically created parameters, particularly in the fact that it will not reference a corresponding, automatically created dataset – a situation we both described and explained in Part I. Because the parameter picklist will simply offer a range of numerals as selection options, we will reference those numerals through direct specification within the Report Parameters dialog (although other approaches exist to support such a parameter, such as using a manually created dataset, etc.)

No.Months to Look Back:

The Report Parameters dialog, including our setting changes, appears as partially depicted in Illustration 13.

As we mentioned earlier, we must perform some additional steps before the new parameters actually work. These steps will need to be accomplished within the syntax of a couple of underlying dataset queries, within which we will be working in the next section.

Work with Underlying Dataset Queries to Enforce Cascading and Support Parameterized “Look Back” Capability

Add Syntax to the TimeMonth Dataset Query to Enforce Cascading

We will return to the Data tab, at this point, to make modifications to the primary dataset query to both properly enable the date-related parameters for Year and Month, as well as to add and enable the MDX function that will support the parameterized “look back” capability within the report that our client colleagues have requested.

Before we work specifically with the primary dataset query, however, we will modify our TimeMonth dataset query to support the cascading parameter picklist for the Month parameter, the current absence of which we discussed earlier. As we mentioned before, we seek to enforce cascading so that the appropriate child members (Months) appear within the Month parameter picklist, based upon the selection made in the Year parameter parent level to which they belong.

At this stage, we face a couple of modifications to the TimeMonth dataset. First, we must add a Query Parameter within the dataset for TimeYear, and then we must embed the parameter, via a placeholder, into the syntax. We shall make these modifications within the steps that follow. We will then need to make a relatively minor final adjustment to the TimeYear report parameter, as a reaction to a reset that it performs based upon our addition of the Query Parameter within the TimeMonth dataset, as we shall see.

The dataset query appears, presenting the MDX syntax in Query view, as we saw in earlier steps. Because we are establishing a dependency, based upon the TimeYear dataset, within the TimeMonth dataset, we must establish this dependency within a Query Parameter within the dataset, just as we did on a larger scale within the primary dataset query earlier.

The Query Parameters dialog (currently empty) appears.

Here we add the new “variable” we are associating, via the query syntax, with the TimeMonth parameter picklist, which appears at runtime. It is in this manner that we build in support for cascading of (only) the appropriate child-months.

TimeYear

The Query Parameters dialog appears, with our addition, as shown in Illustration 14.

We are returned to the Data tab, where we can see the original MDX syntax in the Query pane. The MDX appears as depicted in Illustration 15.

All that remains to enforce a cascading TimeMonth parameter is to insert the appropriate syntax into the TimeMonth dataset query. Our overall objective is simply to force a dynamic “filter” based upon the value of the TimeYear parameter (which we have already “declared” within the Query Parameters dialog). We will do this in the steps that follow:

FROM [Adventure Works]

FROM 
   ( 
      SELECT 
         ( 
            STRTOSET(@TimeYear, CONSTRAINED) ) ON AXIS(0) 
         FROM  
           [Adventure Works]
         )

The entire query should appear as shown in Illustration 16 (I have expanded the additional syntax to promote easier understanding).

The query executes, and a data grid becomes populated, as partially depicted in Illustration 17.

We can scroll through the results dataset and easily see that only those Months that are the children of FY2005 (the default TimeYear parameter value) appear. This will support our desired objective, selections within the Month picklist at runtime that are associated with (and only those that are associated with) the year that is selected at runtime within the parent TimeYear parameter. We will ascertain the effectiveness of our handiwork via the performance of a simple Preview later, once we have completed the syntax modification procedures in the next subsection, where we make final syntax modifications - this time to the primary dataset - to embed the “look back” capabilities requested by our client colleagues, based upon the selections they make at run time for year, month, and “number of months to look back.”

Leverage the MDX LastPeriods() Function, Containing the Year and Month Parameter Placeholders

Our last procedure will comprise “turning it all on” by another syntax modification – this time within the query supporting the primary dataset, ProductData. Recall that we have already added query parameters for TimeYear, TimeMonth and LookBackSpan into the ProductData dataset. At this point, we have only to embed the parameters into the query syntax, which we will do in the steps that follow.

The dataset query appears, presenting the MDX syntax in Query view, as shown in Illustration 18.

 ([Date].[Fiscal].[Month].ALLMEMBERS

SELECT NON EMPTY { [Measures].[Internet
Order Quantity], [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY {  (LASTPERIODS(@LookBackSpan,
STRTOMEMBER(@TimeMonth, CONSTRAINED)) * [Sales Reason].[Sales
Reason].[Sales Reason].ALLMEMBERS * [Sales Territory].[Sales Territory
Group].[Sales Territory Group].ALLMEMBERS ) } DIMENSION PROPERTIES
MEMBER_CAPTION, MEMBER_UNIQUE_NAME, [Sales Reason].[Sales Reason].[Sales
Reason].KEY, [Sales Territory].[Sales Territory Group].[Sales Territory
Group].KEY ON ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, BACK_COLOR,
FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The dataset query appears, with our substitution (circled, once again), within the Query pane as partially shown in Illustration 20.

The query executes, and the data grid again becomes populated, as partially depicted in Illustration 21.

An examination of the query and the data it returns reveals the presence of data for six months (February 2004 through July 2004) which equates to the default “look back” value we have supplied within the query parameter we instituted for LookBackSpan earlier. From the perspective of the Data tab, therefore, our query appears to be performing as expected. We will verify performance of this and other items with which we have worked in the next section, where we will conclude our practice exercise.

Verification: Preview the Report and Inspect the Effectiveness of Our Modifications and Enhancements

Let’s preview the report to inspect the results of our handiwork.

DBJ_OLAP_Report.rdl initializes, and the Year prompt becomes enabled.

We note that the Year parameter is once again displaying the ParameterCaptionIndented Label field - something we had already changed to the standard ParameterCaption Label field within the Report Parameters dialog for the respective parameter TimeYear earlier. It is, perhaps, useful to note, at this stage, that the Label field has reset itself to this, the Reporting Services default, when we “declared” the TimeYear query parameter within the TimeMonth dataset, as a part of making the latter cascade properly, based upon the selection we made within the TimeYear picklist. To restore the standard ParameterCaption Label field, we can simply return to the Report Parameters dialog for the TimeYear parameter, and reselect ParameterCaption via the Label field selector, as shown in Illustration 22.

We note that only two months appear within the Month parameter picklist - the only populated member months of FY 2005 within the Adventure Works sample cube. Let's try changing the Year at this point to demonstrate further the fact the cascading is working effectively:

Once we make a selection within the Year dropdown selector, the next parameter within the cascading chain, Month, becomes enabled, once again.

We note that twelve months appear within the Month parameter picklist - the only populated member months of FY 2004 within the Adventure Works sample cube. We can easily see that the Month parameter picklist cascades appropriately, based upon the selection we make in the Year parameter picklist.

Let's verify the operation of the “look back” capability we have added via the No. Months to Report parameter. As we can see, the default of “6” appears in the selector.

The report executes, and displays its output within six columns, ranging between June 2004 and January 2004, as shown in Illustration 24.

Let's perform another quick test of the parameterized “look back” capability.

The report executes, displaying its output, this time, within three columns, ranging between June 2004 and April 2004, as depicted in Illustration 25.

We therefore see that the “look back” parameter we have put into place, No. Months to Report, accomplishes the intended ends, and enables us to meet the business need expressed by the information consumers. More generally, we can easily see that the Reporting Services 2005 environment, with its graphical design environment, supports easy and flexible design of innovative parameters, which can be based upon a sizable inventory of MDX functions and operators, among other options. Further, we have seen how we can design parameters to cascade, and to otherwise interact in an intuitive, user-friendly fashion. While we might have gone significantly further in designing function-based defaults for cascading parameter picklists, or even more elaborate, dynamically adjusting “look back” ranges, based upon dates we selected, and a host of other possibilities, we assure our client colleagues that, once they understand the basics, we can certainly help them to further enjoy the myriad more advanced capabilities that are exposed within the current version of Reporting Services.

Conclusion ...

In this article, we concluded another extended examination, which we began in Mastering OLAP Reports: Parameterizing Number of “Look Back” Periods with the MDX LastPeriods() Function, Part I, of parameters in Reporting Services 2005. We continued toward our primary objective of getting hands-on exposure to parameterizing LastPeriods() within the sample OLAP report that we cloned in the first half of the article, where we began our practice session by setting up a scenario within which to work with a basic OLAP report in exposing the steps involved.

We picked up, in this article, with the clone of an existing sample report, containing a matrix data region, which we had created, and structurally modified further for our practice session, in Part I. We also briefly recounted an overview of the MDX LastPeriods() function we had performed in the first half of this article, discussing details of the use we intended for the function to support the stated reporting needs of a hypothetical client. In so doing, we touched upon general concepts surrounding the parameterization of MDX functions in general, and the LastPeriods() function specifically.

In this, the second half of our article, we continued our practice session by reviewing and ensuring the adequacy of the datasets (automatically created when we added the required query parameters to support date and function parameterization in Part I) to support report parameters and meet the stated business requirements. We next added syntax to the Month dataset query to enforce cascading, based upon the selection made for the Year parameter by an information consumer at runtime. Finally, we leveraged the MDX LastPeriods() function, containing index and Month parameter placeholders, to provide the “look back” capability requested by our client. At appropriate junctures throughout both Part I and Part II of this article, we discussed the interaction between the various components in supporting the runtime parameter that the end consumer sees, as well as discussing the results obtained with the development techniques that we exploited.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.