BIRT Cascaded Parameters

BIRT provides the capability to use dynamic parameters to present the end user with a list of choices that are populated from a dataset. This is very useful but can cause issues when the dataset returns many rows of data. To reduce the number of items in any parameter, the developer can use a cascaded parameter, which allows multiple levels and multiple datasets. When the user selects the first level parameter in a cascaded parameter group the second level in the group is automatically re-queried.

For example, if you have a customer detail report that allows the end user to select a particular customer, you could create a dynamic parameter that retrieves all the customer names from your database. The user would then select the one they are interested in, and a detail report would be generated on the selected customer. If you have thousands of customers this task becomes more difficult for the end user to navigate. To remedy this, you could create a cascaded parameter that has as its first level, the country and once that value is selected, all customers for the specific country would be listed. This assumes you have a field like country to that can be used to reduce the number of entries. If you do not have a field like this, it may be possible to create your own using script.

If we take the customer list example, one way to reduce the number of items in any of the parameter list box, would be to sort the first level of the cascade alphabetically. So the first level of the cascade could be tied to a scripted datasource that returns the letters of the alphabet. Another option is to do a distinct query on the customers within the database that retrieves only the first letter like:

select distinct SUBSTR( CUSTOMERNAME, 1, 1)
from customers

The second level in the cascade would then be defined with the following query.

select customername
from customers
where customername like ?

The question mark represents a data set input parameter, which is not the same as a report parameter. The dataset parameter can be linked to a report parameter or set programmatically using script. In this example we need to use script to add a wildcard to the query. So in the beforeOpen script of the second level query, we could do this:

inputParams["alpha"] = params["FirstLetter"]+"%";

In this example, alpha is the dataset parameter and the FirstLetter is the first level report parameter in the cascade group. In this case we are only adding the wildcard to the query.

The resultant parameter entry screen would look like:



This report is available here. Another example using a scripted data source is available here.