Thursday, April 30, 2009

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.

11 comments:

akii said...

I want to select multiple parameter from a list box so that it can be handled by in statemant. If i select 'A', 'B' and 'C' then it should show result as

select * from table where field in ('A', 'B', 'C');

Scott Rosenbaum said...

Unfortunately, I don't know of a way to build multi-select cascaded parameters. I spent a bit of time looking at the issue for someone else, and was unable to come up with any easy solution.

You might try submitting a bug to
Eclipse Bugs

Anonymous said...

Also spent some time searching on the subject. Ran into this nice artice and here is the solution I found: advanced options of the cascaded parameter -> Scalar parameter type. Works for me for 2.5.1.
Also explained @ http://www.birt-exchange.org/forum/designing-birt-reports/11738-cascading-parameter-multiple-values.html

Edenist said...

hmmm what if you want to sort the list? for example I'm querying a view in SqlServer to get the values for my list, but SqlServer won't let me sort the view result set so I have to do it in BIRT.. :-(

Anonymous said...

What if you wanted to have a parameter in your cascade group that is dependent on many

So instead of having

date
name
phone
city
address

you'll have

date
name
phone
city
address

Anonymous said...

wow my last comment didn't work out
here is what i really wanted

instead of

date
(tab) name
(tab)(tab) phone
(tab)(tab)(tab) city
(tab)(tab)(tab)(tab) address

you will have

date
(tab) name
(tab)(tab) phone
(tab)(tab) city
(tab)(tab) address

Jag said...

I have the same requirement as the previous poster.

I have 2 parameters that are dependent an other parameter:

Country
-->State/province
-->language

I want the language by country and the state/provinces by country also.

The only ugly way I know how to do it now is by using 2 cascading parameter groups, which means that the user will have to select the country twice.
Any suggestions?

Thanks,

Scott Rosenbaum said...

Unfortunately, I don't have a good way to handle the two parameters from a single prompt.

Jason Weathersby said...

Why not use one cascade with three levels.
Country
State
Language

Although state my not affect Language you probably can still use it in the cascade.

Amit Malyan said...

i have two parameters in reports that depend on a single parameter
for example country ,city ,state.

but in my case city depends on country and one more parameter that is also depends on country at the same time. with cascade i can handle only i.e city. how to handle that that one also

Amit Malyan said...

i have two parameters in reports that depend on a single parameter
for example country ,city ,state.

but in my case city depends on country and one more parameter that is also depends on country at the same time. with cascade i can handle only i.e city. how to handle that that one also