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 5

By William Pearson

Upgrade the New Report to Reporting Services 2005 and Finalize Preparation

Upgrading a Reporting Services 2000 report to the new version is as simple as opening the reports, and then indicating our willingness to perform the upgrade, as we shall see. Let's open the new RS022_MULTI_VAL_PARAM report, to verify its operation, as well as to make a few observations on its existing Report Parameter. We will then prepare the report to take advantage of the new multiple value parameter capabilities offered within Reporting Services 2005.

1.  Right-click RS022_MULTI_VAL_PARAM.rdl in the Solution Explorer.

2.  Select Open from the context menu that appears, as shown in Illustration 12.

Illustration 12: Opening the New Report ...

A message box appears, warning us that the report file must be converted to the current report definition format, and that, by making the conversion, we will sacrifice the file's backward compatibility with previous versions of Reporting Services. The message box appears as depicted in Illustration 13.

Click for larger image

Illustration 13: Notification of the Need For, and Consequences of, File Conversion

3.  Click Yes to convert the report file to Reporting Services 2005.

RS022_MULTI_VAL_PARAM opens in Layout view.

4.  Click the Preview tab to execute the report.

RS022_MULTI_VAL_PARAM executes, and appears on the Preview tab, where we can see the operational Report Parameter picklist when we click on the selector button. In this parameter, created within the more limited capabilities of Reporting Services 2000, we can select a single Product Family, as shown in Illustration 14.

Illustration 14: We Can Only Select a Single Picklist Value ...

As we have discussed, the information consumers with whom we are working have asked that we endow the report with a parameter that supports the selection of multiple values in the picklist. Because this is supported in the current version of Reporting Services, we have only to make adjustments in two places in the Layout view. To illustrate the procedure "from scratch," for the benefit of those who are seeking guidance in creating such a report parameter for the first time, we will eliminate the existing parameter and an associated filter reference, and create the parameter anew in our sample report.

5.  Click the Layout tab to return to the Layout view.

6.  Select Report --> Report Parameters from the main menu atop the Report Designer, as depicted in Illustration 15.

Illustration 15: Select Report --> Report Parameters from the Main Menu

The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 16.

Illustration 16: The Report Parameters Dialog

(For the sake of easy comparison, I include the dialog - for the same parameter - from the Reporting Services 2000 environment in Illustration 17.)

Illustration 17: The Report Parameters Dialog for the Previous Version

7.  In the Parameters list, on the left side of the dialog, click the single entry, ProductFamily, to select it.

8.  Click the Remove button underneath the Parameters list.

The ProductFamily parameter is removed from the list.

9.  Click OK to accept our removal of the parameter.

The now empty Report Parameters dialog closes, and we are returned to the Layout view of the report.

Having removed the sole Report Parameter, we now must delete a remaining reference to it, which we can access via the Properties dialog for the matrix.

10.  Click at some point within the label textbox of the report (containing the label Foodmart Sales), to make the row and column headers of the matrix data region visible.

11.  Right-click the upper left corner of the matrix. (If the headers disappear as you touch them with the cursor, you should still see a faint outline of the matrix.)

12.  Select Properties from the context menu that appears, as shown in Illustration 18.

Illustration 18: Accessing the Matrix Properties

The Matrix Properties dialog opens, defaulted to the General tab.

13.  Click the Filters tab.

We note that, within the top row of the Filter list, we have the expression detailed in Table 1.







Table 1: Filter Expression for Parameter with Single Value Input

14.  Click the Value field of the single occupied row to select it.

15.  Click the Delete button to delete the reference to the parameter, as indicated in Illustration 19.

Illustration 19: Select and Delete the Parameter Reference

The remaining reference to the now-deleted parameter is itself deleted.

16.  Click OK to accept our changes and to close the Matrix Properties dialog.

Having completed the preparation phase of our practice session, we are now ready to undertake creation of a Report Parameter that meets the needs specified by the information consumers for multiple value input.

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