As described previously, a report parameter enables or requires users to specify a value that determines the data to include in a report. When they run a report that uses a JDBC data source, BIRT updates the SQL query with these values before retrieving any data. The data source then returns only the rows that match the user-specified values.
To enable users to filter database data, complete the following tasks in the recommended order. For detailed information about these tasks, see the corresponding topics later in this section.
Create report parameters to prompt the user to specify values that determine what rows to retrieve.
Insert parameter markers in the SQL query.
Create a data set parameter to supply a value for each parameter marker.
Bind the data set parameter to the report parameter, so that the data set parameter gets the user-specified value from the report parameter and passes it to the SQL query.
Determine how to present the report parameters.
Test the report parameters.
Creating a report parameter
Report parameters provide a mechanism for passing values into a report. You can create a report parameter to prompt the report user to specify a value for a particular field. Alternatively, you can use a hidden report parameter to pass a value into the report on a programmatic basis. For instance, a hidden parameter can be used to pass a customer’s account code into a report if you do not want a customer to look at any account data but her own.
Report parameters have global scope, which means they are available to the entire report and any report element can access a report parameter’s value. Report parameter values set by the user are read and saved into the report document when the report document is first generated. Once the report document generates, parameters values in the report document cannot be changed. To enable user filtering, bind the report parameter to a corresponding data set parameter.
When creating a report parameter, you perform two main tasks:
Define the basic properties of the parameter: its name and data type.
Design the presentation of the parameter to the user. Consider the following tasks:
Specifying whether users type a value or select a value from a list box or radio buttons
Providing a default value
Displaying a descriptive text prompt
Organizing report parameters in logical groups
How to create a basic report parameter
1 In Data Explorer, right-click Report Parameters, and choose New Parameter.
1 In Name, type a name for the parameter. It is good practice to use a prefix, such as RP, in the name to help distinguish report parameters from other parameter types, such as data set parameters. For example, a report parameter used to filter on a quantityinstock field might be named RP_quantityinstock. The value you specify in Name appears as the prompt to the report user if you do not specify a value for the next property, Prompt text. Rather than specify only a Name value for the parameter, you should use a technically descriptive value in Name, and provide a user‑friendly value in Prompt text.
2 In Data type, select a data type for the parameter.
The data type that you select for the report parameter determines the formatting options that are available if you choose to provide a default value or a list of values for the report parameter. The data type of the parameter does not have to match the data type of the field in the data source. Values in an orderID field, for example, can be stored as integers in the data source, but the report parameter that is associated with this field can be of string type.
Choose OK.
The parameter appears under Report Parameters in Data Explorer.
Inserting a parameter marker in the SQL query
After creating a report parameter, insert a parameter marker in the WHERE clause of the SQL query of the data set. The parameter marker, represented by a question mark (?), indicates where you want BIRT to insert the parameter value.
For example, to ask the user to specify the threshold inventory quantity for a restocking report, insert the ? parameter marker in the WHERE clause, as shown in the following example:
WHERE quantityinstock < ?
When the report runs, if the user specifies 500 for quantityinstock, BIRT replaces ? with 500 and sends the following filter condition to the data source:
WHERE quantityinstock < 500
If you write a filter condition that uses more than one field, consider the interaction of the field values. Each of the following WHERE clauses, for example, returns a different set of rows:
WHERE quantityinstock < ? AND productvendor = ?
WHERE quantityinstock < ? OR productvendor = ?
The first clause returns only those rows in which both the quantityinstock and the productvendor values match the values that replace the ? markers. The second clause returns more rows. It returns rows in which the quantityinstock value is less than the value that replaces the quantityinstock ? marker and rows in which the productvendor value matches the value that replaces the productvendor ? marker.
Before completing all of the steps to enable filtering, test the filter conditions by specifying actual values in the WHERE clause to verify that the results meet your expectations.
SQL supports many options and operators for specifying filter conditions. For complete information about writing WHERE clauses and SQL statements in general, consult a book about SQL.
How to insert a parameter marker in the SQL query
This procedure assumes that you already created a data set.
1 In Data Explorer, right-click the data set for which you want to edit the query, then choose Edit. Edit Data Set displays the query.
2 Add a WHERE clause with one or more parameters, as shown in the following examples:
WHERE quantityinstock < ?
WHERE quantityinstock <= ? AND productvendor = ?
WHERE quantityinstock <= ? OR productline LIKE ?
Figure 13‑3 shows an example of a query with two parameter markers.
Figure 13‑3 Query with two parameter markers
At this point, you are ready to define a data set parameter for each ? parameter marker. The next section describes this task.
Defining a data set parameter and binding it to the report parameter
A data set parameter passes a value that replaces the ? parameter marker in the WHERE clause of the query when the query is run. You must define one data set parameter for each parameter marker in the query’s WHERE clause. If you do not, BIRT displays an error.
The order of the data set parameters is critical. SQL uses the positions of the ? parameter markers in the WHERE clause to determine which data set parameter matches which ? marker. For example, you must define two data set parameters if you specify the following WHERE clause:
WHERE quantityinstock < ? and productvendor LIKE ?
The first data set parameter must pass a value to quantityinstock < ?, and the second parameter must pass a value to productvendor LIKE ?. In Figure 13‑4, the order of these parameters matches the order of the parameter markers in the example WHERE clause.
If you change the WHERE clause, you must update the data set parameters accordingly. For example, if you change the order of the parameter markers in the WHERE clause, you must change the order of the data set parameters. If you remove a parameter marker, you must delete the corresponding data set parameter.
Figure 13‑4 Edit Data Set displaying two data set parameters
How to define a data set parameter
This procedure assumes that you already inserted a parameter marker in the SQL query.
1 In Data Explorer, double-click the data set for which you want to create parameters. Edit Data Set displays the query.
2 Choose Parameters. Edit Data Set displays the Parameters page. This page displays placeholder data set parameters, which BIRT Report Designer creates for each ? parameter marker in the WHERE clause of the query.
3 Edit each data set parameter. Select a parameter and choose Edit.
4 In Edit Parameter, specify the following information:
1 In Name, type a name for the data set parameter. It is good practice to use a prefix, such as DSP, to differentiate the data set parameter from other parameter types, such as report parameters.
2 In Data type, select a data type for the parameter.
3 In Direction, choose Input. This value means that the parameter is an input parameter.
4 In Linked To Report Parameter, select the report parameter to bind to this data set parameter.
Figure 13‑5 shows an example of a data set parameter definition where the data set parameter, DSP_quantityinstock, is linked to a report parameter, RP_quantityinstock.
Figure 13‑5 Data set parameter definition
Choose OK.
5 Repeat steps 3 and 4 to define additional data set parameters for any other parameter markers in the SQL query.
6 To save the changes to the data set, choose OK.
7 Test the parameters to verify that the query is updated with user-specified values and that the report shows the results you expect.
1 Choose Preview.
2 If Enter Parameters does not appear, choose Refresh Report.
Enter Parameters displays all the report parameters you created. Figure 13‑6 shows example report parameters, RP_quantityinstock and RP_productvendor. The parameter names appear because values were not supplied for the Prompt text property.
Figure 13‑6 Enter Parameters
3 Specify a value for each parameter, then choose OK.
If you completed all the tasks that were described earlier in this section, the WHERE clause of the query incorporates the specified values, and the report displays the rows that match the WHERE clause.
Create report parameters to prompt the user to specify values that determine what rows to retrieve.
