Tuesday, August 17, 2010

Dynamic query in BIRT

Pretend, I have a dataset backed by the following SQL query:

select
COUNT(*)
from ipcc_baA.dbo.Personal_Callback_List pcl
where pcl.FirstName IN ('something')
and pcl.CallStatus <> 'C'

and a parameter, named cctype, type String, returning either value 'HIM' or 'AB'. No duplicate values.

Based on the parameter value, I want to change the query text. For instance, write something into the IN clause within the brackets. Or, change a whole row.

A not elegant, but working way is: using the dataset's beforeOpen method, like this:

if (params["cctype"].value == 'HIM') {
 var inject = '\'H1\',\'H2\',\'H3\',\'H4\',\'I\',\'M\'';
 this.queryText = this.queryText.replace('\'INJECT\'',inject);
}
if  (params["cctype"].value == 'AB') {
var inject = 'where pcl.AlternateVDN IN (\'reserveA1\',\'reserveA2\',\'reserveA3\',\'reserveB1\',\'reserveB2\')';
 this.queryText = this.queryText.replace('where pcl.FirstName IN (\'INJECT\')',inject);
}
}

And of course, modifying the query itself:

select
COUNT(*)
from ipcc_baA.dbo.Personal_Callback_List pcl
where pcl.FirstName IN ('INJECT') --neupravovat!!!
and pcl.CallStatus <> 'C'

And voilá, it works. Of course, one should take extra care not to change the original query, otherwise the replace method won't find the needle in the haystack.

The query, in the data set
The beforeOpen method