Not Able to Tie Multi-select Report Parameter to Data Set Parameter

Why can't you tie a multi-select report parameter to a data set parameter?

Find more posts tagged with

Comments

Hi kpelzer29,

You'll have to do something like in the following devShare example.

http://www.birt-exchange.org/org/devshare/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause/

The reason is because the multiple select returns an array and not just a comma separated list. You have to create this comma separated list yourself as in the example above. Let me know if you have questions.

kpelzer29

Hi mwilliams, 
 
Thank you! The devShare example helped me a lot. Is there a way I can pass an integer value directly into the SQL simliarly to the devShare example? 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="67551" data-time="1282245868" data-date="19 August 2010 - 12:24 PM">
Hi kpelzer29, 
 
You'll have to do something like in the following devShare example. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/771-using-a-multivalue-parameter-in-a-in-clause/</a> 
 
The reason is because the multiple select returns an array and not just a comma separated list. You have to create this comma separated list yourself as in the example above. Let me know if you have questions. </blockquote>

mwilliams

kpelzer29,

What do you mean by passing an integer directly in?

kpelzer29

mwilliams, 
 
I have two cascading parameter groups using two listboxes in each group. The first listbox in each group can only accept a single selection, but the second listbox can accept multiple selections. I am using the same method used in the devShare article to insert the multi-select report paramater values directly into the SQL in a data set (the value(s) selected from the second listbox in each cascading parameter group). I still need to find a way to insert the value selected from the first listbox in each cascading parameter group when the value expected is a single integer value. 
 
I am using the following in the beforeOpen script of the data set used to create the report in order to try to get the value of the first parameter in the first cascading parameter group and insert the value into the SQL of the data set: 
this.queryText = this.queryText.replace("****", params["cascParamOneGroupOne"].value); 
 
Within the where clause in the SQL in the data set, I have inserted: 
AND table1.FieldId in ('****') 
 
I keep getting the following error when I try to replace the value in the SQL for the data set with the report parameter value that begins with: 
org.eclipse.birt.report.engine.api.EngineException: Cannot execute the statement. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object. SQL error #1: Conversion failed when converting the varchar value '****' to data type int. 
 
Normally I would be able to use data set parameters and use the question mark in the query, but I only want the values of the first parameter in both cascading parameter groups. I am not sure how I can skip the second parameter in the cascading parameter groups using the question mark. I need to skip the second parameter in the cascading parameter groups since I am using the method in the devShare article because those are multi-select parameters. 
I hope I am explaining the question better. Please let me know if you have any other questions. 
 
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="67964" data-time="1283280068" data-date="31 August 2010 - 11:41 AM">
kpelzer29, 
 
What do you mean by passing an integer directly in? </blockquote>

mwilliams

For the single value, you can just do:

AND table1.FieldId = ****

then you'd use the same replace statement. You only need to use the IN clause when you're dealing with the multiple select item.