Reporting Services: Customize Automatically Created Parameter Support Objects

Join BI Architect Bill Pearson as he continues his examination of parameterization within Analysis Services reports. In this article, we create basic report parameters using the graphical user interface, and then customize automatically created support objects to remove the “All” selection that appears in picklists by default.

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.

Introduction

As we learned in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, a common enterprise reporting requirement is the capability to filter reports at run time for specific information. This is typically managed via parameterization, also known as “prompting”, where the filter criteria is requested (and hence the consumer is “prompted”) when the report is run. Depending upon the parameter type (the most common are type-in and picklist), the filters are typically enacted when the consumer types or selects a value, or a series of values.

Type-in parameters accept directly typed user input for the values upon which the report is based. Alternatively, the picklist presents a selection of choices to a consumer based upon a static file, a dataset from a larger data source, or through other means. The picklist is often the tool of choice, because of its inherent elimination of typing errors, as well as the enforcement of standard selections. A well-constructed picklist makes selection easy for the consumer, who is not often pleased with a long scrolling process, or other cumbersome method, as the initial step in generating a commonly requested report.

Over the years, I have implemented parameters in many ways to fit many client business needs, ranging from the most basic picklist prompts to sophisticated prompts that “push the envelope” with regard to going beyond out-of-the-box uses for these components. As I discussed in detail in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, one of the most impressive enhancements introduced with Reporting Services 2005 is its capability to automatically create the complete “chain” of components, including filter, report parameter, and supporting dataset(s) we need to quickly add basic parameterized filters to our reports. While the automatically created objects serve us well with minimal modification in many cases, we often have to “tweak” the components supporting parameterization a bit to obtain more sophisticated features.

Another important consideration during the design phase of any implementation effort, too, is that, while we can still manage much customization within the reporting / presentation layer of our integrated business intelligence solution, we also have multiple options for placing the intelligence behind parameterization (as well as other functionality) in other layers of the solution. For example, I have often supported picklist parameterization with support objects I have created within the Analysis Services cube that is used as a data source for the reports under consideration, and so forth. (For an example of implementing support for a hierarchical picklist in this manner, see my Database Journal article Create a Cube-Based Hierarchical Picklist.) When we consider the fact that we have many options for “where to install the intelligence” within the multi-layered BI solution, we can begin to see that a grasp of all layers is critical. For a general summary of my opinions surrounding the importance of thinking "multi-dimensionally" within the design and implementation of a business intelligence system, see Multi-Layered Business Intelligence Solutions ... Require Multi-Layered Architects.

In this article, we will discuss the general concepts, and then set up a scenario within which we work with a basic OLAP report, to expose the steps involved in meeting a basic need of a hypothetical client in modifying the default behavior of automatically created parameter picklists to display an “All” option at runtime. We will review the mechanics that underlie the automatic creation of parameter support objects by Reporting Services, as well as touching upon potential issues that might arise when we modify elements of those objects once they are created. In examining the requested addition and customization of parameters 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 the customization of Parameter Picklists within our report;
• Create, within the graphical Design Mode of the MDX Query Builder, multiple filters for which parameterization is enabled via the Filter pane setting;
• Examine the automatically created Report Parameters and their settings;
• Review the automatically created Datasets underlying the new Report Parameters;
• Discuss how the various components are tied together, and potential challenges we face in modifying these objects without consideration of the resulting dependencies;
• Make modifications to remove the “All” selection that appears by default in our newly created parameters’ picklists at runtime;
• Preview the report to observe the effectiveness of our solution, as well as the cascading nature of the parameters created, in runtime action.

Customizing Auto-Created Parameter Support Objects in Reporting Services 2005

Objective and Business Scenario

As we have emphasized in earlier articles, the reporting / presentation layer of the integrated Microsoft business intelligence solution is often the only point of interaction with corporate information stores for organizational information consumers. Parameterization not only makes this interface more user-friendly, but it affords the tandem benefit of enabling knowledgeable report authors to leverage single reports for multiple uses, delivering “more for less” from a development and maintenance perspective.

We introduced parameterization in general, discussing the challenges that faced many of us within the Reporting Services 2005 environment, in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I. We continued our general discussion of parameterization in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, where we explored the underlying mechanics that support parameters, focusing particularly upon several events that take place in conjunction with parameter creation in the design environment. We further extended our Part I examination of the interaction among the various components that comprise and support run-time parameterization, and focused, in Part II, upon the actions that Reporting Services takes behind the scenes in our creation of cascading parameters.

