Parameterization from Analysis Services - Cascading Picklists

Wednesday Mar 26th 2008 by William Pearson
Share:

BI Architect Bill Pearson adds refinements to Analysis Services- based parameterization within Reporting Services. In this article, we add support for cascading picklists.

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. We noted that this is typically managed via parameterization (known in other enterprise reporting applications 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.

In Customize Automatically Created Parameter Support Objects Pt. I, we reviewed type-in and picklist parameters in general, and then concentrated our focus, once again, upon picklist parameters, which we noted to be a frequent choice among information consumers for user-friendly operation within reports. We next focused upon the effective use of parameter defaults, in Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, in making reports that contain parameters even more user-friendly and efficient at runtime.

In the most recent article of this series, Support Parameterization from Analysis Services, we extended our examination of parameterization support yet further, continuing our work within the scenario we established within Customize Automatically Created Parameter Support Objects Pt. I, again with an objective of meeting the need of the hypothetical client to support parameterization, including picklists, within the report, but with a significant difference: We exposed the steps involved in offering a simple means of supporting our solution from within the Analysis Services layer of the integrated Microsoft BI solution. In examining the support of parameterization from Analysis Services, we created a new clone of an existing sample OLAP report, containing a Matrix data region, to which we made structural modifications, to prepare for our practice exercise session. We next discussed, and then implemented, an approach, from within Analysis Services, to meet the need of our hypothetical client to support Report Parameters from the Analysis Services layer of the integrated Microsoft BI solution. Finally, we accessed and employed the new Analysis Services parameter support components from within Reporting Services, reviewing how the various components of the parameter support solution we proposed were tied together, and concluding with a preview of the report to observe the effectiveness of our solution in runtime action.

In this article, we will continue to work with the basic OLAP report we created in Support Parameterization from Analysis Services. Our focus will be primarily to perform a refinement from the perspective of the support we provided from the Analysis Services layer of our integrated BI solution. We will establish cascading picklists within the report we created in Support Parameterization from Analysis Services (just as we will undertake the addition of support for intuitive parameter defaults at runtime in our next article), for greater user-friendliness and overall operational efficiencies. In pursuing this refinement within the support of parameterization from Analysis Services, we will:

  • Reopen the sample Report Server project, AdventureWorks Sample Reports, and access the existing sample report we prepared in Support Parameterization from Analysis Services.
  • Discuss the utility of establishing cascading parameters within a report to make it more user-friendly and effective.
  • Make modifications to the datasets underlying our report parameters to incorporate cascading picklist support.
  • Preview the report to observe the effectiveness of our solution in runtime action.

Support Parameterization from Analysis Services

Objective and Business Scenario

As any regular reader of this series is aware, parameterization can be implemented in many ways to fit 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. In Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II, we noted that 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), that we need to quickly add basic parameterized filters to our reports. We discussed the fact that the automatically created objects serve us well with minimal modification in many cases; we emphasized, however, the frequent need to “tweak” the components supporting parameterization, often a bit beyond mere cosmetics, to obtain more sophisticated features. In two subsequent articles of our series, Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, we outlined some of the customization needs that might arise, and got some hands-on exposure to the steps required to make the modifications involved.

As we mentioned within these two articles, as well as within many others throughout my various Database Journal series’, we might offer solutions to meeting reporting requirements that are completely contained within the reporting layer of the integrated Microsoft business intelligence solution, but that other options exist at the Analysis Services and MSSQL Server RDBMS levels. An important consideration during the design phase of any implementation effort, as we have noted repeatedly, 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) within other layers of the solution. For example, I have often supported parameter picklists with support objects I have created within the Analysis Services database that serves as a data source for the report(s) under consideration. As I have emphasized in many other of my articles, one of the numerous advantages of “placing the intelligence” into layers outside Reporting Services lies within the fact that we can thus avoid adding the intelligence to support our needs within every individual report where it is useful; it also means a central point of maintenance, the enforcement of consistent application of the logic and / or business rules across multiple reports, and other benefits.

Working with a new basic OLAP report in Support Parameterization from Analysis Services, we began with an objective of meeting the need of a hypothetical client to support parameterization, including picklists, within the report. In that article we exposed the steps involved in offering a simple means of supporting our solution from within the Analysis Services layer of the integrated Microsoft BI solution, and then accessed and employed the new Analysis Services parameter support components from within Reporting Services. Finally, we previewed the report to observe the effectiveness of our solution in runtime action.

