MSSQL Server Reporting Services: Mastering OLAP Reporting: Multiple Value Selection in a Parameter Picklist - Page 7
November 14, 2005
Create a New Filter to Allow Passage of Multiple Values via the Report Parameter
We will now access the Properties dialog for the matrix once again, to create a filter based upon the new Report Parameter. The filter will be similar to the one we deleted earlier, but with one important difference, as we shall see.
1. 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, as we did earlier.
2. Right-click the upper left corner of the matrix.
3. Select Properties from the context menu that appears, as we did in the preparation section.
The Matrix Properties dialog opens, defaulted to the General tab.
4. Click the Filters tab.
5. Type / select the syntax shown in Table 2 into the indicated fields of the top row of the filter list.
Table 2: Filter Expression for Parameter with Multiple Value Input
NOTE: If you add the Value within the Edit Expression dialog by clicking on Parameters and selecting the single parameter offered (instead of simply typing in the value shown in Table 2 above), be sure to the remove the "(0)" that is appended to the Value expression.
The Filter tab of the Matrix Properties dialog appears, with our input, as shown in Illustration 21.
Click for larger image
The difference in the above filter definition and that of its Reporting Services 2000 predecessor is the use of the "IN" operator (before it was "="). "IN" is new in Reporting Services 2005, and is required to enable the passage of multiple selections within a Report Parameter.
6. Click OK to accept the new filter expression, and to close the Matrix Properties dialog.
We return to Layout view, from which we will accomplish a final "finesse" item. We will make the report display more consumer-friendly, in the next section, before previewing the results of our handiwork thus far.
Display Prompt Variables in the Report
Once we give information consumers the capability to select multiple values for parameter input, a somewhat concomitant need arises quite naturally: the need to display upon the face of the report the parameter value selection we have made prior to its execution. Such a "label" makes it apparent to the reader exactly what the report is intended to convey. The hypothetical consumer group in this article has had the foresight to ask for this, but oftentimes it is incumbent upon us as consultants (internal or external) to be preemptive in adding value, especially in cases where consumers are new to the applications within which we are designing / developing, and are not thus likely to be able to make informed requests.
Once-dominant reporting applications, such as Cognos Impromptu and a host of others, make a "prompt variable" object, along with other intuitive report objects (such as page number, report file name, and the like), available for easy inclusion in a report by the author at design time. Reporting Services allows us to use expressions to accommodate this, and virtually any other such need that we can imagine, as we shall see in the following steps.
1. In Layout view, right-click the textbox containing the title FoodMart Sales (in the upper left corner of the report).
2. Select Expression ... from the context menu that appears, as depicted in Illustration 22.
The Edit Expression dialog appears.
3. Within the upper expression box, replace the existing expression ("FoodMart Sales") with the following:
="FoodMart Sales for: "&Join(Parameters!pX_MultiVal_ProductFamily.Value, ", ")
The upper input section of the Edit Expression dialog appears, with our replacement expression, as shown in Illustration 23.
The Join() function we have used works well for displaying the multiple parameter values, as we shall see, because it is designed to return a string created by joining (hence the function name) multiple strings contained within an array. The parameter values are returned in just such an array. If we wished to display any single member value within that array, we might do so by specifying the value under consideration in a fashion similar to the following, which would specify the "first of the values" within a group of one or more selections.
The syntax for Join() is as follows:
Sourcearray would represent, in our immediate instance, the one-dimensional array of parameter values returned, based upon our selection in the picklist (we will see examples in the next section), which we seek to join via the function for display in the report. The delimiter, an optional part of the function, represents a string character we can use to separate the substrings in the returned string of values joined by the function. We have supplied a comma ("',") combined with a single space in the expression input above, simply to allow for the generation of a comma / space delimited string, to make for easy reading.
4. Click OK to accept our new expression and to close the Matrix Properties dialog.
5. With the same textbox still selected, select "12" in the Font Size selector within the Report Formatting toolbar beneath the main menu.
6. Click the Bold ("B") button, to the right of the Font Size selector, to make the new report title bold.
The report appears in Layout view, with the formatting settings we have adjusted circled in red, as partially depicted in Illustration 24.
We will see our handiwork in action in the next section.