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.

Multi-Select Parameters - Part 2

 In a previous post I described how you could create a simple method to use multi-select parameters with a SQL IN clause in a generic fashion.  In that post I referenced that the right way to do this is to use Java Bind variables

There is a way that this can be done using Java Bind variables, but it is significantly more difficult.

But I did not explain how that is done.  Today I gave a webinar on how you can use the Design Engine API and the BIRT Script Function extension point to dynamically add parameter binding to your report designs.

The DEAPI code is a bit tricky to use, but thanks to the ScriptFunction extension, any one can use the function quickly and easily.

All of the code and the slides for the project are available on the Innovent Subversion server here.  You can use the subclipse plugin to download this code as an Eclipse project.

The slides for the presentation are included here:

BIRT Multi Select Parameters

Publish at Scribd or explore others: How-to-Guides & Manu multiselect script function

BIRT 2.5 M6 New and Notable

Just wanted to drop a note to say the new and notable for BIRT 2.5 M6 is now available. Some great features are making their way into BIRT 2.5, like z ordering for combination charts, improved 508 compliance, project configuration changes, and better image marker support.



To read more about the new features, go here. As always we appreciate feedback on the new features.