Reporting Services: Customize Automatically Created Parameter Support Objects

Wednesday Jan 16th 2008 by William Pearson
Share:

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:

Open the Sample Report Server Project

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:

Ascertain Connectivity of the Analysis Services Data Source

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:

Create a Copy of a Sample OLAP Report

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.

1.  Right-click DBJ_OLAP_Report.rdl (or your own choice of a similar report) in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 1, as necessary.


Illustration 1: Opening the New Report ...

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:

1.  Click the Data tab.

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.


Illustration 2: MDX Query Builder in Design Mode (Compressed View)

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.

2.  Click the Execute Query button atop the Data tab, as shown in Illustration 3.


Illustration 3: Click Execute Query to Retrieve Data with the Existing Query ...

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


Illustration 4: Initial Query Operates Successfully in a Quick Test

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.

3.  In the Metadata pane, expand the Date dimension, as required.

4.  Within the expanded Date dimension, expand the Calendar folder.

5.  Right-click the Calendar Year attribute hierarchy that appears within the expanded Calendar folder.

6.  Select Add to Filter within the context menu that appears, as shown in Illustration 5.


Illustration 5: Select Add to Filter in the Context Menu

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.

7.  Leave the Operator setting (to the immediate right of the Hierarchy column) at its default of “Equal.”

8.  Place a checkmark in the checkbox that appears in the Parameters setting (to the immediate right of the Filter Expression setting), the rightmost column in the new row of the Filter pane.

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


Illustration 6: The Filter Pane Reflects Our Addition of the Calendar Year Attribute Hierarchy

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.

NOTE: As we have noted in other articles, it is important to remember that, although the Report Parameter and its underlying Dataset are created automatically when we enable parameterization within the Filter pane of the MDX Query Builder (Design Mode), the removal of the “Parameter” checkmark within the associated row of the Filter pane, or even the deletion of the entire corresponding filter row, will not produce an opposite effect. The Report Parameter and the dataset will remain until they are manually removed. Moreover, the disablement of parameterization within the Filter pane, followed by re-enablement and / or recreation of the Filter pane entry will, unless we intervene before we re-enable / recreate the Parameter, result in the creation of a second Report Parameter.

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.

9.  Click the Layout tab to transit to Layout view.

10.  Select Report -> Report Parameters from the main menu.

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.

11.  Replace the default string that appears within the Prompt box, Date.Calendar Year, with the following, more user-friendly label:

Calendar Year

12.  Uncheck the checkbox labeled Multi-value.

13.  In the “Available Values” section (the middle section of the Report Parameters dialog), in the bottom of the three settings, Label field, modify the setting from ParameterCaptionIndented to ParameterCaption.

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


Illustration 7: Our Settings for the Automatically Created Report Parameter

14.  Click OK to accept settings, and to dismiss the Report Parameters dialog.

15.  Click the Preview tab next, to execute the report.

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.


Illustration 8: The New Runtime Parameter Appears ...

16.  Click the downward arrow to the right of the Calendar Year parameter selector to expose the picklist of Year choices.

17.  Select CY 2003.

18.  Click the View Report button in the upper right corner of the Preview tab.

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.

19.  Click the Data tab.

20.  Click the downward selector arrow on the right side of the Dataset selector.

21.  Select the new DateCalendarYear dataset that appears, as shown in Illustration 9.


Illustration 9: Select the New Dataset ...

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.

NOTE: While we will encounter and discuss MDX functions, expressions, queries, and related subject matter throughout the MSSQL 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.

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.

22.  Using the dropdown Dataset selector, as before, select ProductData, to return to the primary dataset and Filter pane.

23.  Click the leftmost box (which contains the placeholder <Select dimension>, as before), within the Dimension column, in the bottom row of the Filter pane (this time, beneath the box containing Date, which we added in creating the first entry in the Filter pane earlier).

The dropdown selector is again enabled.

24.  Select Date from the list that appears in the selector, once again.

25.  Select Date.Calendar Quarter of Year within the Hierarchy box to the immediate right, using the selector that is built in.

26.  Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of “Equal,” as we did within the first entry.

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.

27.  Leaving settings in the third row of the Filter pane, select Report ->Report Parameters ... from the main menu, once again.

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,

28.  Click OK to close the Report Parameters dialog.