NOTE: 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.

Having created, in Support Parameterization from Analysis Services, a basic sample OLAP report containing a matrix data region, to which we added simple filter / parameter combinations (based upon structures we created within Analysis Services) and having previewed the effectiveness of our solution for rudimentary runtime parameterization, we will next delve into the modifications we need to make to meet additional business requirements of a hypothetical client, the AdventureWorks organization.

To detail the specifics, the same client team with which we consulted in Support Parameterization from Analysis Services, the Adventure Works Customer Care department, has made an additional request for our support in accomplishing its objectives. Our client colleagues inform us that information consumers have asked that they refine the report we created together in Support Parameterization from Analysis Services to make the existing parameterization even more user-friendly by providing cascading behavior.

As most of us are aware, in a cascading picklist scenario, the set of values of one parameter depends upon the value chosen in another, typically “previous” parameter. For example, the first parameter 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, is updated with a list of cities within the chosen state. A third parameter could then display a list of customers within the selected city. The customer name, or other identifying number, could then be used to filter the report to a particular customer. The process of filtering a list of parameter values, based upon a value from a previous parameter, is described as “cascading” (and also sometimes known as “hierarchical” or “dependent”).

We confirm our understanding of the foregoing need, and propose to construct a working example of a way to provide the cascading picklists that our colleagues have requested for the new report parameters – an example, we add, that can be modified to drive different picklist behavior, should the consumers request further refinements after “road testing” the initial enhancements.

Preparation

Preparation: Locate and Open the Report Project and Report Created in Pt I

As a part of preparation for our practice session in Support Parameterization from Analysis Services, we created a clone of a sample OLAP report, containing a matrix data region, for which 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.

We discussed, and then implemented, an approach to meet the need of our hypothetical client to support Report Parameters from the Analysis Services layer of the integrated Microsoft BI solution. We then accessed and employed the new Analysis Services parameter support components from within Reporting Services, examining how the various components of the parameter support solution we proposed are tied together to support our runtime parameters.

In this practice session, we will resume where we left off at the end of Support Parameterization from Analysis Services, modifying our newly created Report Parameters further to support the cascading behavior requested by the client, as described in the section above. We will perform the preparation and practice steps which follow within the SQL Server Business Intelligence Development Studio, as we did within Support Parameterization from Analysis Services.

1.  Reopen SQL Server Business Intelligence Development Studio, as appropriate.

2.  Close the Start page, if desired.

3.  Reopen the AdventureWorks Sample Reports project, which contains the sample reports that ship with the Reporting Services component of the MSSQL Server 2005 suite, as well as the clone report we created in Support Parameterization from Analysis Services.

4.  Reopen the report with which we conducted our practice session in Support Parameterization from Analysis Services, which we named DBJ_OLAP_Report, by double-clicking the report within the Solution Explorer.

DBJ_OLAP_Report opens in Layout view, as shown in Illustration 1.


Illustration 1: Our Sample Report in Layout View

We are now ready to begin working with the existing report to add support for cascading picklists.

Practice

Practice: Modify Datasets in Reporting Services to Support Cascading Parameter Picklists

We noted in Support Parameterization from Analysis Services, in the section titled “Verification: Preview the Report and Inspect the Effectiveness of Our Solution,” that the parameter picklists we had put into place in the practice section did not cascade. An example of this behavior can be easily demonstrated by taking the following steps.

1.  From our current position in Layout view, 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 Year selector.

4.  Select CY 2004 within the Year parameter picklist.

5.  Click the downward pointing arrow on the right side of the Quarter selector.

We notice at this stage that all four quarters appear in the Quarter selector, as depicted in Illustration 2.


Illustration 2: Evidence of Non-Cascading Behavior in the Report ...

The appearance of four quarters is a circumstance that is contradicted by the fact that CY 2004 is not a complete year in our cube – only the first three quarters contain data, so CY Q4 should not appear as a selection option. We can thus see clearly that we need to make some modifications to the report to support cascading picklists. We will accomplish this in the steps that follow.

6.  Click the Data tab to return to the datasets underlying the report.

Here we will make modifications to “chain” the report’s datasets, which we created in Support Parameterization from Analysis Services to support our time-related parameters. This might be handled several ways, but the use of simple WHERE expressions in the dataset queries supporting the Quarter and Year picklists will serve as a quick, intuitive approach.

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

8.  Select AS_DateQtrParams_Support within the selector, as shown in Illustration 3.


