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 as described previously.
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
5 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.