29.  Returning to the third row of the Filter pane, where we left off with our settings to establish a second new parameterized filter, place a checkmark in the checkbox that appears in the Parameters setting, once again.

30.  Leaving the Filter pane once again, select Report ->Report Parameters ... from the main menu.

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.

31.  Click the DateCalendarQuarterofYear item within the Parameters list to select the new parameter within the Report Parameters dialog.

32.  Replace the default string that appears within the Prompt box, Date.Calendar Quarter of Year, with the following, more user-friendly label:

Calendar Quarter

33.  Uncheck the checkbox for the Multi-value setting, as we did with the first Report Parameter.

34.  In the “Available Values” section (the middle section of the Report Parameters dialog), in the bottom of the three settings, Label field, modify the setting from ParameterCaptionIndented to ParameterCaption, once again.

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


Illustration 10: Settings for the New Calendar Quarter Report Parameter

35.  Click OK to accept settings, and to dismiss the Report Parameters dialog.

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

36.  Click the downward selector arrow on the right side of the Dataset selector, once again.

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.

37.  In the bottom row of the Filter pane, click the leftmost box within the Dimension column, once again.

38.  Select Date from the list that appears in the selector, as we did before.

39.  Select Date.Month of Year within the Hierarchy box to the immediate right, using the selector that is built in.

40.  Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of “Equal,” as we did within the first entry.

41.  Place a checkmark in the checkbox that appears in the Parameters column, as we have done with the previous two row entries we have made in the Filter pane.

The Filter pane, with all four entries, appears as shown in Illustration 11.


Illustration 11: The Four Parameterized Filters in the Filter Pane

42.  Select Report ->Report Parameters ... from the main menu, as before.

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

43.  Click the DateMonthofYear listing (the bottom of the four) within the Parameters list to select the new parameter within the Report Parameters dialog.

44.  Replace the default string that appears within the Prompt box, Date.Month of Year, with the following, more user-friendly label:

Month

45.  Uncheck the checkbox for the Multi-value setting, as we did with the previous two parameters.

46.  In the “Available Values” section (the middle section of the Report Parameters dialog), in the bottom of the three settings, Label field, modify the setting from ParameterCaptionIndented to ParameterCaption, once more.

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


Illustration 12: Settings for the New Month Report Parameter

47.  Click OK to accept settings, and to dismiss the Report Parameters dialog.

48.  Click the downward selector arrow on the right side of the Dataset selector, once again.

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

49.  Click the Layout tab, once again, to transit away from the Data tab.

50.  Click the Data tab, once more, to return immediately.

51.  Click the downward selector arrow on the right side of the Dataset selector, one last time.

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.


Illustration 13: All Datasets Appear as Expected ...

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.

NOTE: As we have noted previously, although a Report Parameter and its underlying Dataset are created automatically when we enable parameterization within the Filter pane of the MDX Query Builder (Design Mode), the removal of the “Parameter” checkmark within the associated row of the Filter pane, or even the deletion of the entire corresponding filter row, will not produce an opposite effect. The Report Parameter and the dataset will remain until they are manually removed. Moreover, the disablement of parameterization within the Filter pane, followed by re-enablement and / or recreation of the Filter pane entry will, unless we intervene before we re-enable / recreate the parameter, result in the creation of an additional Report Parameter.

Order is somewhat important in preventing confusion: for example, when deleting, the Report Parameter needs to be deleted first, with the underlying dataset (to which it refers) being deleted next, before creating a new Report Parameter – even of the same name – within the Filter pane. A Report Parameter can, of course, be repointed, but, unless we manually intervene, will likely be left pointing to an incorrect, remaining dataset if the dataset to which it was initially married was, for some reason, deleted “from under it.”

It is important to remember that changing a name can also have consequences from the perspective of alignment, and that if one “side of the equation” is modified, the other side must also be modified. Changing the Prompt label has no adverse effect, as we have seen multiple times within the steps of our practice exercise.

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.

52.  Click the Preview tab.

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

53.  Leave the Product Category prompt selection at its default of” Bikes, Components.”

54.  Click the downward pointing arrow on the right side of the Calendar Year selector.

55.  Select CY 2003 within the Calendar Year parameter picklist, as shown in Illustration 14.


