Hi All,

I googled about dynamic query in BIRT a few days back, but I got little idea on how to do so though this article was informative click here.

So I thought of sharing few things that I learnt by just playing around. I encountered two ways of doing it, though there may be many, these two are quite easy to pick and are very useful.

1)Using Parameters:

While creating the data set, write the query like a normal sql query with unknown parameters replaced by the symbol ?.

Eq: select * from CLASSICMODELS.EMPLOYEES where OFFICECODE = ?

Click Finish, then goto the parameters tab, BIRT would have identified the ? symbol by now and would have already listed it there as a query parameter. Give a suitable name and link it to report parameter, if no suitable parameters to link, create a new report parameter and link.

The value passe to the report parameter will get replaced in the query above.

2)Using Scripts:

While creating the dataset, write the query like a normal sql query but replace the dynamic text with some special text.

Eg: select * from CLASSICMODELS.EMPLOYEES where OFFICECODE = ':officeCode'

This is useful if same parameter is to be used at multiple places.

Now, in the data set's beforeOpen method (click the desired dataset and click on the script tab in designer to reach to this method), the query string can be accessed by the variable queryText.

Since queryText is a normal string, it can be modified easily as a normal string:
this.queryText = this.queryText.replaceAll(":officeCode", params["officeCode"]);
Where params["officeCode"] is a report parameter.

regards
Arpan