how to pass multiple values to a single report parameter

delta123 · August 31, 2011

I am having a Date type report parameter and passing its value into dataset sql query 'select.....from.....where date=?' But now i want to pass multiple values for this single report parameter into above query...plz suggest me any solution for it..

mwilliams · August 31, 2011

Are you using a textbox parameter and having the dates entered with a comma separator? Or are you having a multi-select parameter where the user selects multiple dates from a listbox?

delta123 · September 1, 2011

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="82179" data-time="1314824186" data-date="31 August 2011 - 01:56 PM">
Are you using a textbox parameter and having the dates entered with a comma separator? Or are you having a multi-select parameter where the user selects multiple dates from a listbox? </blockquote>

delta123 · September 1, 2011

I am having a multi-select parameter where the user selects multiple dates from a listbox. Its working fine but i dont know how to pass values of this multi select parameter in IRunAndRenderTask task.
When i am taking single value parameter then m passing its value like IRunAndRenderTask task.setParameterValue("tdDate", paramValue2);
But now, in the case of multi value parameter, how to pass multiple values in task.setParameterValue("tdDate",........)
Plz help me

delta123 · September 2, 2011

<blockquote class='ipsBlockquote' data-author="'delta123'" data-cid="82189" data-time="1314851472" data-date="31 August 2011 - 09:31 PM">
I am having a multi-select parameter where the user selects multiple dates from a listbox. Its working fine but i dont know how to pass values of this multi select parameter in IRunAndRenderTask task. 
When i am taking single value parameter then m passing its value like IRunAndRenderTask task.setParameterValue("tdDate", paramValue2); 
But now, in the case of multi value parameter, how to pass multiple values in task.setParameterValue("tdDate",........) 
Plz help me </blockquote>

delta123 · September 2, 2011

Hi everyone.....i got the solution of my problem,,,I have to pass an Object[] type variable in place of paramValue2 that is an integer variale, in IRunAndRenderTask task.setParameterValue("tdDate", paramValue2);

mwilliams · September 7, 2011

Sorry for the delay, glad you found a solution. Let us know whenever you have questions!

dishatto · February 8, 2012

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="82394" data-time="1315423187" data-date="07 September 2011 - 12:19 PM">
Sorry for the delay, glad you found a solution. Let us know whenever you have questions!

</blockquote>
 
 
Hello, I am having the same trouble but with entering multiple values into a textbox parameter. Can you provide a solution? My parameter works perfectly but only with one value.

mwilliams · February 8, 2012

I didn't open it up, but I believe this example shows how to enter multiple values into a text box and then uses them in the query. Let me know.

http://www.birt-exchange.org/org/devshare/designing-birt-reports/338-birt-parameters--using-in-clause/

dishatto · February 9, 2012

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="95227" data-time="1328738759" data-date="08 February 2012 - 03:05 PM">
I didn't open it up, but I believe this example shows how to enter multiple values into a text box and then uses them in the query. Let me know. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/338-birt-parameters--using-in-clause/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/338-birt-parameters--using-in-clause/</a> </blockquote>
 
current problem. This code is giving me an error message that it isn't ended properly. But why? 
 
where 
(a.status = 'Replaced' or 
a.status = 'Warranty') and 
a.serialno IN ('MS:param_1') ? 
 
Also, can you tell me what the MS stands for? I admit I am using that piece of the code blindly.

mwilliams · February 9, 2012

Can you create a sample report, using the sample database, that shows me what you're doing? Thanks!

dishatto · February 9, 2012

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="95449" data-time="1328803125" data-date="09 February 2012 - 08:58 AM">
Can you create a sample report, using the sample database, that shows me what you're doing? Thanks! </blockquote>
 
As you were writing that answer above I was modifying my question to the current problem. Does the code I provided help? Select is just typical columns from 'a' database. The code works perfectly fine except when I try to add the in clause. Here's a sample from the classic models database: 
select products 
from classicmodels 
where CLASSICMODELS.PRODUCTS = classicmodels.products in ('MS:Parameter_1') and classicmodels.products in ('MS:NewParameter')) 
 
(I can not test it because the classicmodels appears to be disabled). 
 
Here is an error message I am getting for my regular report: 
 
Failed to prepare the query execution for the dataset: Data Set 
Cannot set the string value(%) to parameter1 
Cannot set preparedStatement paramenter string value 
SQL error #1: Invalid column index

mwilliams · February 9, 2012

Are you not using the way from the example in the download? Are you using scottr's way? I'm guessing MS = multi-select just by looking at it. Let me know on which way you're trying to do this.