Illustration 14: Select CY 2003 within the Calendar Year Parameter Picklist

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.

56.  Select CY Q3 from the Calendar Quarter picklist.

57.  Select the month of August in the Calendar Month parameter picklist.

58.  Click the View Report button.

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.


Illustration 15: The Sample Report Operates as Expected with New Date Parameters

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.

1.  Click the Data tab.

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

2.  Using the Dataset selector on the left side of the Data tab toolbar, select the DateCalendarYear dataset, as shown in Illustration 16.


Illustration 16: Select the DateCalendarYear Dataset ...

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.

3.  Click the Execute Query button atop the Data tab.

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


Illustration 17: The Retrieved Parameter Support Dataset

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.

NOTE: For more detail surrounding the MDX .Ordinal function, see MDX Numeric Functions: The .Ordinal Function. For information on the .Level function, see various articles throughout my MDX Essentials series (of which the .Ordinal function article is also a member) at Database Journal.

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

5.  Ensuring that the DateCalendarYear dataset appears within the Dataset selector, click the ellipses (“...”) button that appears to the selector’s immediate right, as shown in Illustration 18.


Illustration 18: Opening the Dataset Dialog

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

6.  Click the Filters tab.

7.  In the Filters table on the tab, click the top row in the Expression column to activate the downward-pointing selector.

8.  Select =Fields!ParameterLevel.Value within the selector, as depicted in Illustration 19.


Illustration 19: Selecting the Parameter Level Value Expression ...

9.  Ensure that the “=” sign occupies the Operator column to the immediate right (it is populated by default when we select an entry for the Expression column).

10.  Click the Value column to the right of the Operator column for the same row, and type the following into the box:

=1

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


Illustration 20: The Filters Tab of the Dataset Dialog with Our Input

11.  Click OK, to accept our modifications, and to close the Dataset dialog.

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.

12.  Select the DateCalendarQuarterofYear dataset within the dropdown Dataset selector.

13.  Click the Execute Query button atop the Data tab to retrieve the dataset.

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.

14.  In the table labeled Specify details of the parameters defined in the query, click the second row in the Default column (the row containing DateCalendarYear) to activate the downward-pointing selector.

15.  Expand All Periods in the selector picklist, and place a check in the box to the left of CY 2003 within the selector, as depicted in Illustration 21.


Illustration 21: Select a Default for the DateCalendarYear Parameter ...

16.  Click OK to accept the new default.

17.  Click OK on the Query Parameters dialog to save our input and to dismiss the dialog.

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


Illustration 22: The DateCalendarQuarterofYear Dataset Appears ...

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.

18.  Click the ellipses (“...”) button that appears to the immediate right of the Dataset selector, as we did earlier.

The Dataset dialog for the DateCalendarYear dataset opens.

19.  Click the Filters tab.

20.  In the Filters table on the tab, click the top row in the Expression column to activate the downward-pointing selector, once again.

21.  Select =Fields!ParameterLevel.Value within the selector, as we did for the DateCalendarYear dataset earlier.

22.  Ensure that the “=” sign occupies the Operator column to the immediate right, once again.

23.  Click the Value column to the right of the Operator column for the same row, and type the following into the box:

=1

24.  Click OK, to accept our modifications, and to close the Dataset dialog.

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.

25.  Select the DateMonthofYear dataset within he dropdown Dataset selector.

26.  Click the Execute Query button atop the Data tab to retrieve the dataset.

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.

27.  In the table labeled Specify details of the parameters defined in the query, click the second row in the Default column (the row containing DateCalendarYear) to activate the downward-pointing selector.

28.  Expand All Periods in the selector picklist, and place a check in the box to the left of CY 2003 within the selector, as we did earlier.

29.  Click OK to accept the new default.

30.  Click the third row in the Default column (the row containing DateCalendarQuarterofYear) to activate the downward-pointing selector.

31.  Expand All Periods in the selector picklist, and place a check in the box to the left of CY Q3 within the selector, as we did earlier.

32.  Click OK to accept the new default.

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


Illustration 23: The Query Parameters Dialog for the DateMonthofYear Dataset with Our Input

33.  Click OK on the Query Parameters dialog to save our input and to dismiss the dialog.

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


Illustration 24: The DateMonthofYear Dataset Appears ...

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.