In this two-part article, we continue the extended exmination of Parameters in Reporting Services 2005, concentrating upon a couple of specific refinements we can make to our parameters to “fine tune” them to local business environments. We will begin, once again, by adding simple filter / parameter combinations to a basic sample OLAP report, containing a Matrix data region, via the graphical interface of the MDX Query Builder. We will review, as part of our preparation of the sample report for our practice session, the objects that Reporting Services automatically creates, in conjunction with our additions, to support runtime parameterization. We will then delve into the modifications we need to make to meet the requirements of a hypothetical client, the AdventureWorks organization.

To detail the specifics, we have been contacted by a team with which we consult regularly, the Adventure Works Customer Care department, whose work centers around analyzing and reporting upon customer satisfaction indicators, among various other information, in support of the overall organization in accomplishing its objectives. Our client colleagues inform us that information consumers have asked that they refine an existing OLAP report, Sales Reason Comparisons, to make the existing parameterization a bit more user friendly. First, they wish to be able to select Year, Quarter and Month for which the report is run (the initial report, a sample authored by an intermediate-level employee who has since returned to his home country, presents data for “all time,” allowing users the capability of selecting Product Class(es) only at runtime). Moreover, the team wants a brief overview of the mechanics involved in the automatic creation of the support objects that occurs as we create the new parameterized filters, primarily so that they can understand the relationships between these components to allow for ongoing maintenance.

Because the client representatives have worked with date-related parameterization in other reports and systems, they already know that, in addition to merely being able to specify dates through standard picklists, they want to request a couple of enhancements to the simple prompts they have seen thus far in reports with similar runtime parameters. First, they have noted in several OLAP reports that, even though the authors did not design the reports to provide multi-value selection in the picklists (something they so not want in the report under consideration, either), an “All Periods” selection appears in each of the date selectors (say, for Year, Quarter, Month, etc.), which confuses some consumers (reporting requirements will be restricted to single selections within each level of the Date dimension to meet the current, simple needs of the group). Moreover, the group tells us that they want the prompts to reflect “intelligent,” intuitive defaults, and not just arbitrary, hard-coded values or the “<Select a value>” label that might otherwise appear, if no defaults at all are in place.

We confirm our understanding of the foregoing needs, and propose to first add the requested date-related parameters, and then to eliminate the “All Periods” selection from the respective picklists entirely. Having accomplished this, we will construct working examples, in the second half of this article, of ways to provide the intuitive defaults that our colleagues have requested for the new report parameters – examples, we add, that can be modified to specify different defaults, should the consumers request them after “road testing” the initial labels.

Preparation

Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement the solutions that we propose to meet our client’s requirements. The focus of our efforts in this, the first half of this article, will be the elimination of the “All” level we have described earlier. In Part II of this article we will examine the addition of intuitive parameter defaults for the same parameters. Because of time limitations, 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 basic parameterization is the same in real world scenarios, with perhaps a more complex 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. We will examine a solution for the elimination of the default “All” selections within the parameter picklists, then, within the first half of this article, and position our report for the addition of intelligent parameter defaults in the second half.

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 Development 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 report 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. We can, therefore, use the original 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 basic parameterized filters as required by our client colleagues. 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 from which we can commence our “from scratch” practice steps.

We will begin by adding additional parameters to our sample report. These parameters will initially be of a standard, basic picklist variety. We will then work with these parameters as we explore our options for eliminating a characteristic default behavior of their respective picklists (in this half of our article), and for adding intuitive parameter defaults to our report (in the second half of our article).

Add Additional Parameters through the Query Builder Interface and Review the Automatic Creation of Support Objects

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 created the basic report with the core datasets, and are ready to add three rudimentary, date-related prompts that will meet our objectives. We will create these prompts within Design view, by taking the following steps:

We enter the Data view, which defaults to the presentation of the first of two pre-existing datasets, ProductData. 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 depicted in Illustration 2.

We have returned to add three date-related filters (each of which we shall parameterize): one each for Calendar Year, Calendar Quarter and Calendar Month. Let’s first run the existing query to ascertain that all is in order with regard to the retrieval of data.

Data populates the Data pane, and appears similar to that depicted in Illustration 4.