Illustration 3: Select the AS_DateParamsQtrSupport Dataset

The Dataset opens.

9.  Underneath the syntax appearing within Query pane of the Query Designer, type the following WHERE clause:

<WHERE
   (STRTOMEMBER(@DateCalendarYear,
CONSTRAINED))

Here we are filtering, via the MDX WHERE clause, the data we are retrieving for Quarter picklist support to only those quarters that are properly included for the year we specify when prompted at runtime.

The Query pane, with our syntax addition, appears as depicted in Illustration 4.


Illustration 4: The Query, with Newly Added WHERE Clause ...

We also need to declare the new query parameter (seen as the @DateCalendarYear placeholder within the WHERE clause) by taking the next step.

10.  Click the Query Parameters button atop the Data pane (the second button to the left of the Execute Query (!) button).

The Query Parameters dialog opens.

11.  Click the leftmost box in the first row of the dialog, labeled Parameter, to activate it.

12.  Type the following into the box:

DateCalendarYear

13.  Click the next box to the right, labeled Dimension, to activate its selector button.

14.  Select Date within the selection list that appears, as shown in Illustration 5.


Illustration 5: Select the Date Dimension ...

15.  Click the next box to the right, labeled Hierarchy, to activate its selector button.

16.  Select Date.Calendar within the selection list that appears, as depicted in Illustration 6.


Illustration 6: Select the Date Dimension, Calendar Hierarchy ...

17.  Click the box to the far right, labeled Default, to activate its selector button.

18.  Using the “+” sign to the immediate left of the All Periods level in the hierarchical tree that appears, expand to the next level, Calendar Years.

19.  Click CY 2004 to highlight / select it, as shown in Illustration 7.


Illustration 7: Select CY 2004 as the Default Year ...

20.  Click OK to accept our selection and to close the selector.

The Query Parameters dialog appears, with our settings, as depicted in Illustration 8.


Illustration 8: The Query Parameters Dialog, with Our Input

21.  Click OK to accept input and to dismiss the dialog.

Selecting a default for the new Query Parameter allows us to execute the parameter immediately, within the MDX Query Builder, as the Default value is substituted for the parameter placeholder in the query syntax at runtime.

22.  Click the Execute Query (!) button to run the query against the Analysis Services data source, as shown in Illustration 9.


Illustration 9: Execute the New Query ...

The query runs, and the data is returned, as depicted in Illustration 10.


Illustration 10: The New Dataset Appears ...

As we can see, we have the workings of a good support dataset for the cascading Quarter parameter picklist – the captions and values correctly reflect the fact that our cube contains data within only three quarters for CY 2004. (This is why I chose CY 2004 as the default, to show the effect on the dataset, and therefore to demonstrate the effectiveness of its use to support the properly cascading parameter picklist.)

Let’s make a similar adjustment to the Month parameter picklist support query.

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

24.  Select AS_DateMoParams_Support within the selector, as shown in Illustration 11.


Illustration 11: Select the AS_DateParamsMoSupport Dataset

The Dataset opens.

25.  Underneath the syntax appearing within Query pane, type the following WHERE clause:

WHERE
   (STRTOMEMBER(@DateCalendarQuarterofYear,
CONSTRAINED))

We are, of course, filtering once again, via the MDX WHERE clause. This time, we are restricting the data we are retrieving for Month picklist support to only those months that are properly included within the quarter we specify when prompted at runtime. We also need, as we noted when adding a parameter to the Quarter parameter query earlier, to declare the new query parameter (seen as the @DateCalendarQuarterofYear placeholder within the WHERE clause), which we will accomplish by taking the next step.

26.  Click the Query Parameters button atop the Data pane, as we did earlier.

The Query Parameters dialog opens, once again.

27.  Click the leftmost box in the first row of the dialog (“Parameter”) to activate it, as before.

28.  Type the following into the box:

DateCalendarQuarterofYear

29.  Click the next box to the right (“Dimension”), as we did with the last dataset, to activate its selector button.

30.  Select Date within the selection list that appears, exactly as we did in the previous dataset Query Parameters dialog.

31.  Click the next box to the right (“Hierarchy”), to activate its selector button, as we did earlier.

32.  Select Date.Calendar within the selection list that appears, again precisely as we did in the previous dataset Query Parameters dialog.

33.  Click the box to the far right of the row (“Default”), as before, to activate its selector button.