34.  Click the ellipses (“...”) button that appears to the immediate right of the Dataset selector, once again.

35.  Click the Filters tab on the Dataset dialog for the DateMonthofYear dataset, which opens next.

36.  In the Filters table on the tab, click the top row in the Expression column to activate the downward-pointing selector.

37.  Select =Fields!ParameterLevel.Value within the selector, as we did for the DateCalendarYear and DateCalendarQuarterofYear datasets earlier.

38.  Ensure that the “=” sign occupies the Operator column to the immediate right, as we did earlier.

39.  Click the Value column to the right of the Operator column for the same row, and type the following into the box, once again:

=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.)

1.  Click the Preview tab.

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

2.  Leave the Product Category prompt selection at its default of” Bikes, Components.”

3.  Click the downward pointing arrow on the right side of the Calendar Year selector.

4.  Select CY 2004 within the Calendar Year parameter picklist.

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.

5.  Select CY Q2 from the Calendar Quarter picklist, as depicted in Illustration 25.


Illustration 25: Selection Options Demonstrate Cascading Nature of the Date Parameters

6.  Select the month of June in the Month parameter picklist, as shown in Illustration 26.


Illustration 26: Cascading Nature of the Date Parameters Again in Evidence

We notice again that the “All Periods” selections that previously appeared atop the Quarter and Month parameters’ picklists have disappeared. The cascading nature of the date-related parameters is also demonstrated again, as we see that our selection of CY Q2 enables the picklist for the Month parameter, which appropriately reflects only the three member Months belonging to CY Q2 as selections, April, May and June.

7.  Click the View Report button.

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 27.


Illustration 27: The Sample Report Operates as Expected with New Date Parameters

Our verification process has demonstrated the effective removal of the “All” selection (which appears by default when we chose to generate our parameter picklists via the automatic creation mechanism that Reporting Services offers) from our newly added picklists. Moreover, we have witnessed the cascading nature of the new parameters that is established as a secondary benefit of adding parameters in the manner we have chosen.

We have verified that the parameter level filtering we have put into place accomplishes the intended ends, and allows us to meet the expressed requirement of the information consumers to remove the “All” selection. We have also noted that the Reporting Services 2005 environment, with its graphical design environment, supports easy and flexible design of cascading parameters, in addition to allowing us to customize picklists via filtering within the automatically created datasets.

We will extend our examination of parameterization yet further in the second half of this article, where we will generate a solution to meet the need of our colleagues to provide intuitive parameter defaults to information consumers at report runtime.

NOTE: Please consider saving the .rdl file we have created to this point for use in the second half of this article, so as to avoid the need to repeat the preparation process we have undertaken above.

8.  Experiment further with the report, if desired.

9.  When ready, click the Layout tab.

10.  Select File -> Save DBJ_OLAP_Report.rdl As ... to save our work, up to this point, to a location where it can be easily accessed for Part II of this article.

11.  Select File -> Exit to leave the design environment, when ready.

Conclusion ...

In this article, we continued the extended examination of Parameters in Reporting Services 2005 that we began in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II. After discussing parameterization in general, we defined a hypothetical scenario within the context of a client business requirement: the need to remove the “All” selection that appears by default within parameter picklists created automatically by Reporting Services. We then moved into our hands-on practice session.

After creating a clone of a sample OLAP report, containing a Matrix data region, we ascertained connectivity of its shared Analysis Services data source. We then made structural modifications to the report, to prepare for our practice exercise session with three date-related parameters, whose additions to the report were requested by our hypothetical client. We created, within the graphical Design Mode of the MDX Query Builder, three filters for which parameterization was enabled via the Filter pane setting.

In conjunction with the creation of the parameterized filters, we inspected the automatically created Report Parameters and their settings, as well as the subsequently created datasets underlying the new Report Parameters. We then focused upon the modification of the respective datasets to filter the “Allparameter level from the data rows retrieved and presented within the parameter picklists at runtime. Throughout the steps we undertook, we discussed how the various components were tied together, and the potential challenges we face in modifying these objects without consideration of the resulting dependencies. Finally, we previewed the report to observe the effectiveness of our solution in eliminating the “All” selection in the picklists at runtime, as well as the added benefit of the general cascading nature of the parameters at runtime.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved