Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II - Page 6

Procedure: Adding Cascading Parameters in Reporting Services 2005

As we noted earlier, our intent within this article is to begin our examination of Cascading Parameters by taking the simplest approach: we will add Parameters using the Query Builder interface in Design Mode. We will parameterize hierarchical levels of the Date dimension, so as to be able to easily verify, with no ambiguity, that the resulting cascading parameters work effectively – that they present the appropriate child members based upon the selection made in the parameter preceding the level to which they belong. Moreover, we will examine the internal processes that take place “behind the scenes.”

In other articles of this series, we add Parameters via more manual, less “visually direct” channels (some of the steps of which will take place within the Query view versus the Design view), for various reasons that become apparent within the scenarios we encounter there. We compare and contrast methods throughout the related articles, where relevant, introducing, within each, new concepts upon which we can build more sophisticated parameterization support into our organizations’ reports.

Add Cascading Parameters through the Query Builder Interface

As we stated in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, and elsewhere within this, and other of my, series’, the Design Mode button in the toolbar of the Data tab allows us to easily shift between Design view and Query view, when working with our OLAP queries. The idea, obviously, is to provide those who are not at ease with direct MDX a means of creating queries within a graphical interface.

We have produced the core dataset, and are ready to build the cascading, date-related prompts that will meet our objectives. We will create our rudimentary cascading parameter chain within Design view, by taking the following steps:

We enter the Data view, as we did earlier. To state what is probably obvious, we can tell that we are in Design view, primarily because we see the column headings for our query components in the Data grid, and we do not see MDX syntax. We also see that the Design Mode button is depressed, as shown in Illustration 36.

We have returned to add three “chained” filters (which we will parameterize): one each for Calendar Year, Calendar Quarter and Calendar Month.

While, as we shall see, we might simply drag the hierarchical levels that we need to the Filter pane, it is, in my opinion, easier to work directly with the pane selectors. (We have expanded the Date.Calendar hierarchy more as a visual reference, in this case.)

Our completed Filter-with-Parameter entry appears in the top row of the Filter pane, as shown in Illustration 38.

We have successfully added a new Parameter, in conjunction with a filter, within the Dataset underlying our report. The addition of the Parameter within the graphical interface, as we shall see, has already triggered the automatic creation of a Report Parameter. A supporting Dataset will also be created, as soon as we move to the Layout tab. But first, we need to add entries within the Filter pane for the additional filters within the chain.

We recall that we 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, the presence of a new Report Parameter, called DateCalendarYear. This Report Parameter was created automatically when we designated our new row within the Filter pane as a Parameter (via the enabling checkbox).

Another important observation we might make is that the From query setting within the Available values section of the dialog is active (the radio button is selected), and that the setting references a Dataset (and the Value and Label fields therein) with the same name as the new Report Parameter - a Dataset, we will discover, that has also been automatically created. The purpose of this Dataset is to support the selection picklist for the new Report Parameter.

Finally, we note that a couple of settings have been set for us: in the Prompt section (within the upper third of the dialog) of the Report Parameters dialog, the Multi-value setting is checked. While we might certainly leave the setting in place, we will disable it for the purposes of our practice session.

In addition, in the Default values section (in the lower third of the dialog), we find that the radio button for Null has been preselected. We will leave the latter preselected setting (“Null”) in place; the disablement of the Allow null value setting in the Prompt section effectively means that, although the parameter selector defaults to “blank” at runtime, the information consumer will be forced to make a selection before he / she can proceed further with executing the report. This arrangement works well in many environments, particularly where default settings tend to be overlooked, at times, and the report run with unintended constraints - obviously settings should be adjusted to accommodate local requirements and realities.

Calendar Year:

The Report Parameters dialog appears, with our adjustments, as depicted in Illustration 39.

The report runs, and then displays a drop-down parameter prompt labeled Calendar Year, the selector box of which, by default, contains the placeholder <Select a Value>, as shown in Illustration 40.

The report runs again, and returns for the Date level groupings, for Calendar Year 2003, as expected.

We will now return to the Data tab, to examine the Dataset which has been automatically created, to populate the picklist in support of the new Report Parameter.

The Dataset loads, and presents the MDX syntax in Query view. This is the query that has been automatically created to generate the picklist to support the new DateCalendarYear Report Parameter (we saw the DateCalendarYear Dataset referenced within the Dataset selector, where columns from the new Dataset were assigned to the Value field and Label field selectors, within the Available values section of the dialog. As we have noted, the Dataset was created automatically, after the Report Parameter was itself created, and connected to its underlying Dataset via the three settings we described, which were also set automatically by Reporting Services.

As we noted in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, the automatically MDX generated query creates additional fields, in addition to retrieving data from the cube, whose sole purpose is support of the parameter picklist, from which information consumers make the selections at runtime. These fields are:

• ParameterCaption
• ParameterValue
• ParameterLevel

All three fields are products of calculated members created via MDX syntax (using the WITH MEMBER keyword combination). As we discover within other articles of the series, there are multiple ways to approach picklist support (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 settings within the Report Parameter dialog reference the ParameterValue and ParameterCaption fields, respectively, within this Dataset.

Having examined various details about the objects that are created to support a parameterized filter, we will return to the Data tab to create the remaining two members of the cascading parameters chain.

The dropdown selector is again enabled.

We will take a small detour, at this point, to confirm our understanding as to the timing of Report Parameters creation, based upon our activities within the Filter pane.

The Report Parameters dialog opens, revealing only the single Report Parameter that we saw earlier, DateCalendarYear.

The Report Parameter dialog opens, this time revealing two Report Parameters. We see that the DateCalendarYear parameter that existed before has been joined by the DateCalendarQuarterofYear parameter. It thus becomes evident that the act of checking the Parameters checkbox in the respective Filter pane row triggers the creation of the associated Report Parameter.

Calendar Quarter:

The Report Parameters dialog - with the DateCalendarQuarterofYear parameter selected - appears, with our adjustments, as shown in Illustration 42.

Our completed entry appears in the second row of the Filter pane.

An examination of the Dataset selection picklist reveals only the ProductData and DateCalendarYear Datasets. In this manner, we can verify that, although the creation of the Report Parameter, complete with internal references to a supporting Dataset, is triggered by our placing a checkmark in the Parameters setting of the respective Filter pane row, the Dataset itself is not physically created until we leave the Data tab (as we did in our previous example, when we clicked the Layout tab to transit to the Layout view).

We will now create the final parameterized filter within the Filter pane.

The Filter pane, with all three entries, appears as depicted in Illustration 43.

The Report Parameters dialog opens, this time revealing three Report Parameters.

Calendar Month:

The Report Parameters dialog - with the DateMonthofYear parameter selected - appears, with our adjustments, as shown in Illustration 44.

An examination of the Dataset selection picklist reveals only the ProductData and DateCalendarYear Datasets, as we left them earlier.

Based upon our earlier discussion, we see the behavior we have expected. All four Datasets, including the primary Dataset and the three we have added via the enactment of parameterized filters within the Filter pane (and subsequent departure from the Data tab) now appear, as depicted in Illustration 45.

We have successfully added our cascading parameters, based upon filters we added within the Filter pane of the primary Dataset underlying our report. The addition of the parameters within the graphical interface, as we have verified, triggered the automatic creation of corresponding Report Parameters. Supporting datasets were also created, as soon as we moved away from the Data tab. The timing of the automatic creation of objects can thus be understood; the sequencing becomes very important in the event that we need to modify or remove any of the objects after they are initially created and aligned.

We are now ready to verify the operation of the cascading parameters we have installed within the OLAP report.