## Tutorial 3: Creating and using report parameters

This tutorial provides instructions for building a report that lists products, their vendors, and quantities in stock. Rather than display all the products in stock, the report shows only products that need to be re-stocked. Because the number at which inventory is considered low can change with time, the report uses a report parameter that prompts the user to specify the minimum stock threshold when the user runs the report. The report also uses a report parameter that lets the user select a particular vendor or all vendors for the products that need re-stocking.

In this tutorial, you perform the following tasks:

- Create a new report.
- Build a data source.
- Build a data set.
- Lay out the data.
- Create a report parameter that prompts for a minimum product quantity.
- Create a report parameter that prompts for a vendor name.
- Edit the query.
- Create data set parameters and bind them to the report parameters.
- Test the report parameters.
- Provide the option to select all vendors.
- Create a title that uses the report parameter values.

### Task 1: Create a new report

1. In Navigator, right-click the My Reports project you created in the first tutorial, then choose New->Report.
2. On New Report, type the following text as the file name:

    Inventory.rptdesign

3. Choose Finish. The layout editor displays a blank report.

### Task 2: Build a data source

Before you begin designing your report in the layout editor, create a data source to connect your report to the Classic Models sample database.

1. Choose Data Explorer.
2. Right-click Data Sources, and choose New Data Source from the context menu.
3. Select Classic Models Inc. Sample Database from the list of data sources. Use the default data source name, then choose Next. Connection information about the new data source appears.
4. Choose Finish. BIRT creates a new data source that connects to the sample database. It appears within Data Sources in Data Explorer.

### Task 3: Build a data set

In this procedure, you build a data set to retrieve data from the Products table in the Classic Models database.

1. In Data Explorer, right-click Data Sets, and choose New Data Set.
2. On New Data Set, type the following text for Data Set Name:

    Products

3. Use the default values for the other fields, then choose Next.
4. On Query, type the following query. Alternatively, drag the fields from the Products table, and drop them in the text area:

    select productname,
    productvendor,
    quantityinstock
    from Products

5. Choose Finish to save the data set. Edit Data Set displays the columns you specified in the query, and provides options for editing the data set.
6. Choose Preview Results to confirm that the query is valid and that it returns the correct data. You should see the results that appear in Figure 12-20.

Figure 12-20 Data rows returned by the Products data set
7. Choose OK to save the data set.

### Task 4: Lay out the data

In this procedure, you create a layout that displays the data in a simple row-and-column format.

1. Drag the Products data set from Data Explorer, and drop it in the layout editor. BIRT Report Designer creates a table that contains all the data set fields and corresponding labels, as shown in Figure 12-21.

Figure 12-21 Layout editor, displaying product data in a table
2. Edit and format the labels in the table's header row (the first row):

1. Double-click PRODUCTNAME. Change the text to:

    Product Name

2. Press Enter to accept the change.

3. In Property Editor, in General properties, choose B to format the label text as bold text.
4. Similarly, edit the PRODUCTVENDOR and QUANTITYINSTOCK labels so that they appear as:

    Vendor
    Quantity In Stock

3. Preview the report. It should look like the one in Figure 12-22.

Figure 12-22 Report preview

### Task 5: Create a report parameter that prompts for a minimum product quantity

In this procedure, you create a report parameter that prompts the user to specify a minimum quantity of stock. The report will display only rows where the quantity in stock is less than or equal to the user-specified value.

1. Choose Layout to resume editing the report.
2. In Data Explorer, right-click Report Parameters, then choose New Parameter.
3. Specify the following property values for the report parameter:

1. In Name, type:

    RP_quantityinstock

2. In Prompt text, type the following text:

    Display products whose quantity in stock is less than or
    equal to this number

3. In Data type, select Integer.
4. In Display type, use the default, Text box.
5. In Default value, type:

    1000

Figure 12-23 shows the complete parameter definition.

Figure 12-23 Properties for RP_quantityinstock report parameter
4. Choose OK to save the report parameter.
5. In the layout editor, choose Preview. Enter Parameters appears, as shown in Figure 12-24. It displays the prompt text and the default parameter value that you specified when you created the report parameter.