dishatto · February 10, 2012

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="95547" data-time="1328820480" data-date="09 February 2012 - 01:48 PM">
Are you not using the way from the example in the download? Are you using scottr's way? I'm guessing MS = multi-select just by looking at it. Let me know on which way you're trying to do this. </blockquote>
 
I couldn't download his file so that it came out readable for some reason, so I went to the link provided by one of his commenters instead and followed that example: 
<a class='bbc_url' href='http://birtworld.blogspot.com/2009/03/birt-multi-select-statements.html.'>http://birtworld.blogspot.com/2009/03/birt-multi-select-statements.html.</a> 
 
He said creating a multiple input parameter for a text box was a simple two step process. 1) modify your code with 
 
select status 
from orders 
where status in ('MS:pStatus') and 
state in ('MS:pState') 
 
 
2) and then add to the the script section of birt: 
 
this.queryText = BlackboardFunctions 
.MultiSelectSql(this.queryText, reportContext); 
 
so I modified my workable single value textbox parameter report to include the one parameter I want to be multiple to: 
 
select serialno 
from asset 
where serialno in (MS:paramater1') 
 
and the script to read 
 
this.queryText = BlackboardFunctions 
.MultiSelectSql(this.queryText, reportContext); 
 
 
I may not be understanding this correctly. Thank you for your patience and your help. I am quite a newbie!

Tubal · February 10, 2012

<blockquote class='ipsBlockquote' data-author="'dishatto'" data-cid="95703" data-time="1328889683" data-date="10 February 2012 - 09:01 AM">
I couldn't download his file so that it came out readable for some reason, so I went to the link provided by one of his commenters instead and followed that example: 
<a class='bbc_url' href='http://birtworld.blogspot.com/2009/03/birt-multi-select-statements.html.'>http://birtworld.blogspot.com/2009/03/birt-multi-select-statements.html.</a> 
 
He said creating a multiple input parameter for a text box was a simple two step process. 1) modify your code with 
 
select status 
from orders 
where status in ('MS:pStatus') and 
state in ('MS:pState') 
 
 
2) and then add to the the script section of birt: 
 
this.queryText = BlackboardFunctions 
.MultiSelectSql(this.queryText, reportContext); 
 
so I modified my workable single value textbox parameter report to include the one parameter I want to be multiple to: 
 
