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 Jul 27, 2004

MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts - Page 10

By William Pearson

Preview the Report

Let's preview operation of the report, supplying parameters as we go, in order to observe the cascading nature of the prompts.

1.  Click the Preview tab of the Report Designer.

The report parameters appear in the heading area of the Preview tab. The first parameter, Country, appears in an enabled state, because it is the first in the list of report parameters we created as a set.

2.  Select USA in the selector to the immediate right of the Country label, as depicted in Illustration 39.

Click for larger image

Illustration 39: Select USA in the Country Parameter Selector

As soon as USA is selected, the State selector is enabled. When we click the State selector, we see only the states (there are three States in which the FoodMart organization operates) that coincide with our country selection, USA. This is the proper action of a cascading parameter.

3.  Select WA in the selector to the immediate right of the State label, as depicted in Illustration 40.

Illustration 40: Selecting WA from the Three USA States

Selecting the State parameter enables the City picklist. We note, too, that the cities are, indeed, limited to those in the state of Washington.

4.  Select Seattle from the City selector.

5.  Select Store 15, the only Store that appears in the Store picklist corresponding to Seattle.

Our complete selection using the cascading picklists appears as shown in Illustration 41.

Illustration 41: Our Compete Selection via Cascading Picklists

6.  Click the View Report button, atop the Preview tab, and to the right of the parameter selectors.

The report appears, as depicted in Illustration 42. (The View Report button is circled for easy identification).

Illustration 42: The Report Appears .... (75% Zoom)

7.  Exit the Report Designer environment and Visual Studio.NET, saving report file and project when prompted, when desired.

Through the forgoing steps, we have met the requirements of the information consumers within the FoodMart Marketing department. We have provided a cascading, prompted parameter set, based upon geographical location of the organization's stores, within a basic report that focuses on consolidated product revenues. The fact that the Marketing team already had in place a report whose general attributes were satisfactory made our job a bit easier than in a scenario where we might have had to design and create the entire report from scratch, in addition to engineering the cascading parameter components. We had to perform only small modifications to the report, primarily to tailor its presentation to an integrated view of product revenue, before adding the parameterization requested by the intended audience.


In this article, we explored an approach to providing cascading prompts, to meet the illustrative business requirement of a group of hypothetical information consumers. We initially made a copy of a report that the consumers already liked, and modified the clone to show an integrated view of the revenues associated with the FoodMart products. We then began the creation of the various components involved in supporting cascading picklists.

We mentioned along the way that much of the work we were performing might be reused, provided we built the functionality into calculated members within the cube underlying such a report. We completed the definition of the calculated members involved at the report level, for purposes of our practice exercise, to isolate the rudimentary concepts. We noted other benefits of using parameters, including easier, more consistent reporting, as well as greater centralization of functionality and maintenance; a parameterized report can be used to retrieve a wider range of information, meaning a dramatic reduction in the number of individual reports in the library that, in essence, accomplish the same objectives for individual locations. Finally, after constructing the datasets and report parameters required to support cascading prompt picklists, we executed the report and verified its operation as a whole.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Reporting Services Forum.

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