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

Parameterization of MDX functions is one of those things that cannot be delivered solely via graphical MDX Editor – but then, you really didn’t think that you could avoid actually touching MDX at all, did you? BI Architect Bill Pearson demonstrates a way to parameterize 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

In recent articles within this series, we have focused 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.

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 first exposure to the concepts of parameterization was in working with very early versions of Cognos Impromptu. My continued application of those concepts over the succeeding years within Cognos, Crystal, Business Objects, MicroStrategy, and several 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’s easy to see 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. Indeed, 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 practitioner 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 various articles of this series, 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 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 will become noticeable later in our article.

In this article, we will get hands-on exposure to parameterizing an MDX function, LastPeriods(), within a preexisting sample OLAP report. Beginning with the general concepts, we will continue into a practice session where we set up a scenario, within which we work with a basic OLAP report, to expose the steps involved. In examining the rudiments of specific function parameterization within an OLAP report containing a matrix data region, we will:

• Open the sample Report Server project, AdventureWorks Sample Reports, and ascertain connectivity of its shared Analysis Services data source;
• Create a clone of an existing sample report, containing a matrix data region, with which to perform our practice exercise;
• Make structural modifications to the clone report, to prepare for our practice exercise session with parameters within a matrix data region;
• Perform a brief overview of the MDX LastPeriods() function, which we will use to support the stated reporting needs of a hypothetical client;
• Discuss the parameterization of MDX functions in general, and the LastPeriods() function specifically;
• Add the required query parameters to support date and function parameterization;
• Ensure the adequacy of automatically created datasets to support report parameters and meet business requirements (in Part II of this article);
• Add syntax to the TimeMonth dataset query to enforce cascading (in Part II of this article);
• Leverage the MDX LastPeriods() function, containing the month parameter placeholder(in Part II of this article);
• Discuss the interaction of the various components in supporting the runtime parameters that the end consumer sees (throughout Part I and Part II of this article);
• Discuss the results obtained with the development techniques that we exploit (throughout Part I and Part II of this article).

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.

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the newly required parameter. The focus of our efforts will be the addition of this parameter into an OLAP report containing a matrix data region (the mechanics behind adding the capability, not the design of the report itself). To save time, we will be working with a simple, pre-existing sample report – in reality, the business environment will typically require more sophistication. The process of setting up the basic parameter is the same in real world scenarios, with perhaps a more complicated set of underlying considerations. (I virtually never encounter a client reporting requirement that does not involve at least basic parameterization.)

We will perform our practice session from inside the MSSQL Server Business Intelligence Development Studio. For more exposure to the Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my Database Journal series Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to keep to the focus of the article more efficiently.

Preparation: Create a Clone Report within the Reporting Services Development Environment

For purposes of our practice session, we will create a copy of the Sales Reason Comparisons report, one of several samples that are available with (and installable separately from) the MSSQL Server 2005 integrated business intelligence suite. Making preparatory modifications, and then making the enhancements to the report to add the functionality that forms the subject of our lesson, can be done easily within the Business Intelligence Studio environment. Working with a copy of the report will allow us the luxury of freely exploring our options, and will leave us with a working example of the specific approach we took, to which we can refer in our individual business environments.

Open the Sample Report Server Project

For purposes of our practice session, we will open the AdventureWorks Sample Reports project, which contains the sample reports that ship with the Reporting Services component of the MSSQL Server 2005 suite. We will complete our practice session within the sample project so as to save the time required to set up a development environment from scratch within the Business Intelligence Development Studio.

To open the AdventureWorks Sample Reports project, please see the following procedure in the References section of my articles index:

Ascertain Connectivity of the Shared Data Source

Let’s ensure we have a working data source. Many of us will be running “side-by-side” installations of MSSQL Server 2000 and MSSQL Server 2005. This means that our installation of the latter will need to be referenced as a server / instance combination, versus a server name alone. (The default for the Adventure Works DW project sample’s connection is localhost, which will not work correctly in such a side-by-side installation, as MSSQL Server 2000 will have assumed the identity of the local PC by default.)

If you do not know how to ascertain or modify connectivity of the Analysis Services data source, please perform the steps of the following procedure in the References section of my articles index:

Create a Copy of the Sales Reason Comparisons Report

We will begin with a copy of the Reporting Services 2005 Sales Reason Comparisons OLAP report, which we will use for our practice exercise. Creating a “clone” of the project means we can make changes to select contents (perhaps as a part of later exploration with our independent solution), while retaining the original sample in a pristine state for other purposes, such as using it to accompany relevant sections of the Books Online, and other documentation, as a part of learning more about Reporting Services (particularly an OLAP report using an Analysis Services data source), and other components of the Microsoft integrated business intelligence solution in general.

