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

Wednesday, August 4, 2010

Finding BirtDateTime

I noticed this today: if the SQL dataset column type is DATETIME, and you set it to DateTime in BIRT as well, the report will show it like this (for 2nd August, 2010 00:00:00, Central European Summer Time):

Mon Aug 02 00:00:00 CEST 2010

This is not something Javascript can accept - although I tried the toLocaleString() method and it worked. However, I just needed the date part. toLocaleDateString() did not show anything.

This is where I tried the BirtDateTime class; it accepts the DateTime strings nicely. Now I can do this for instance:

BirtDateTime.day(row["Day"]) + '. ' + BirtDateTime.month(row["Day"],2) + ' ' + BirtDateTime.year(row["Day"]) 

It will show (provided the browser locale is set to Czech):
2. srpna 2010