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

June 27, 2008

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.