If you do not know how to create a copy of an existing report, please perform the steps of the following procedure in the References section of my articles index:

We now have a clone OLAP report file within our Reporting Services 2005 Project, with which we can proceed in the next section to make modifications for our subsequent practice session.

Preparation: Modify the OLAP Report for Use within Our Practice Session

We will next make a few modifications to prepare the report for our practice session. Our objective will be to begin the session with a simple OLAP report that contains no parameters. Let’s open the report in Layout view (for those of us not already there) and make the necessary settings to place it into a state upon which we can commence our practice steps.

DBJ_OLAP_Report.rdl opens in Layout view.

We will start with the Data tab.

We enter the Data tab, where we will remove a handful of components that we do not need for our practice session. We will accomplish this from the perspective of the MDX Query Builder, the main components of which (in Design mode) are labeled in Illustration 2 below.

The primary (and sole remaining) dataset, ProductData opens.

We will make an addition to the Dataset next.

The new Month column appears, as desired. Having made the necessary changes on the Data tab, we are ready to move to the Layout tab, where we can conclude our preparatory modifications to the report file.

The gray column and row bars disappear, as a light, patterned outline forms around the matrix data region, and the context menu appears.

The Matrix Properties dialog appears, defaulted to the General tab.

The Grouping and Sorting Properties dialog appears, defaulted to its General tab.

matrix1_Month

#,###

Here we are simply changing the existing format to one more appropriate for a count value. The Format code appears on the Format tab of the Textbox Properties dialog as shown in Illustration 16.

We next need to change the column heading associated with the Group we modified earlier, so that it reflects Months, versus the previous grouping criteria of Sales Territory Group.

The Expression Editor opens.

=Fields!Month.Value

Recall that the intent is to label the column to reflect the nature of the Group we have put in place: Months instead of Sales Territory Group.

All that remains to complete our preparatory steps is to remove the pre-existing Report Parameter. Recall that one of our earlier steps was to remove the Product entry from the Filter pane (the sample report from which our report was cloned came equipped with a parameterized Product filter). This entry had been established with the Parameter checkbox selected, a common way to create a parameterized filter, as we have seen in various other articles of the MSSQL Server Reporting Services series. Because parameterization was selected for the Product filter, it follows that a Report Parameter might well have been created at the time the entry was made. We will now remove the remaining Report Parameter.

The Report Parameters dialog opens, and we see a single Report Parameter listed.

We will execute the report to ascertain that our modifications are complete, and that we have a working report for the practice session that follows.

The Report is being generated message briefly appears, and then the report displays. The modified report appears as partially depicted in Illustration 23.

We note that the fiscal months July 2001 through July 2004 appear (representing a total of 37 months, the earliest of which are relatively sparse with regard to data).

Our report is now ready for the practice exercise, which we will begin in the next section.

Procedure: Adding and Parameterizing the Date Dimension within the LastPeriods() Function in Reporting Services 2005

As we noted earlier, our intent within this article is to examine the detailed mechanics involved in adding – and parameterizing – a single MDX function, LastPeriods() within our report. Moreover, we will parameterize the Date dimension itself (both Fiscal 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 the last month with activity within the cube) as the “starting point” from which they look back. We will accomplish the initial steps from the Data tab, to which we will return in the following steps.

We return to the Data tab, where this time we will ultimately replace some of the syntax that currently exists within the MDX query - syntax which has, up until now, been assembled graphically via the MDX query designer in Design mode.

As most of us know, when we create a dataset from an Analysis Services data source, Report Designer initially offers us the MDX query designer in Design mode. We can work within Design mode to interactively build an MDX query using graphical elements. Design mode affords us the capability to select a cube and to drag numerous objects to the Data pane, including:

• Dimensions
• Dimension Attributes
• Levels
• Hierarchies
• Measures
• Key Performance Indicators (KPIs).

We can also add calculated members, set default values for variables (we will expose a specific instance of this in our practice exercise, as part of the focus of our article), and automatically see result sets returned for the queries we build (particularly useful as we make changes within the Query pane) to the Data pane. We can set filters (like the one we removed in our earlier steps to prepare our OLAP report clone for use within our practice session), to limit the data retrieved from the data source by the query, and define parameters.

