Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 16, 2007

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

By William Pearson

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM