scripting: adding a parameter placeholder ('?') to a dataset query at runtime

rummicub

Hi.
I wish to modify an sql query's dynamic parameter set at runtime, based on the value of a report parameter: I.E. if value of report parameter != -1 then redefine the sql to include "WHERE X = ?", and then bind the value of the RP to the new '?' placeholder.

Though this seems like a fairly common task, I'm having trouble finding reference for such an implementation. I've read http://www.birt-exchange.org/org/forum/index.php/topic/15763-dynamic-query-modification/ but could get my head around it: I can't see how the suggested solution binds the added placeholder to a parameter. I've also found http://www.birt-exchange.org/org/devshare/designing-birt-reports/673-how-to-create--dataset-parameter/, which claims to allow such modification using a more-elaborate java code. Yet I'd rather contain the solution within a single rptdesign file, and keep it simple, if indeed it can be achieved through (JS) scripting.

Would thank anyone pointing me to a scripting solution that handles this case. I'm using BIRT 2.6.2.
Cheers

CBR

Due to the nature of how the BIRT JDBC driver runs the query, the engine has to know the exact number of parameters before you run the report.
What you can do is to modify the query using a static statement that has the same number of parameters as the one you originally defined.

A common solution for your problem is to add the last parameter twice. Your example would look like this:

... WHERE (X=? OR ?=-1) (don't forget the parantheses if you have multiple ANDs before)

How does it work?
It's fairly simple: If the parameter is -1 it executes X=-1 or -1=-1 so the last condition return true for every row. If the parameter is something else then -1 the last condition returns false, which means that the check X=? is run for every row.