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 Nov 14, 2005

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

By William Pearson

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.


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

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