dcsimg

MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist - Page 8

November 14, 2005

Verify the Operation of the Report Parameter

Our final steps will consist of a couple of quick tests of the report, to ascertain that its parameter meets the specifications of the information consumers, as well as to give us the opportunity to gage the effectiveness of the displayed description we added in our last steps.

1.  Click the Preview tab to execute the RS022_MULTI_VAL_PARAM report.

The report executes, and appears as partially shown in Illustration 25.

Click for larger image

Illustration 25: Executed Report, Defaulted to "All" Parameter Selections (Partial View)

We see the report appear, executed by default with "all" parameter values selected. We can verify this, not only by our label, which lists the three Product Families as selected, but also by looking at what selections have been made within the parameter picklist.

2.  Click the dropdown selector button to the right of the Product Family(ies) parameter.

The picklist is exposed, as depicted in Illustration 26.

Click for larger image

Illustration 26: The New Parameter Picklist

3.  Uncheck the checkboxes for the Food and Non-Consumables families in the picklist, leaving only the Drink family selected, as shown in Illustration 27.

Click for larger image

Illustration 27: Specify Only the Drink Family ...

4.  Click the View Report button to refresh the report.

The report executes and returns data relating to the Drink Product Family only, as depicted in Illustration 28.


Illustration 28: The Report Filtered to the Drink Product Family ...

And so we see that our Report Parameter does, indeed, function correctly with multiple input values. Moreover, our dynamic title serves us well in displaying our parameter input, making it easy for any consumer to understand precisely what data is being displayed.

5.  Select File --> Save All from the main menu to save all work to the present.

6.  Select File --> Exit to exit MSSQL Server Business Intelligence Development Studio when ready.

We have corroborated the correct operation of the newly incorporated features. We present the results to the Operations consumers, who express satisfaction that the changes do, indeed, meet specifications.

Conclusion...

In this article, we extended previous examinations of report parameters to focus upon a commonly encountered business need: the capability to select multiple input values within a Report Parameter. We noted that this popular feature is supported in Reporting Services 2005. As a part of preparing the backdrop for a practice exercise surrounding the setup of multiple selection support within a Report Parameter, we got some hands on exposure to the migration of a Reporting Services 2000 report, with a simple Report Parameter in place, to Reporting Services 2005. The steps we took within the context of the sample Reporting Services 2000 report gave us a feel for what is involved in bringing the new capability to select multiple values to existing Reporting Services 2000 reports in our own environments. We emphasized that, with the introduction of multi-value parameter input in Reporting Services 2005, Reporting Services closes yet another of the few remaining gaps between itself and other, once dominant enterprise reporting solutions.

To set the stage for our discussion and practice session, we presented a business requirement based upon the needs of a hypothetical client, which we then addressed within a migration of the Reporting Services 2000 sample report to the current version of Reporting Services. We prepared for our practice session by creating a project within Reporting Services, and then created a "clone" report, within which we performed our exercises. We upgraded the report to Reporting Services 2005, and then removed the existing Report Parameter, together with a filter that referenced it, to allow for recreation of both with provision for the new multi-value input capabilities.

Once we had put in place the new Report Parameter with multiple value selection support, we added a textbox to the report containing an expression to display our parameter picklist selections on the face of the report. We introduced the Join() function and illustrated how to display the parameter value array in a comma-delimited string to meet the specifications of our hypothetical information consumers. Finally, we verified the operation of our enhancements in a couple of tests of report operation.

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

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