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

Tuesday Jan 16th 2007 by William Pearson
Share:

Join BI Architect Bill Pearson as he continues his examination of parameterization within Analysis Services reports. In this article, we get hands-on practice creating rudimentary cascading parameters using the graphical user interface, and then examine the nature and timing of automatically created objects.

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.

About the Mastering OLAP Reporting Articles ...

One of the first things that become clear to “early adopters” of Reporting Services is that the “knowledgebase” for Analysis Services reporting with this tool is, to say the least, sparse. As I stated in my article, Mastering OLAP Reporting: Cascading Prompts (where I treated the subject of cascading parameters for Reporting Services 2000), the purpose of the Mastering OLAP Reporting subset of my Reporting Services series is to focus on techniques for using Reporting Services for OLAP reporting. In many cases, which I try to outline in my articles at appropriate junctures, the functionality of well-established, but expensive, solutions, such as Cognos PowerPlay, can be met in most respects by Reporting Services – at a tiny fraction of the cost.

The vacuum of documentation in this arena, even taking into consideration the release of several books surrounding Reporting Services 2005 in recent months, continues to represent a serious “undersell” of Reporting Services, from an OLAP reporting perspective. I hope to contribute to making this arena more accessible for everyone, and to share my implementation and conversion experiences as the series evolves. In the meantime, we can rest assured that the OLAP potential in Reporting Services will contribute significantly to the inevitable commoditization of business intelligence, via the integrated Microsoft BI solution.

For more information about the Mastering OLAP Reporting articles, see the section entitled “About the Mastering OLAP Reporting Articles” in my article Ad Hoc TopCount and BottomCount Parameters.

Overview

A common enterprise reporting requirement among information consumers is the capability to filter reports at run time for specific information they need. 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.

A further refinement of the picklist parameter type is the cascading picklist. In a cascading picklist scenario, the set of available values among which a consumer can select for one parameter depends upon the value previously chosen in another parameter. For example, the first parameter (“state”) could present a list of states within which the organization’s customers reside. When the consumer selects a state, the set of possible values presented from which to select the second parameter (“city”) is updated with a list of cities within the chosen state. A third parameter (“customers”) could then display a list of customers within the selected city. The customer name or other ID number could then be used to filter the report to a particular customer. The process of filtering a list of parameter values, based upon the value selected for a previous parameter, is described as “cascading” (and also known as “hierarchical,” or “dependent”).

I have implemented cascading parameters in numerous ways. One of my favorite ways to accomplish any sort of picklist parameterization has been to create support objects within the Analysis Services cube that is used as a data source for the reports under consideration. For an example of implementing support for a hierarchical picklist in this manner, see my Database Journal article Create a Cube-Based Hierarchical Picklist.

In this article, we will provide an approach to creating a rudimentary set of cascading parameters completely within Reporting Services 2005, where the debut of the new Query Builder makes the assembly of the various parts much more straightforward than the steps we had to take in Reporting Services 2000. (For a detailed example of creating cascading parameters in Reporting Services 2000, see my article Mastering OLAP Reporting: Cascading Prompts, an earlier member of this series). The use of the Query Builder also presents challenges, however, when we need to make modifications to components that are largely automatically generated. 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 setting up cascading parameters, as well as to identify potential issues that might arise within the set of automatically generated support objects. In examining the setup of cascading 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 Cascading Parameters within a Matrix data region;
  • Create, within the graphical Design Mode of the MDX Query Builder, multiple filters for which parameterization is enabled via the Filter pane setting;
  • Inspect the automatically created Report Parameters and their settings;
  • Examine 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;
  • Preview the report to observe the Cascading Parameters in runtime action.

Cascading Parameters for Analysis Services Reporting

Objective and Business Scenario

In Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, we introduced parameterization in general, discussing the challenges that faced many of us within the Reporting Services 2005 environment. Chief among the difficulties in working with the early release of the application was the absence of an MDX editor: while those of us who were comfortable with MDX syntax were not impaired significantly (although we had to deal with circumstances that accompanied cascading parameterization in Reporting Services 2000, such as the need to string our queries for passage from Reporting Services to the Analysis Server, and the inability to “test generate” our Datasets, once we had Parameters in place within the MDX queries), those who were already challenged with MDX as a language almost certainly found no amusement in dealing with the added mechanics. We noted that Reporting Services 2005 introduced the MDX Query Builder, a tool that appeals, due to its “on-off” flexibility, to most practitioners who are comfortable writing direct MDX queries, as well as to the MDX- challenged, among those authors and developers who need to work with OLAP data sources. This arrival of the current edition has, unsurprisingly, changed the way that many of us had become accustomed to writing the underlying MDX queries for our Analysis Services reports.

In this article, we continue the extended examination of Parameters in Reporting Services 2005 that we began in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, where our objective was to explore the basics surrounding Parameters, revealing several events that take place in conjunction with their creation in the design environment. Just as we examined the interaction among the various components that comprise and support run-time parameterization within our initial article, we will continue our observation of the actions that Reporting Services takes behind the scenes in our creation of cascading parameters. We will begin, once again, by adding simple filter / Parameter combinations to a basic OLAP report, containing a Matrix data region, via the graphical interface of the MDX Query Builder. We will then, more importantly, examine the objects that Reporting Services automatically creates, in conjunction with our additions, to support cascading parameterization at runtime. Moreover, we will discuss the relationship of these objects, and issues that can emerge when we make modifications to them – issues that we can circumvent by taking steps we will suggest.

Practice

Our first objective is to create a copy of the Sales Reason Comparisons sample report, within which we can implement cascading parameters. The focus of our efforts will be the addition of cascading parameterization into an OLAP report containing a Matrix data region (the mechanics behind adding the capability, not the design of the report itself). 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.

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 project 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, 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 no parameters or filters. 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 ...

DBJ_OLAP_Report.rdl opens in Layout view.

We will start with the Data tab.

3. Click the Data tab.

We enter the Data view, where we will remove a handful of components that we do not need for our practice session. We will accomplish this from the perspective of the MDX Query Builder, the main components of which are labeled in Illustration 2 below.


Illustration 2: The MDX Query Builder

4. With the Dataset selector, select the ProductList dataset, as depicted in Illustration 3.


Illustration 3: Select the ProductList Dataset ...

5. Once the Dataset loads, click the Delete button, as shown in Illustration 4.


Illustration 4: Deleting the Unneeded Dataset ...

6. Click Yes on the Microsoft Report Designer warning message dialog that appears next, as depicted in Illustration 5.


Illustration 5: Confirm Intention to Delete ...

The primary (and sole remaining) dataset, ProductData opens.

7. Within the Query pane of the MDX Query Builder, right-click the column heading for Internet Total Product Cost.

8. Click Delete Internet Total Product Cost from the context menu that appears, as shown in Illustration 6.


Illustration 6: Deleting the Unwanted Measure from the Dataset

9. Within the Calculated Members pane, right-click the sole Calculated Member, Profit.

10.  Click Delete on the context menu that appears, as depicted in Illustration 7.


Illustration 7: Deleting the Calculated Member

11.  Click OK on the confirmation dialog that appears, asking if we are “sure we want to delete the selected calculated members.”

12.  Right-click the single entry in the Filter pane (directly atop the Query pane).

13.  Click Delete on the context menu that appears, to eliminate the existing Product Category Parameter from the Filter pane, as shown in Illustration 8.


Illustration 8: Deleting the Product Filter from the Filter Pane

We next need to remove the single Report Parameter that was associated with the parameterized Product Category filter that we have removed from the Filter pane. This Product Category Filter pane entry had been established with the Parameter checkbox selected, a common way to create a parameterized filter, as we saw in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, and as we shall see later in the practice session within this article. Because parameterization was selected for the Product Category filter, it follows that a Report Parameter might well have been created at the time the entry was made. We will now remove the remaining Report Parameter.

14.  From the main menu, select Report -> Report Parameters..., as depicted in Illustration 9.


Illustration 9: Select Report -> Report Parameters...

The Report Parameters dialog opens, and we see a single Report Parameter listed.

15.  Ensure that the ProductCategory Parameter is selected.

16.  Click the Remove button, as shown in Illustration 10.


Illustration 10: Removing the Residual Report Parameter ...

17.  Click OK to accept our modifications, and to dismiss the (now empty) Report Parameters dialog.

We will make preparatory modifications to the Dataset next. First, we will eliminate an unneeded data field.

18.  Within the Data pane, click the Sales Reason column heading, to select it.

19.  Drag the Sales Reason column heading into the Metadata pane, to remove it from the Data pane, as depicted in Illustration 11.


Illustration 11: Removing the Sales Reason Data Field from the Data Pane

The Sales Reason column disappears, as desired. We will now add data fields that will be useful within our practice report.

20.  Within the Metadata pane, expand the Date dimension, by clicking the “+” sign to its immediate left.

21.  Expand the Calendar folder that appears within the Date dimension.

22.  Expand the Calendar hierarchy within the Calendar folder.

23.  Drag Calendar Year into the Data pane, dropping it to the left of the Sales Territory Group column, as shown in Illustration 12.


Illustration 12: Adding Calendar Year to the Dataset

The new Calendar Year column appears, as desired.

24.  In like manner, drag Calendar Quarter into the Data pane, dropping it between the Calendar Year and Sales Territory Group columns.

25.  Similarly, drag Month into the Data pane, dropping it between the Calendar Quarter and Sales Territory Group columns.

26.  Drag Date into the Data pane, dropping it between the Month and Sales Territory Group columns.

The newly added columns in the Data pane appear as partially depicted in Illustration 13.


Illustration 13: The New Columns in the Data Pane (Partial View)

Having made these changes on the Data tab, we are ready to move to the Layout tab, where we will perform further modifications to the report file.

27.  Click the Layout tab, as shown in Illustration 14.


Illustration 14: Click the Layout Tab

28.  On the Layout tab, within the Matrix data region, select the value appearing underneath the Internet Total column heading.

29.  Right-click the value in the text box (the value appears as =Sum(Fields!Internet_Total_Product_Cost.Value) ).

30.  Select Delete from the context menu that appears, as depicted in Illustration 15.


Illustration 15: Delete the Value for Internet Total Product Cost

31.  Right-click the upper left-hand corner of the Matrix Data Region (the gray square).

The gray column and row bars (the “handles”) disappear, as a light, patterned outline forms around the Matrix data region, and the context menu appears.

32.  Select Properties from the context menu, as shown in Illustration 16.


Illustration 16: Select Properties from the Context Menu ...

The Matrix Properties dialog appears, defaulted to the General tab.

33.  Click the Groups tab.

34.  In the Rows section of the Groups tab (in the upper half of the tab), select the sole group, named matrix1_Sales_Reason.

35.  Click the Edit button, as depicted in Illustration 17.


Illustration 17: Editing the Matrix1_Sales_Reason Row Group ...

The Grouping and Sorting Properties dialog appears, defaulted to its General tab.

36.  Atop the General tab, change the existing Name to the following:

matrix1_Cal_Year

37.  Click the row containing the Expression value (currently the expression is “Fields!Sales_Reason.Value”), within the Group on list, to enable the selector.

38.  Select =Fields!Calendar_Year.Value within the selector, as shown in Illustration 18.


Illustration 18: Replacing the Existing Group Expression ...

39.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

40.  Click the Add button, as depicted in Illustration 19.


Illustration 19: Adding a New Row Group

The Grouping and Sorting Properties dialog reappears, defaulted to its General tab, as before.

41.  Atop the General tab, change the default Name to the following:

matrix1_Cal_Qtr

42.  Click the row containing the Expression value (currently the expression is blank) within the Group on list, to enable the selector.

43.  Select =Fields!Calendar_Quarter.Value within the selector.

The Grouping and Sorting Properties dialog appears, with our input, as shown in Illustration 20.


Illustration 20: The Grouping and Sorting Properties Dialog with Our Input

44.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

45.  Click the Add button, once again.

The Grouping and Sorting Properties dialog appears, once again.

46.  Atop the General tab, change the default Name to the following:

matrix1_Cal_Month

47.  Click the row containing the Expression value (currently the expression is blank) within the Group on list, to enable the selector.

48.  Select =Fields!Month.Value within the selector.

The Grouping and Sorting Properties dialog appears, with our input, as depicted in Illustration 21.


Illustration 21: The Grouping and Sorting Properties Dialog with Our Input

49.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

50.  Click the Add button, once again.

The Grouping and Sorting Properties dialog reappears.

51.  Atop the General tab, change the default Name to the following:

matrix1_Cal_Date

52.  Click the row containing the Expression value (currently the expression is blank) within the Group on list, as before, to enable the selector.

53.  Select =Fields!Date.Value within the selector.

The Grouping and Sorting Properties dialog appears, with our input, as shown in Illustration 22.


Illustration 22: The Grouping and Sorting Properties Dialog with Our Input

54.  Click OK, to accept our modifications, and to close the Grouping and Sorting Properties dialog.