Having ascertained that the core query is in working order, we will now add the date-related filters, as well as the “vanilla” parameters that we intend for them to support.

The Date dimension populates the Dimension column, within a newly created row of the Filter pane. The Calendar Year attribute hierarchy appears within the Hierarchy column, as well.

Our completed filter-with-parameter entry appears in the new row of the Filter pane, as depicted in Illustration 6.

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 begun a sequence of events that will trigger the automatic creation of a corresponding 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 Date filters we intend to add.

Let’s pause from our preparation steps and take this opportunity to view some of the “behind the scenes” construction that occurs as we progress our report.

We recall that a single Report Parameter for Product Category existed in the original report, before we began 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 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 a couple of other settings that have been made 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. For the time being, 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, even if 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, and, as we shall see in our practice session, we can accommodate intuitive defaults in conjunction with some modifications in this area.

Calendar Year

The Report Parameters dialog appears, with our adjustments, as shown in Illustration 7.

The report runs, and then displays a drop-down parameter prompt labeled Calendar Year, to the right of the pre-existing Product Category prompt. The selector box for Calendar Year, by default, contains the placeholder <Select a Value>, and, when it is expanded, via the downward pointing arrow on its right side, appears as depicted in Illustration 8.

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 discussed, the Dataset was created automatically, after which the Report Parameter was itself created and connected to its underlying Dataset via the three settings we described (which were also established automatically by Reporting Services).

As we noted in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, the automatically generated MDX query creates additional fields, in addition to retrieving data from the cube; the purpose of the group of fields is to support the parameter picklist, from which information consumers make 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 Date parameters that we will require for our practice session. While, as we saw above, we might simply move the hierarchical levels that we need to the Filter pane using the context menu – for that matter, we might drag them as well - it is sometimes easier to work directly with the pane selectors. (We can, of course, still refer to the expanded Date.Calendar hierarchy within the Metadata pane as a visual reference, in this case.)

We will add two more parameterized filters directly in the Filter pane next.

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 two Report Parameters (the original ProductCategory parameter, along with the DateCalendarYear parameter we created in the immediately preceding steps) that we saw earlier,

The Report Parameter dialog opens, this time revealing three Report Parameters. We see that the ProductCategory and DateCalendarYear parameters that existed before have 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 depicted in Illustration 10.

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

An examination of the Dataset selection picklist reveals only the pre-existing ProductData and ProductList, together with the newly added 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 fully instantiated 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 four entries, appears as shown in Illustration 11.

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

Month

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

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

Based upon our earlier discussion, we see the behavior we have expected. All five Datasets, including the originally existing two Datasets 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 13.

We have successfully added three rudimentary 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.

Let’s perform a quick test to see that all is operating as expected in our sample report, before venturing into the practice session that follows, where we eliminate the “All Periods” selection within the date-related picklists. In the meantime, we will be setting up the environment for the second half of our article, where we will establish intuitive run-time defaults within our newly added date parameters.

DBJ_OLAP_Report.rdl initializes, and the first prompt (based upon the pre-existing ProductCategory parameter), becomes enabled.

We notice the “All Periods” selection that appears atop each of the date–related picklists we have created. This is the “All” option that we will eliminate within the practice session, as one of our objectives in meeting the expressed needs of our client colleagues.

Once we make a choice within the Calendar Year dropdown selector, the next parameter selector, Calendar Quarter, becomes enabled.

The report executes quickly and returns the data for the selections we have made within our parameter picklists, in a manner similar to that depicted in Illustration 15.

Having obtained assurance that the sample report operates as expected, we are ready to proceed with its use in our practice session, which follows below.

Practice

Having added the requested date-related parameters in the foregoing Preparation section, we are ready to begin making modifications to the client’s existing report to meet the two requirements they have communicated. We will first eliminate the “All Periods” selection that appears within the picklists for the new parameters at runtime (the “focus procedure” in this, the first half of our article). Then, in Part II of this article, we will begin the construction of example intuitive defaults for the new report parameters, to provide illustrations of ways our client colleagues might approach similar reporting needs.

Procedure: Removing the “All” Selection from Auto-Created Parameter Picklists in Reporting Services 2005

