Programmatically add dataset parameter

Hi all, 
 
I am trying to achieve the following: 
 
My report has a multivalued report parameter which should act as a filter on a dataset (The classic "dynamic SQL IN statement" program. As I am working with very large datasets I want to perform the filtering on the database side. 
 
My idea is the following: 
 
1. Add a special tag in the SQL query "--TAG and where column_bla in (TAG)" 
2. read report parameter and check if it is non-empty 
3. If non-empty then remove "--TAG" and replace "TAG" with variable number of "?," 
4. add the correct number of input parameters to the dataset using javascript 
 
When performing the above in the beforeOpen script I get an UnsupportedOperationException due to the fact that the set of parameters is stored in an unmodifiable map. 
 
In short: Is there some way to dynamically add dataset parameters at runtime using javascript? 
 
Many thanks & Best regards 
 
Jonas

Find more posts tagged with

Comments

Tubal

You can do this in the beforeOpen script event of your dataset. 
 
I used something like this in one of my reports: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var parmcount = params["ven_cd"].value.length;
var vendors = "";
for ( i=0; i < parmcount; i++ ){
if( params["ven_cd"].value[i] != null && params["ven_cd"].value[i] != '') {
if( i == 0){
vendors = vendors + "'" + params["ven_cd"].value[i] + "'";
} else {
vendors = vendors + "," + "'" + params["ven_cd"].value[i] + "'";
}
} else {parmcount = parmcount - 1}
}
if ( parmcount > 0){
this.queryText = this.queryText.replace("/**vendors**/", " s.vendor_cd IN (" + vendors + ") and ");
}</pre>
 
It's basically iterating through the multiselection parameter, putting it into a comma delimited format, and then replacing the instance of '/**vendors**/' in the sql query with an IN statement.

isl01jbe

<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="97153" data-time="1330800481" data-date="03 March 2012 - 11:48 AM">
You can do this in the beforeOpen script event of your dataset. 
 
I used something like this in one of my reports: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>var parmcount = params["ven_cd"].value.length;
var vendors = "";
for ( i=0; i < parmcount; i++ ){
if( params["ven_cd"].value[i] != null && params["ven_cd"].value[i] != '') {
if( i == 0){
vendors = vendors + "'" + params["ven_cd"].value[i] + "'";
} else {
vendors = vendors + "," + "'" + params["ven_cd"].value[i] + "'";
}
} else {parmcount = parmcount - 1}
}
if ( parmcount > 0){
this.queryText = this.queryText.replace("/**vendors**/", " s.vendor_cd IN (" + vendors + ") and ");
}</pre>
 
It's basically iterating through the multiselection parameter, putting it into a comma delimited format, and then replacing the instance of '/**vendors**/' in the sql query with an IN statement. </blockquote>
 
 
Thanks Tubal, 
 
Unfortunately that solution is not possible for us as it leaves us open to an SQL injection attack. What I want to use is the "safe" framework of BIRT to bind the parameters to the SQL query. I currently have the solution mentioned above including an detection of sql escape characters but I'm still not satisfied with the security. Hence the desired approach.

Hans_vd

Hi,

There is a devshare article with a sample report that does that:
http://www.birt-exchange.org/org/devshare/designing-birt-reports/832-dynamic-binding-of-in-list-parameters/

Hope it helps
Hans

johnw

Take a look at the BIRT Function Lib (http://code.google.com/a/eclipselabs.org/p/birt-functions-lib/).

It has a bind function that should do what you are looking for.

If your datasets are reasonable in size, you might want to consider using dynamic Filters instead. These can be dynamically added with a little bit of script.

isl01jbe

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="97208" data-time="1330930571" data-date="04 March 2012 - 11:56 PM">
Hi, 
 
There is a devshare article with a sample report that does that: 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/832-dynamic-binding-of-in-list-parameters/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/832-dynamic-binding-of-in-list-parameters/</a> 
 
Hope it helps 
Hans </blockquote>
 
Hans, 
 
Thanks a lot for the help. Much appreciated. That solved the problem. The main issue was in accessing the dataset parameters. I used the script from devshare but modified more to suit my needs: 
 
1. I updated it so that it can take multi-value parameters 
instead of linking to report parameter directly, set parameter default value to params["ParamName"].value 
2. It now replaces comments of the type below with the content of ParamName: 
--PARAM:[ParamName] and where ColumnName in (PARAM:[ParamName]) 
3. There is a piece of code which I do not really understand in that script. When inserting the first new parameter it checks if it is the first parameter. In that case it uses replace instead of insert. I might have missed something but that seems wrong. It loops over an iterator that must be empty (because it is the first parameter) and only then replaces the parameter. Because it is inside the iteration, it will never be executed, no? 
 
Best regards 
 
Jonas