The Group tab reappears, with our input, as depicted in Illustration 23.


Illustration 23: The Grouping Tab with the New Groups

55.  Click OK on the Groups tab, to close the Matrix Properties dialog.

We are returned to the Layout view.

56.  Once again on the Layout view, right-click the leftmost textbox of the Matrix value row (which currently contains the “=Fields!Sales_Reason.Value”).

57.  Select Expression ... on the context menu that appears, as shown in Illustration 24.


Illustration 24: Editing the Existing Value Expression ...

The Expression Editor opens.

58.  Replace the expression in the upper portion of the Editor with the following:

=Fields!Calendar_Year.Value

The intent is to label the column to reflect the nature of the Group we have put in place: Years instead of Sales Reasons.

59.  The Expression Editor appears, with our modification, as depicted in Illustration 25.


Illustration 25: The Expression Editor with Our Substitution in Place

60.  Click OK to accept our modification, and to dismiss the Expression Editor.

Since the report no longer deals with Sales Reasons, we will eliminate the Sales Reason heading label.

61.  Right-click the textbox (containing the title Sales Reason), immediately above the box for which we changed the expression to “=Fields!Calendar_Year.Value.”

62.  Select Delete on the context menu that appears, as shown in Illustration 26.


Illustration 26: Deleting the Unwanted Label ...

Now, only a few minor cleanup items remain in our preparation sequence.

63.  Right-click the value field underneath the column label Internet Orders. The value displayed in the box is =Sum(Fields!Internet_Order_Quantity.Value)”).

64.  Click Properties from the context menu that appears, as depicted in Illustration 27.


Illustration 27: Editing the Value Field Properties ...

65.  On the Textbox Properties dialog that next appears, click the Format tab.

66.  Replace the existing Format code setting (in the upper left corner of the Format tab) with the following string:

#,###

Here we are simply changing the existing format to one that is more appropriate for a count value. The Format code appears on the Format tab of the Textbox Properties dialog as shown in Illustration 28.


Illustration 28: Replacing the Existing Format Code ...

67.  Click OK to accept our modification, and to dismiss the Textbox Properties dialog.

Now let’s reset a couple of basic layout dimensions to make the report a bit more compact.

68.  Select all four row group columns, by clicking them, one after another, with the SHIFT key depressed.

69.  In the Properties pane, set the Width for the columns to 1.00, as depicted in Illustration 29.


Illustration 29: Modifying the Widths for the Four Selected Columns ...

70.  Click inside the report body to enact the new Width setting.

The selected columns immediately contract as expected.

71.  Click inside the Matrix, once again, to cause its handles to appear.

72.  Click the handles for the bottom two rows of the matrix, to highlight / select both rows.

73.  Set the Font Size (in the toolbar above the development environment) for the selected rows to 8pt, as shown in Illustration 30.


Illustration 30: Setting Font Size for the Bottom Two Rows of the Matrix ...

The font adjusts immediately within the affected rows.

74.  In a similar manner, click the handle for the row immediately above the bottom two rows we have just modified (and the second row from the top of the Matrix), to highlight / select it.

75.  Assign a Font Size of 10pt to the selected row.

76.  Click the top report heading, which bears the words “Adventure Works Cycles,” to select the textbox involved.

77.  Assign the textbox a Font Size of 12pt, as depicted in Illustration 31.


Illustration 31: Setting the Font Size for the Primary Report Heading

As a final piece of housekeeping, we will name the four textboxes reflecting the row groups we have added. We will then reference one of these names in our concluding preparatory step, where we set visibility for the Date column.

78.  Right-click the leftmost textbox of the Matrix value row (the textbox whose contents we modified to “=Fields!Calendar_Year.Value” in an earlier step of this section).

79.  Select Properties on the context menu that appears.

The Textbox Properties dialog opens, defaulting to the General tab, as we saw earlier.

80.  Replace the existing Name (“Sales_Reason_1”) in the upper portion of the Editor with the following:

 Calendar_Year

The Textbox Properties dialog appears, with our modification, as shown in Illustration 32.


Illustration 32: The Textbox Properties Dialog – General Tab, with Our Substitution in Place

81.  Click OK to accept our modification, and to dismiss the Textbox Properties dialog.

82.  Right-click the textbox to the immediate right of the one we have just renamed “Calendar_Year.”

83.  Select Properties on the context menu that appears.

84.  Replace the existing Name (“textbox4” or similar), atop the Textbox Properties dialog General tab that next appears, with the following:

Calendar_Quarter

85.  Click OK to accept our modification, and to dismiss the Textbox Properties dialog.

86.  Right-click the textbox to the immediate right of the one we have just renamed “Calendar_Quarter.”

87.  Select Properties on the context menu that appears.

88.  Replace the existing Name (“textbox5” or similar), atop the Textbox Properties dialog General tab that next appears, with the following:

Calendar_Month

89.  Click OK to accept our modification, and to dismiss the Textbox Properties dialog.

We will now return to the Matrix Properties dialog to make our last preparatory settings.

90.  Right-click the upper left-hand corner of the Matrix data region (the gray square), once again.

NOTE: If the handles for the Matrix data region are not visible, click somewhere within the region to cause them to appear, so as to enable the step above.

The gray column and row bars disappear, as the outline that we saw earlier forms around the Matrix data region.

91.  Select Properties on the context menu that appears, as we did earlier.

The Matrix Properties dialog appears, defaulted to the General tab, once again.

92.  Click the Groups tab.

93.  In the Rows section of the Groups tab (in the upper half of the tab), select the matrix1_Cal_Date entry (the bottom row group in the list).

94.  Click the Edit button, once again.

The Grouping and Sorting Properties dialog appears.

95.  Click the Visibility tab.

96.  On the Visibility tab, click the radio button labeled Hidden, to select Initial visibility of Hidden.

97.  Place a check in the checkbox labeled “Visibility can be toggled by another report item.”

98.  In the selector labeled Report item, which becomes enabled upon checking the above, select Calendar_Month, as depicted in Illustration 33.


Illustration 33: Select Calendar Month as the Toggle Point for Calendar Date

99.  Click OK to accept our modification, and to dismiss the Grouping and Sorting Properties dialog.

100.  Click OK on the Groups tab, to close the Matrix Properties dialog.

We are returned to the Layout tab. We will execute the report to ascertain that our modifications are complete, and that we have a working report for the practice session that follows.

101.  Click the Preview tab, as shown in Illustration 34.


Illustration 34: Click the Preview Tab ...

The Report is being generated message briefly appears, and then the report displays.

102.  Click the “+” sign to the left of January 2002 (which is at the right of Q1 CY 2002, near the top of the report), to ascertain that the Visibility setting behaves as expected.

The modified report, complete with the sample expanded Calendar Month, appears as partially depicted in Illustration 35.


Illustration 35: The Modified Report (Partial View)

Our non-parameterized, non-filtered report is now ready for the practice session, which we will begin in the next section.

Procedure: Adding Cascading Parameters in Reporting Services 2005

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

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

Add Cascading Parameters through the Query Builder Interface

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

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

1. Click the Data tab.

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


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

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

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

3. Expand the Date Calendar hierarchy, once again.

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

4. Click the leftmost box (which contains the placeholder <Select dimension>), within the Dimension column, in the single existing row of the Filter pane, to enable its dropdown selector.

5. Select Date from the list that appears in the selector, as depicted in Illustration 37.


Illustration 37: Adding the First Date Filter ...

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

7. Leave the Operator setting (to the immediate right of the Hierarchy setting) at its default of “Equal,” which appears when we click the box.

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 top row of the Filter pane.

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


Illustration 38: Filter Pane of the MDX Query Builder - Settings for First Parameterized Filter

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

NOTE: As we have noted in other articles, and as we will reiterate multiple times, 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 two Report Parameters.

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

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

We recall that we previously removed all Report Parameters, as part of our preparation for the practice exercise. Yet we note, within the Report Parameters dialog that has appeared, the presence of a new Report Parameter, called DateCalendarYear. This Report Parameter was created automatically when we designated our new row within the Filter pane as a Parameter (via the enabling checkbox).

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

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

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

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 to disable it.

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


Illustration 39: Settings for the Automatically Created Report Parameter

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

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

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


Illustration 40: The New Runtime Parameter Appears ...

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

16.  Select CY 2003.

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

18.  Click the Data tab once again.

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

20.  Select the new DateCalendarYear Dataset that appears, as depicted in Illustration 41.


Illustration 41: 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 noted, the Dataset was created automatically, after the Report Parameter was itself created, and connected to its underlying Dataset via the three settings we described, which were also set automatically by Reporting Services.

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

  • ParameterCaption
  • ParameterValue
  • ParameterLevel