As many of us have no doubt already learned, the need for dealing with “direct MDX” has not disappeared. This can be particularly true when the need arises to tailor an MDX query to meet (especially more sophisticated) business requirements within Reporting Services. One example will be illustrated within the focus of our article – the use of any but the most basic (those that are implicit within the arrangement of objects within the Data pane of the MDX functions within the MDX query designer in Design mode) MDX functions. (We illustrate other examples where we must use the MDX query designer in Query mode in numerous other articles of the MSSQL Server Reporting Services series.)

To view or edit MDX query syntax directly, we must switch to Query mode by clicking the Design Mode toggle button on the toolbar. The Design Mode toggle button appears within the Report Designer as shown in Illustration 24.

The syntax for the underlying query appears, as depicted in Illustration 25.

What we need to do, to meet the requirements of our client colleagues, includes the following (in the order specified):

• Add a query parameter for each of Year, Month, and Span of Lookback;
• Ensure that the datasets automatically created for each of the newly added query parameters are appropriate, adjusting as necessary to support report parameters in meeting the business requirement;
• Add the syntax to the query to leverage the MDX LastPeriods() function, which will enclose the parameterized input, ultimately, of the three query parameters, to achieve the retrieval / presentation dictated by the selections of information consumers at runtime.

We will accomplish these steps within the following subsections.

Add the Required Query Parameters to Support Date and Function Parameterization

The Query Parameters dialog appears as depicted in Illustration 27.

Here we add the three “variables” we will be associating, via the query syntax, with the report.

TimeYear

The row within which we have been working in the Query Parameters dialog appears as shown in Illustration 33.

Next, let’s add a query parameter for Month.

TimeMonth

Next, we will add a query parameter for the span of desired "look back" months (for purposes of our illustration, we will select six months – keeping in mind that the default here, in effect, serves only to provide a value to substitute for the associated query parameter value at run time (as well as for the associated report parameter default). This parameter will have some different settings than the more “typical” settings above – including blanks in a couple of places (a fact that is not well documented ...).

LookBackSpan

6

The third row, within which we have been working in the Query Parameters dialog, appears as depicted in Illustration 36.

We are returned to the Data tab, where we will make some modifications to the query syntax in the section that follows. Before doing so, let’s quickly confirm some events that have transpired behind the scenes, as a part of our creating the three query parameters in the preceding steps of this section.

We note the appearance of the three new query parameters within the Parameters pane of the Report Parameters dialog, which appears next, as depicted in Illustration 38.

We note that only the ProductData selection appears within the selector, as shown in Illustration 39.

We note that the TimeYear and TimeMonth datasets (automatically created when we moved to the Layout tab) join the ProductData selection within the selector, as depicted in Illustration 40.

The datasets we see have been automatically created to support parameters that we have defined with simple datasets that we may or may not choose to use “out of the box.” (See the various articles I have published surrounding parameters in Reporting Services 2005, within my MSSQL Server Reporting Services series at Database Journal.

One might wonder why the third query parameter we created, LookBackSpan, did not trigger the creation of a third dataset. Because the simple parameter is based upon a mere number, in this case, and not upon a dimensional construct, no basis exists within the “auto create” capability upon which to build the dataset. We will, of course, be able to manage this via another avenue, as we shall see in a later section in Part II. Our first step in Part II, will be to overview the two datasets that were created, and ensure their adequacy to support the associated date-related report parameters in meeting the business requirements of our client colleagues. We will then conclude by working with underlying dataset queries to enforce cascading, and to support the parameterized “look back” capability that is the primary focus of this article.

NOTE: Please consider saving the .rdl file we have created to this point for use

in the article that follows, so as to avoid the need to repeat the preparation process we have undertaken above.

Conclusion ...

In this article, we began another extended examination of Parameters in Reporting Services 2005. This time, we set out to obtain some hands-on exposure to parameterizing an MDX function, LastPeriods(), within a preexisting sample OLAP report. Beginning with the general concepts, we continued into a practice session where we set up a scenario within which we work with a basic OLAP report, to expose the steps involved. In examining the rudiments of specific function parameterization within an OLAP report containing a matrix data region, we performed a brief overview of the MDX LastPeriods() function, which we stated that we would use to support a stated reporting need of a hypothetical client. We discussed the parameterization of MDX functions in general, and the LastPeriods() function specifically.

We next prepared a copy of a sample report sourced from Analysis Services, containing a Matrix data region, with which to perform our practice exercise. We then added the required query parameters to support date and function parameterization, which we stated we would complete in Part II of this article. Throughout our various preparation and other steps, we discussed the interaction of the various components in supporting the runtime parameters that the end consumer sees, as well as touching upon the results obtained within 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.