Figure 12-24 Enter Parameters, displaying the RP_quantityinstock parameter
Choose OK. The report output shows all products. The report does not display only products with quantities less than or equal to 1000, because you have not yet bound the report parameter to the data set.

### Task 6: Create a report parameter that prompts for a vendor name

In this procedure, you create a report parameter that asks the user to select a particular vendor or all vendors for the products whose quantity in stock matches the user-specified value. You create a report parameter that appears as a list box, which displays all the vendor names.

1. Choose Layout to resume editing the report.
2. In Data Explorer, right-click Report Parameters, then choose New Parameter.
3. Specify the following property values for the report parameter:

1. In Name, type:

    RP_productvendor

2. In Prompt text, type the following text:

    Display products for this vendor

3. In Data type, select String.
4. In Display type, select List Box.
On New Parameter, List of value displays options for providing a list of values to the user, as shown in Figure 12-25. The figure also shows the properties you have specified for the report parameter so far.

Figure 12-25 Properties for RP_productvendor report parameter

4. Under List of value, choose Dynamic.
This option creates a list of values dynamically. BIRT retrieves the values from the data source when the report runs, which ensures that the values displayed are always current.
Under List of value, Data Set displays Products, which is the only data set created for the report so far.
5. Create a new data set to retrieve the vendor names to display in the list box's list of values:

1. Choose Create New, next to the Data set field.
2. On New Data Set, in Name, type the following name:

    Vendors

Choose Next.
3. On Query, type the following query:

    select productvendor
    from Products

4. Choose Finish to save the data set. Edit Data Set displays the columns you specified in the query, and provides options for editing the data set.
5. Choose Preview Results to confirm that the query is valid and that it returns the correct data. You should see the results that appear in Figure 12-26. Notice that some vendor names are listed multiple times.

Figure 12-26 Data rows returned by the Vendors data set
6. Choose OK to save the data set.

6. Specify the values to display in the list box:

1. On New Parameter, in Data set, choose Vendors, the data set you created in the previous step.
2. In Select value column, select PRODUCTVENDOR.
3. In Select display text, select PRODUCTVENDOR.

7. Specify how the values should be sorted:

1. Under Sort, in Sort by, select either Value Column or Display Text. In this tutorial, both provide the same values.
2. In Sort direction, select Ascending.
3. Deselect Allow Duplicate Values. Remember that the Vendors data set returned some vendor names multiple times. Deselecting the Allow Duplicate Values option displays each name once in the list box.
Figure 12-27 shows the complete definition of the RP_productvendor report parameter.

Figure 12-27 Complete definition of the RP_productvendor report parameter

8. Choose OK to save the report parameter.
9. In the layout editor, choose Preview. Enter Parameters appears, as shown in Figure 12-28. The RP_productvendor parameter appears as a list box with the first value in the list selected by default.

Figure 12-28 Enter Parameters, displaying both report parameters
10. View the values in the list box. The values are sorted in ascending alphabetical order, and there are no duplicate values.
Choose OK. The report output still shows all products because you have not yet bound the report parameters to the Products data set.

### Task 7: Edit the query

In this procedure, you edit the query in the Products data set so that it is dynamically updated at run time to use the values of the report parameters.

1. Choose Layout to resume editing the report.
2. In Data Explorer, right-click the Products data set, and choose Edit.
3. Edit the query to add a WHERE clause, as shown:

    select productname,
    productvendor,
    quantityinstock
    from Products
    where quantityinstock <= ?
    and productvendor Like ?

The WHERE clause contains two parameter markers, ?, which indicate where you want BIRT to insert the report parameter values at report run time. The Like operator is a SQL pattern-matching option. Using Like, you can replace the parameter marker with a value, such as A%, to return rows where the vendor name starts with A. In a later procedure, you see the flexibility of using the Like operator instead of the = operator.

### Task 8: Create data set parameters and bind them to the report parameters

In this procedure, you define two data set parameters that correspond to the ? markers in the query. You then bind each data set parameter to the appropriate report parameter. At run time, the data set parameters get the values from the report parameters, and pass the values to the query.

1. In Edit Data Set, choose Parameters. The Parameters page displays two placeholder data set parameters, which BIRT Report Designer created when you modified the query.
2. Define the first data set parameter:

