Task 10: Provide the option to select all vendors
The query and the design of the report parameters require the user to specify a minimum stock quantity and a specific vendor. The report displays only products meeting the specified minimum stock quantity and for the selected vendor. In this procedure, you provide the user with the option of selecting all vendors, so that the report displays all products—supplied by any vendor—that meet the specified minimum quantity. To provide this option, you modify the RP_productvendor report parameter to display an All Vendors value, and to send the appropriate value to the query.
1 Choose Layout to resume editing the report.
2 In Data Explorer, expand Report Parameters, right-click RP_productvendor, then choose Edit.
3 In Edit Parameter, under Selection list values, choose Static. This option supports creating user-defined values, whereas the dynamic option does not.
The properties under Selection list values change to reflect the change from dynamic to static values.
4 Specify the values to display in the list box.
1 Choose Import Values. Import Values displays the first data set in the report, Products, and the values of the first field in the data set, PRODUCTNAME.
2 In Select Data Set, select Vendors.
3 In Select Column, select PRODUCTVENDOR. Import Values displays the values for the field.
4 Choose >> to import all values from the field.
Figure 13‑47 shows the selections you make in Import Values.
Figure 13‑47 Import Values showing the data set field values to import
5 Choose OK. Edit Parameter displays the imported values in the Values table, as shown in
Figure 13‑48.
Figure 13‑48 Edit Parameter displaying the values that appear in the list box
5 In Edit Parameter, add a new value to display in the list box.
1 Choose New next to the Values table.
2 In New Selection Choice:
In Display Text, type the following text:
All Vendors
In Value, type the following character:
%
Figure 13‑49 Definition of a new value
In SQL, % is a wildcard character that matches any sequence of characters. When the user selects All Vendors, the WHERE clause in the Products query is updated as follows:
WHERE ...
and productvendor Like %
This query returns all rows that have any productvendor value.
Choose OK to save the new value definition.
In Edit Parameter, the new value appears at the bottom of the Values table.
6 Designate the new value as the default value.
1 In the Selection values table, scroll to the bottom of the list.
2 Select the % value.
3 Choose Set as Default.
7 Specify how the values should be sorted when presented to the user.
1 Under Sort, in Sort by, select Value Column. This sort displays All Vendors at the top of the list in the list box because the value % appears before A.
2 In Sort direction, select Ascending.
8 Choose OK to save your changes to the report parameter.
9 Test the report parameter.
1 Choose Preview. Enter Parameters appears, as shown in
Figure 13‑50. All Vendors is the first value in the list, and this value is selected by default.
Figure 13‑50 Enter Parameters displaying the updated vendor parameter
2 Provide values for the parameters.
For the first parameter, type the following number:
500
For the second parameter, use All Vendors.
Choose OK.
The generated report should look like the one shown in
Figure 13‑51. The report displays products, supplied by any vendor, where the quantity in stock is less than or equal to 500.
Figure 13‑51 Report displays rows that match the specified report parameter values
