Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
January 16, 2007
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.
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 organizations 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:
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.