Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II - Page 3
June 27, 2008
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
Lets 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. Lets return to the Report Parameters dialog to inspect the resulting relationship further.
1. Select Report -> Report Parameters from the main menu, once again.
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.
2. Ensure that the TimeYear parameter is selected within the Parameters pane on the left side of the Report Parameters dialog.
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].& ), 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.)
3. Click OK to dismiss the Report Parameters dialog for the present time.
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.)
4. Click the Data tab once again.
5. Click the downward selector arrow on the right side of the Dataset selector.
6. Select the new TimeYear dataset that appears, as shown in Illustration 1.
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.
7. Click the Execute Query button in the toolbar, as depicted in Illustration 2.
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:
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.
NOTE: While we will discuss MDX functions, expressions, queries, and related subject matter throughout the MSSQL Server Reporting Services series, please refer to the articles of my MDX Essentials series, whose member articles are published monthly here at Database Journal, for detailed information about MDX, as well as MDX keywords, functions, and other considerations.
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:
8. Click the Edit Selected Dataset (...) button to the right of the Dataset selector (currently containing TimeYear), as depicted in Illustration 4.
The Dataset dialog appears, defaulted to the Query tab, as shown in Illustration 5.
9. Click the Filters tab on the Dataset dialog.
10. Click the Expression box in the top row of the table labeled Filters on the Filters tab, to activate its downward pointing selector arrow.
11. Using the selector, select the =Fields!ParameterLevel.Value option (at the bottom of the list), as depicted in Illustration 6.
12. Ensure that an equals ( = ) sign appears in the Operator column, to the right of the Expression column, within the same row of the Filters table.
13. Type the following into the Value column, to the right of the Operator column, within the same row:
The Dataset dialog, along with our settings, appears as shown in Illustration 7.
14. Click OK to accept our input, and to close this dialog.