MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist - Page 5
November 14, 2005
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.
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
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.
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.
The Report Parameters dialog, where we define parameters for the report, appears, as shown in Illustration 16.
(For the sake of easy comparison, I include the dialog - for the same parameter - from the Reporting Services 2000 environment in Illustration 17.)
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.
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.
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.