Pass Parameters seperated by commas to Report

Hello everyone,

I did search for this on the internet and I think it is not (yet?) possible in birt but I just wanted to be safe and ask here too

.

I have a (a single one) parameter with multiple values that is passed in this form to the report:

0, 10, 15

I would like to use this in a query with an IN clause like this:

Select * from XXXXX where XXXXX IN (?)

Is there a workaround for this?

Thank you all very much,

Stan

Find more posts tagged with

Comments

mwilliams

Hi Stan,

I don't see a way to link a multi-select parameter to a single parameter from a query. What you can do is set the following filter on the dataSet using the multi-select parameter:

row["ColumnName"] In param["ParameterName"]

This is if you have static values. If you have dynamic values, you'll have to let all the data come in and filter on the table level or the user won't be able to see the values in the parameter box.

If I find a way to do connect a multi-select parameter to a query parameter, I'll post it in here.

Hope this helps.

sbanaszak

Hi Michael,

Thanks for your reply.

I tried using filters but that didn't work, it always only used the first number of the string. I found a tutorial that did it with a script on beforeOpen of the dataset:

if(params["param"].value != null){
this.queryText = this.queryText + " where parent_id in (" + reportContext.getParameterValue("param") + ")";
}

Why does it work when you use a script but not if you pass the parameter directly into the query with a "?" ?

mwilliams

Stan, 
 
If you use the text box type parameter and let the user just type in values, it only takes the first. However, if you use a list box type parameter, there is a check box that says allow multiple values. You can either define the possible values on your own in a static format, or you can use a dynamic parameter where the values are defined by the dataSet. 
 
As for the '?' parameter in the dataSet not accepting multiple values, I'm not sure exactly. If you set up a multiple select parameter like I say above, the option to link the '?' parameter to it is not there. When you set the query up in script, you're not using the '?' parameter link to the report parameter, so it will work different. This is definitely something that you could request an enhancement for at <a class='bbc_url' href='http://www.eclipse.org/birt/phoenix/reportabug.php'>BIRT: Reporting Bugs and Requesting Enhancements</a>.

sbanaszak

I filed a bug and this was Lin Zhu's reply:

"This is the JDBC driver limitation rather than BIRT limitation. When you use
the beforeOpen method actually your queryText is being evaluated by java script engine so that what passed in is a simple sql query w/o parameter. If you use data set parameter then it is up to your JDBC driver to decide whether to
accept parameter values such as 10,20,30,40."

Maybe it would be a good enhancement if birt would evaluate all SQL queries before using the jdbc driver?

vidu

hey i this this i s simple 
 
 
there are two ways here 
 
<a class='bbc_url' href='http://www.birt-exchange.com/devshare/designing-birt-reports/535-using-in-like-clause-in-birt/'>Using IN/LIKE Clause in BIRT - Tutorials - BIRT Exchange</a> 
 
 
second one 
 
do something like this in property binding 
 
if (params["custList"].equals("*")) { 
this.queryText = "select CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER, CLASSICMODELS.CUSTOMERS.CUSTOMERNAME from CLASSICMODELS.CUSTOMERS"; 
} else { 
var endOfQuery = "WHERE CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER IN(" + params["custList"] +")"; 
this.queryText = "select CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER, CLASSICMODELS.CUSTOMERS.CUSTOMERNAME from CLASSICMODELS.CUSTOMERS " + endOfQuery; 
 
and set the parameter custList to be a string text box... 
 
you are done 
 
hope this helps...

mwilliams

Thanks vidu! I also figured this out since my last post in here, but you beat me to it.

Stan,

Give vidu's solutions a try and let me know if you have any problems.

vidu

glad that i give something to this forum .. I have taken alot and still gaining alot , thought to give something back...

you are the inspiration Williams.you have guided me this far

Vidu

sbanaszak

The report is working fine now!

Thanks guys

nirmal792

hello,

I also want the same functionality..so i used same kind of script in property binding of dataset.

if (params["user"].value.equals("*"))
{this.queryText = this.queryText ;}
else
{var parameter="g_user NOT IN (" + params["user"].value +")";
this.queryText = this.queryText.replace ('g_user IS NOT NULL',parameter);}

but after excuting the report m getting following error:
engine error: TypeError: Cannot call method "replace" of undefined (<inline>#4)

please help me in this.

P.S-user is the report parameter i.e dymanic values with listbox wilth multiple values allowed to select..i even tried it with simple text box.

mwilliams

Hi nirmal792,

Can you replicate your issue with the sample database so that I can see exactly what you're doing and so I can run the report and test on it? Thanks!

P.S. - Please include your BIRT version as well.