All three fields are products of calculated members created via MDX syntax (using the WITH MEMBER keyword combination). As we discover within other articles of the series, there are multiple ways to approach picklist support (I very often construct members such as the above at the Analysis Services level, to afford central maintenance and reusability, among other, perhaps less obvious, advantages). This is simply the mechanism through which Reporting Services accomplishes support of the automatically generated Report Parameter. As we saw earlier, the Value and Label settings within the Report Parameter dialog reference the ParameterValue and ParameterCaption fields, respectively, within this Dataset.

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 members of the cascading parameters chain.

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

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

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

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

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

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

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

27.  Click OK to close the Report Parameters dialog.

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

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

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

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

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

Calendar Quarter:

32.  Uncheck the checkbox for the Multi-value setting to disable it, as we did with the first Report Parameter.

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


Illustration 42: Settings for the New Calendar Quarter Report Parameter

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

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

34.  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 and DateCalendarYear Datasets. In this manner, we can verify that, although the creation of the Report Parameter, complete with internal references to a supporting Dataset, is triggered by our placing a checkmark in the Parameters setting of the respective Filter pane row, the Dataset itself is not physically created until we leave the Data tab (as we did in our previous example, when we clicked the Layout tab to transit to the Layout view).

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

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

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

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

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

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

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


Illustration 43: The Three Parameterized Filters in the Filter Pane

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

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

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

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

Calendar Month:

43.  Uncheck the checkbox for the Multi-value setting to disable it, as we did with the previous two parameters.

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


Illustration 44: Settings for the New Month Report Parameter

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

45.  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 and DateCalendarYear Datasets, as we left them earlier.

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

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

48.  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 four Datasets, including the primary Dataset and the three we have added via the enactment of parameterized filters within the Filter pane (and subsequent departure from the Data tab) now appear, as depicted in Illustration 45.


Illustration 45: All Datasets Appear as Expected ...

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

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 two Report Parameters.

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 will default 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.

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

Verification: Preview the Report and Inspect Effectiveness of Cascading Parameters

Let’s preview the report to inspect the results of our handiwork. (As is probably obvious, one of the reasons that I chose hierarchical time for an example in cascading parameters is the unambiguous relationship between parent and child level members. We should be able to verify correct operation simply by knowing, for example, which Months are the children of a given Calendar Quarter, etc.)

1. Click the Preview tab.

DBJ_OLAP_Report.rdl initializes, and the Calendar Year prompt becomes enabled.

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

3. Select CY 2002 within the Calendar Year parameter picklist.

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

4. Select CY Q3 from the Calendar Quarter picklist.

We note that Calendar Quarters 1, 2, 3, and 4 appear, indicating that what we're seeing is likely to be correct.

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

Again, we can see that our selections consist of months that belong to parent CY Q3.

6. Click the View Report button.

The report executes quickly and returns the data for the selections we have made within our parameter picklists. We will “drill down” the August 2002 group that appears upon the face the report, to verify that the dates therein indeed belong to the month of August, 2002.

7. Expand the August 2002 group by clicking the “+” sign to its immediate left.

The underlying dates appear, all of which are appropriately classified children (specific Dates) of the Month of August, 2002, as partially shown in Illustration 46.


Illustration 46: Our Cascading Parameters Deliver as Expected ...

We thus see that the cascading parameters we have put into place accomplish the intended ends, and allow us to meet the need as expressed by the information consumers. We can easily see that the Reporting Services 2005 environment, with its graphical design environment, supports easy and flexible design of cascading parameters. As we have noted throughout the steps of our practice procedures, however, potential obstacles exist when we rely upon the automatic generation of the objects supporting cascading parameters, particularly in scenarios where we need to modify one or more objects after their initial creation and alignment. We have examined some of these considerations, and, if we keep them in mind during design of cascading parameters, we should be able to enjoy the benefits of the capabilities they provide in the current version of Reporting Services.

8. Experiment further with the report, if desired.

9. Select File -> Save All to save our work to this point.

10.  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. After discussing some of the improvements that have come along since Reporting Services 2000, and specifically focusing upon how the new Query Builder makes the assembly of the various objects involved in the construction of cascading parameters much more straightforward than the steps we had to take in Reporting Services 2000, we briefly touched upon some of the shortcomings that accompany automatic generation of those objects, and 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 cascading parameters. We then created, within the graphical Design Mode of the MDX Query Builder, multiple 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. 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 cascading Parameters in runtime action.

» 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