select serialno 
from asset 
where serialno in (MS:paramater1') 
 
and the script to read 
 
this.queryText = BlackboardFunctions 
.MultiSelectSql(this.queryText, reportContext); 
 
 
I may not be understanding this correctly. Thank you for your patience and your help. I am quite a newbie! </blockquote>
 
Are you talking about a multiselect parameter? (Where you can highlight multiple selections) 
 
Or are you talking about one single textbox where they enter something like 'a, b, c' etc. 
 
Could you give a sample of what they are entering into the parameter? 
 
Thanks.

mwilliams · February 10, 2012

Tubal - His original question was about entering values in a text box.

dishatto - I'll try to look at the example I linked to you. If it doesn't work, I'll make one for you. What is your BIRT version?

dishatto · February 10, 2012

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="95710" data-time="1328897040" data-date="10 February 2012 - 11:04 AM">
Tubal - His original question was about entering values in a text box. 
 
dishatto - I'll try to look at the example I linked to you. If it doesn't work, I'll make one for you. What is your BIRT version? </blockquote>
 
version is 2.5.2. Thanks for your help- it's very much appreciated.

dishatto · February 10, 2012

<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="95708" data-time="1328891426" data-date="10 February 2012 - 09:30 AM">
Are you talking about a multiselect parameter? (Where you can highlight multiple selections) 
 
Or are you talking about one single textbox where they enter something like 'a, b, c' etc. 
 
Could you give a sample of what they are entering into the parameter? 
 
Thanks. </blockquote>
 
Hi Tubal, thanks for your interest. No I am not creating a multiselection report but one where the user can search a single field in a database by putting multiple values into a single textbox. They would be entering for example 10 serial numbers. Right now they can only enter one at a time, but they need a report where they can enter several serial numbers at once. 
 
Perhaps there is another way to do this other than through a parameter. Someone told me you can have the user attach a file to the report and have the report search the database for the values, but I have not figured out how birt works in this way.

Tubal · February 10, 2012

This is for BIRT 3.7.1. I'm not sure if 2.5.2 has the same functionality. 
 
In your dataset query, do something like this (or whatever your query requires. The important thing is to have /**serialno**/ where the where statement would normally go. We are going to replace this with a script. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select *
from table
/**serialno**/</pre>
 
Now, in the beforeOpen event of your dataSet, do this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("/**serialno**/", "WHERE serialno IN (" + params["serialno"].value + ")");
</pre>
 
Basically, in that script, you're replacing /**serialno**/ in your query with "WHERE serialno IN ( <whatever the user enters>)" 
 
This is assuming that the user enters a comma delimited list. You may need to do some cleanup of their text before you replace it if you have problems.

mwilliams · February 13, 2012

Yes, 2.5.2 will work this same way.

dishatto - Let me know if you still need an example to look at, still. Tubal's description is pretty good, though!

dishatto · February 13, 2012

<blockquote class='ipsBlockquote' data-author="'Tubal'" data-cid="95732" data-time="1328919138" data-date="10 February 2012 - 05:12 PM">
This is for BIRT 3.7.1. I'm not sure if 2.5.2 has the same functionality. 
 
In your dataset query, do something like this (or whatever your query requires. The important thing is to have /**serialno**/ where the where statement would normally go. We are going to replace this with a script. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>select *
from table
/**serialno**/</pre>
 
Now, in the beforeOpen event of your dataSet, do this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("/**serialno**/", "WHERE serialno IN (" + params["serialno"].value + ")");
</pre>
 
Basically, in that script, you're replacing /**serialno**/ in your query with "WHERE serialno IN ( <whatever the user enters>)" 
 
This is assuming that the user enters a comma delimited list. You may need to do some cleanup of their text before you replace it if you have problems. </blockquote>
 
 
Hi Tubal, 
 
Thank you for your help. I am not sure I understand what to do correctly so thank you for your patience. I am very new to this. I just tried your method and received an error message. 
 
Here is exactly what I have done: 
 
SELECT column1, column2, colum3, serialno 
from table1, 
inner join table2 
on 
table1.commonfield = table2.commonfield 
where 
table1.serialno like ? and 
(table1.status = 'RBW' or 
table1.status = 'WR') 
order by table1.serialno 
 
Then in the script before open section I put what you told me to put in: 
this.queryText = this.queryText.replace("/**serialno**/", "WHERE serialno IN (" + params["serialno"].value + ")"); 
 
And recieved this error message: + missing ) after argument list (<inline>#1) 
 
So I modified the code in the beforeopen script text to include the the actual name of the parameter('NewParameter') and changed this part of your code: "/**serialno**" for simply "?" 
 
this.queryText = this.queryText.replace("?", "WHERE serialno IN (" + NewParameter["serialno"].value + ")"); 
 
I recieved the same error message for the second try. 
 
Can you tell me what I am doing wrong?

Tubal · February 13, 2012

Change this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT column1, column2, colum3, serialno
from table1,
inner join table2
on
table1.commonfield = table2.commonfield
where
table1.serialno like ? and
(table1.status = 'RBW' or
table1.status = 'WR')
order by table1.serialno</pre>
 
To this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT column1, column2, colum3, serialno
from table1,
inner join table2
on
table1.commonfield = table2.commonfield
where
/**serialno**/
(table1.status = 'RBW' or
table1.status = 'WR')
order by table1.serialno</pre>
 
Then in your beforeOpen script, put this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>this.queryText = this.queryText.replace("/**serialno**/", "table1.serialno IN (" + params["serialno"].value + ") and ");</pre>
 
This is assuming that your parameter is called 'serialno'. 
 
What we are doing is bypassing the whole ? in the query stuff, and just "manually" putting that part of our where clause in there. So we're replacing the "/**serialno**/" in your query with "table1.serialno IN (bla, bla, bla) and " before your query gets to your database. 
 
Since you have multiple serial #'s you are trying to match, you can't use LIKE because that only compares 1 to 1. You would use IN(x, x, x, x) to find matches with anything in your IN parentheses. 
 
So after your beforeOpen script runs, your query will look like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT column1, column2, colum3, serialno
from table1,
inner join table2
on
table1.commonfield = table2.commonfield
where
table1.serialno IN (bla, bla, bla) and
(table1.status = 'RBW' or
table1.status = 'WR')
order by table1.serialno</pre>
 
If your serial numbers are stored as text and not numbers, you will need to use javascript to do some modification in your beforeOpen script to get quotes around each serial number. It would need to look like "table1.serialno IN ('bla', 'bla', 'bla') and " in your query.

Shasha · March 26, 2013

This is not working for me

mwilliams · March 29, 2013

Are you getting an error? If so, what is it? Can you attach your design?

how to pass multiple values to a single report parameter

Comments

Categories