dcsimg

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

July 27, 2004

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.

Conclusion...

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.

MSSQL Server Reporting Services
Introducing the Tablix Data Region: Basic Grouping Concepts
Introducing the Tablix Data Region in Reporting Services 2008
100% Stacked Column Chart for Analysis Services Data
XY (Scatter) Chart for Analysis Services Data
Simple Doughnut Chart for Analysis Services Data
Exploded Pie Chart for Analysis Services Data
Stacked Bar Chart for Analysis Services Data
Line Chart for Analysis Services Data
Stacked Column Chart for Analysis Services Data
A More Advanced Pie Chart for Analysis Services Data
Simple Pie Chart for Analysis Services Data
Simple Bar Chart for Analysis Services Data
Simple Column Chart for Analysis Services Data
Introducing Reporting Services Charts for Analysis Services
Mastering OLAP Reports: Parameterized Grouping
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Top" Items with the MDX TopCount() Function, Part I
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part II
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part I
Support Parameterization from Analysis Services - Parameter Defaults
Parameterization from Analysis Services - Cascading Picklists
Support Parameterization from Analysis Services
Parameter Support Objects, Pt II: Support OLAP Parameter Defaults with Datasets
Reporting Services: Customize Automatically Created Parameter Support Objects
Snapshot Reports II: SQL Server Management Studio Perspective
Snapshot Reports I: Report Manager Perspective
Report Execution Caching II: Report Manager Perspective
Report Execution Caching I: SQL Server Management Studio Perspective
Report Session Caching in Reporting Services 2005
Black Belt Administration: Reporting Services Configuration Manager
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. II
Black Belt Components: Support Simple Navigation with a Document Map
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Pt. I
Black Belt Components: Interactive Sorts within a Matrix Data Region
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
BlackBelt Administration: Linked Reports in SQL Server Management Studio
BlackBelt Administration: Linked Reports in Report Manager
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Report Builder: Creating a Report Model
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part II
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics, Part 1
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Interactive Sorting Within Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist
MSSQL Server Reporting Services : Mastering OLAP Reporting: Drilling Through Using MDX
MSSQL Server Reporting Services: Mastering OLAP Reporting: Relationally-Based Picklists for OLAP Reporting
MSSQL Server Reporting Services: Mastering OLAP Reporting: Extending Conditional Formatting: SWITCH and Drilldown Defaults
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Chart Presentation Nuances
MSSQL Server Reporting Services: Mastering OLAP Reporting: Percent of Total - Two Perspectives
MSSQL Server Reporting Services : Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Sorting with Parameters
MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout
MSSQL Server Reporting Services : Black Belt Administration: Execution Log Performance and Audit Reports
MSSQL Server Reporting Services: Black Belt Administration: Prepare the Execution Log for Reporting
MSSQL Server Reporting Services: Black Belt Components: Ad Hoc Conditional Formatting for OLAP Reports
MSSQL Server Reporting Services : Black Belt Components: Manage Nulls in OLAP Reports
MSSQL Server Reporting Services: Reporting Services Basics: Create a Reusable Template Report
MSSQL Server Reporting Services: Master Chart Reports: Track Exchange Rates in a Line Chart
MSSQL Server Reporting Services: Master Chart Reports: Pie Charts in Reporting Services
MSSQL Server Reporting Services: Mastering OLAP Reporting: Cascading Prompts
MSSQL Server Reporting Services: Managing Reporting Services: Data-driven Subscriptions, and External Data Sources for Subscriber Data
MSSQL Server Reporting Services: Managing Reporting Services: Report Execution and Standard Subscriptions
MSSQL Server Reporting Services: Managing Reporting Services: Data Connections and Uploads
MSSQL Server Reporting Services: The Authoring Phase: Overview Part II
MSSQL Server Reporting Services: The Authoring Phase: Overview Part I
MSSQL Server Reporting Services: A New Paradigm for Enterprise Reporting








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers