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

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.