34.  Using the “+” sign to the immediate left of the All Periods level in the hierarchical tree that appears, expand to the next level, Calendar Years.

35.  Expand Calendar Year CY 2004, exposing the two Semester level members H1 CY 2004 and H2 CY 2004.

36.  Expand Semester H1 CY 2004, exposing its Quarter level members Q1 CY 2004 and Q2 CY 2004.

37.  Click Q2 CY 2004 to highlight / select it, as depicted in Illustration 12.


Illustration 12: Select Q2 CY 2004 as the Default Quarter ...

38.  Click OK to accept our selection and to close the selector.

The Query Parameters dialog appears, with our settings, as shown in Illustration 13.


Illustration 13: The Query Parameters Dialog, with Our Input

39.  Click OK to accept input and to dismiss the dialog.

40.  Click the Execute Query (!) button, as we did earlier, to run the query against the Analysis Services data source.

The query runs, and the data is returned, as depicted in Illustration 14.


Illustration 14: The New Dataset Appears ...

We have added a support dataset for the cascading Month parameter picklist – the captions and values correctly reflect the months that belong to our selected default quarter, Q2 CY 2004.

We have extended the parameter support structures we created within Analysis Services in Support Parameterization from Analysis Services to enable cascading picklists. We are now ready to verify the effectiveness of our solution. We will verify that the date-related Report Parameters supported by objects within the Analysis Services layer of our integrated BI solution – perform as expected at runtime, providing cascading picklists as a part of operation.

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

Let’s preview the report to inspect the results of our handiwork.

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 Year selector.

4.  Select CY 2004 within the Year parameter picklist.

The third prompt, Quarter, becomes enabled.

5.  Select CY Q2 from the Quarter parameter picklist.

We notice at this stage that cascading is occurring within the Quarter parameter picklists, as we see only the three existing quarters for CY 2004 appear in the dropdown selector list, as shown in Illustration 15.


Illustration 15: Only the CY 2004 Quarters Appear ...

The fourth, and final, prompt, Quarter, becomes enabled.

6.  Select the month of June in the Month parameter picklist.

Here we see further evidence that the parameter picklists are cascading, as desired. We see only the three months that reside within CY Q2 of CY 2004 appear in the dropdown selector list, as depicted in Illustration 16.


Illustration 16: Only the Q2 Months for CY 2004 Quarters Appear ...

As we noted in Support Parameterization from Analysis Services, where we first established our Analysis Services - supported date parameters within the report, it occurs to us at this point in running the report that intelligent defaults would give our consumers a better runtime experience. We will address this item in a subsequent article devoted specifically to the subject.

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 shown in Illustration 17.


Illustration 17: The Report Parameters Operate as Expected ...

Our verification process has demonstrated the effective support of cascading picklists for our Report Parameters, which are themselves supported by objects within the Analysis Services layer of our BI solution. We will extend our examination of Analysis Services supported parameterization yet further in the next article of our series, 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 article that follows, 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 finished with the report, 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 the next article within our series.

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

Conclusion ...

In this article, we continued an extended examination of Parameters in Reporting Services, which we began in Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I, and Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II. Having focused upon the support of parameterization solely from within Reporting Services in Reporting Services: Customize Automatically Created Parameter Support Objects and Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets, we then turned our attention to supporting parameterization from outside Reporting Services. In the article preceding this one, Support Parameterization from Analysis Services, we established rudimentary Analysis Services - supported parameters within the report.

Working with the basic OLAP report we created in Support Parameterization from Analysis Services, we set out to further enhance the Analysis Services - based parameter support by making the modifications required to support cascading picklists for those parameters at runtime. In examining the requested refinement, we began with a brief review of the concept of parameterization in general, touching upon options for supporting report parameterization among the three primary layers of the integrated Microsoft business intelligence solution. We then returned to the report we began in our previous article, Support Parameterization from Analysis Services, and discussed, and then implemented, an approach, for adding cascading behavior to our Analysis Services - based parameter picklists.

Throughout our article, we overviewed how the various components of the parameter support solution we proposed are tied together. Working with the components we had already established within Analysis Services and Reporting Services to support our runtime parameters, we focused our practice session upon the enhancements necessary to support cascading parameter picklists at runtime. Finally, we previewed the report to observe the effectiveness of our solution in runtime action, and looked ahead to our next article, where we will examine the addition of intuitive parameter defaults, for greater runtime user-friendliness and overall operational efficiencies.

» 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