1. Select the first parameter, then choose Edit.
2. On Edit Parameter, specify these values:

- In Name, type:

      DSP_quantityinstock

- In Data Type, select Integer.
- In Direction, select Input.
- In Linked to Report Parameter, select RP_quantityinstock. This option binds the data set parameter to the RP_quantityinstock report parameter.

Figure 12-29 shows the complete data set parameter definition.

Figure 12-29 Definition of the first data set parameter
3. Choose OK to save the data set parameter. The Parameters page displays the edited data set parameter.

3. Define the second data set parameter:

1. Select the second parameter, then choose Edit.
2. On Edit Parameter, specify these values:

- In Name, type:

      DSP_productvendor

- In Data Type, select String.
- In Direction, select Input.
- In Linked to Report Parameter, select RP_productvendor.

Figure 12-30 shows the complete data set parameter definition.

Figure 12-30 Definition of the second data set parameter
3. Choose OK to save the data set parameter. The Parameters page, shown in Figure 12-31, displays the edited data set parameter.

Figure 12-31 Parameters page, displaying the two data set parameters

4. Choose OK to save your changes to the Products data set.

### Task 9: Test the report parameters

In this procedure, you test that the query is properly updated with the report parameter values.

1. Choose Preview. Figure 12-32 shows the report output. This time, BIRT uses the default report parameter values, and generates the report without displaying Enter Parameters.

Figure 12-32 Report displays rows that match the default report parameter values
2. Choose Show Report Parameters to run the report with different parameter values. The report does not display any rows if none of the rows match the values you supply.

### 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 with 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--with 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. In Data Explorer, expand Report Parameters, right-click RP_productvendor, then choose Edit.
2. On Edit Parameter, under List of value, choose Static. This option lets you create user-defined values, whereas the dynamic option does not.
The properties under List of Value change to reflect the change from dynamic to static values.
3. Specify the values to display in the list box:

1. Choose Import Values. Import Values displays the first data 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 the >> button to import all values from the field.
Figure 12-33 shows the selections you make in Import Values.

Figure 12-33 Import Values, showing the data set field values to import
5. Choose OK. Edit Parameter displays the imported values in the Selection values table, as shown in Figure 12-34.

Figure 12-34 Edit Parameter, displaying the values that appear in the list box

4. Add a new value to display in the list box:

1. Choose New next to the Selection values table.
2. On New Selection Choice, as shown in Figure 12-35:

1. In Display Text, type:

      All Vendors

This text appears in the list box's list of values.
2. In Value, type:

      %

    Figure 12-35	 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:

      WHERE ...
      and productvendor Like %

This query returns all rows with any productvendor value.
3. Choose OK to save the new value definition.
On Edit Parameter, the new value appears at the bottom of the Selection values table.

5. 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.

6. 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.
3. Deselect Allow Duplicate Values.

7. Choose OK to save your changes to the report parameter.
8. Test the report parameter:

1. Choose Preview. Enter Parameters appears, as shown in Figure 12-36. All Vendors is the first value in the list, and this value is selected by default.

Figure 12-36 Enter Parameters, displaying the updated vendor parameter
2. Provide values for the parameters:

- For the first parameter, type:

      500

- For the second parameter, use All Vendors.

Choose OK.
The generated report should look like the one shown in Figure 12-37. The report displays products, supplied by any vendor, where the quantity in stock is less than or equal to 500.

Figure 12-37 Report displays rows that match the specified report parameter values

### Task 11: Create a title that uses the report parameter values

Report parameters can be used for purposes other than filtering data. In this procedure, you add a report title that displays a report parameter value.

1. Choose Layout to resume editing the report.
2. Insert a text element at the top of the report.
3. On Edit Text Item, in the field with the value Auto, select HTML from the drop-down list.
4. Type the following text:

    Products with inventory less than
    <VALUE-OF>params["RP_quantityinstock"]</VALUE-OF>

The <VALUE-OF> tag supports displaying a dynamic value. The expression params["RP_quantityinstock"] refers to the RP_quantityinstock report parameter.
5. Choose OK to save your edits to the text element.
6. Preview the report. The report title displays:

    Products with inventory less than 500