Parameterization from Analysis Services - Cascading Picklists
March 26, 2008
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.
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:
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 organizations 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: 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.
We are now ready to begin working with the existing report to add support for cascading picklists.