We noticed in our earlier “trial run” of the report (where our intent was to verify the operation of our newly added Date parameters) that an “All Periods” option appeared atop each picklist. While I have had myriad clients state that they did not want this option to be available, the fact is that Reporting Services is delivering exactly what is requested via the datasets that were automatically created to support the newly added parameters. The dataset created to support each parameter simply does not filter, by default, the dimensional levels retrieved from the dataset and presented in the runtime picklist, which, as we shall see, means that the “All” level is retrieved among the members of the desired level.

The good news is that provision has been made, within the dataset, to entrain the level number as a data column. We can easily perform our own filtering via this column, as we will do in the following steps.

We arrive at the Data view, as we did earlier.

The MDX Query Builder opens, exposing the MDX that was created (to populate the Report Parameter supporting dataset for the new parameter) as an activity in the chain of events triggered by our addition of the parameterized filter for DateCalendarYear in the Preparation section above. Let’s retrieve the dataset to examine its composition.

The column upon which we will focus is the ParameterLevel column. This column contains the values of a calculated member, [Measures].[ParameterLevel], which represents the data retrieved from the AdventureWorks cube by the following expression: [Date].[Calendar Year].CURRENTMEMBER.LEVEL.ORDINAL. This expression simply generates a number for the dimensional level at which the ParameterCaption (the label seen in the picklist by the information consumer at runtime) and the ParameterValue (the corresponding MDX value passed by Reporting Services as filter criteria in generating the data selected in the report at runtime) belong.

To eliminate the “All Periods” selection from our Year parameter picklist, we have only to implant a dataset filter at this stage.

The multi-tabbed Dataset dialog for the DateCalendarYear dataset opens.

=1

The Filters tab of the Dataset dialog appears, with our input, as shown in Illustration 20.

The dialog closes, and we return to the MDX Query Builder. At this point, we should note that, should we execute the query once again, we will see no difference in the dataset that is returned: the filter is not applied at this level, although it is enforced when we run the report, as we shall see a few steps below.

The Query Parameters dialog appears. It is here that we provide a default value for the Year parameter, to enable the query to execute. In effect, passage of the Year parameter value, at execution time of the Quarter dataset, is the mechanism behind the cascading nature of the parameter picklists.

Data populates the Data pane, and appears similar to that shown in Illustration 22.

Here again we see that the “All” level value is zero (“0”). We will, therefore, filter the data supporting the corresponding parameter picklist to Level 1, as we did for the DateCalendarYear dataset earlier.

The Dataset dialog for the DateCalendarYear dataset opens.

=1

The dialog closes, and we return to the MDX Query Builder, once again. We are now ready to perform level filtering on the last of our date-related datasets, DateMonthofYear.

The Query Parameters dialog appears, as it did for the DateCalendarQuarterofYear dataset earlier. We will provide a default value for both the Month and Quarter parameters, at this point, to enable the query to execute.

The Query Parameters dialog appears, with our input, as that depicted in Illustration 23.

Data populates the Data pane, and appears similar to that shown in Illustration 24.

Once again we see that the “All” level value is zero (“0”). We will filter the data supporting the corresponding parameter picklist to Level 1, as we did for the DateCalendarYear and DateCalendarQuarterofYear datasets earlier.

=1

Having applied the filters required to the new parameters’ supporting datasets, we are now ready to verify the effectiveness of our solution, and to ascertain the removal of the “All Periods” option within the date-related parameters’ picklists at runtime.

Verification: Preview the Report and Inspect the Effectiveness of Our Solution

Let’s preview the report to inspect the results of our handiwork. (One of the reasons that I chose hierarchical time for the dimensional example with which we undertook our practice efforts is the unambiguous relationship between parent and child level members. The idea was to both demonstrate the means of eliminating the “All” selection within the parameter picklists, while providing the dual benefit of demonstrating the cascading nature of the picklists as a secondary advantage of taking the avenue we have taken.)

DBJ_OLAP_Report.rdl initializes, and the first prompt (based upon the pre-existing ProductCategory parameter), becomes enabled.

We notice the “All Periods” selection that previously appeared atop the Year parameter’s picklist has now disappeared. We also notice that, once we make a choice within the Calendar Year dropdown selector, the next parameter selector, Calendar Quarter, becomes enabled. The cascading nature of the newly added, date-related parameter set becomes obvious in this scenario, as the AdventureWorks cube contains data only through August, and the third quarter (CY Q3) of 2004, a fact that is reflected in the picklist of three Quarter selection options, as we shall see next.