ireport List in where
<parameter name="p1" class="java.util.List"/>
<queryString language="SQL">
<![CDATA[select * from user2 where $X{IN, name , p1}]]>
</queryString>
create table user2 (
id varchar2(3),
name varchar2(5)
);
insert into user2 values('1','tom');
insert into user2 values('1','ask');
insert into user2 values('3','tom2');
insert into user2 values('3','a');
expression is
new Boolean($P{p1}.add($F{your_field_from_query})
keep the rest type as report so that all the values from the field get added into the para p1
--> now use it in your where clause as follows:
where $X{IN,col_name,p1}
Lee,
I apologize! I was wrong because I mixed the two cases : string parameter and collection!
I first tried with a collection and , as far as I remember, it worked but as I said I needed to pass the parameters through an URL.
The $X{} parameter form needs effectively a collection.
What I used for the string parameter is the $P!{} form (notice the !) which expands the parameter as it.
So in your query you should try something like that :
" where table.value in $P!{A}" if A contains the string "(1,2,3)"
I am sure I used it and it worked. Now I am not in the environment where I can access my jasperserver so I cannot do cut and paste of the real query I used.
As soon as I am in the good environment, I will post you more details !
Gaby38
Project Roles
Posts: 248
Karma: 35
#44638
RE:Issue with Parameters that are from a List 08/14/2008 17:56
Lee,
I confirm my previous post. I have a report under jasperserver which is launched via URL and which has one of his parameters "I_ActList" which is a list of "activities id" passed as a string and in my query I have in the where clause :
"where stat.QUARTIER_Adresse.GdQuartier=$P{GdQuartier}
and conso_jour.id_act in $P!{I_ActList}"
THis is a copy/paste of a part of my query viewed under iReport.
I_ActList contains a comma separated list of integers which are the ids of activity fields :
"(6,7,8)" for instance
and it works.
Hope you will success in your environment
http://stage.jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=70494
http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=44570
org.apache.commons.lang.StringUtils.join($P{A}.toArray(),",")
http://www.daniweb.com/forums/thread143304.html
http://www.javalobby.org/java/forums/t43616.html
http://www.tkk7.com/killme2008/archive/2007/02/06/98242.html
http://jtri.sourceforge.net/en/report/ireportHibernate.html
http://netspirit.javaeye.com/blog/144057
Lee,
I confirm my previous post. I have a report under jasperserver which is launched via URL and which has one of his parameters "I_ActList" which is a list of "activities id" passed as a string and in my query I have in the where clause :
"where stat.QUARTIER_Adresse.GdQuartier=$P{GdQuartier}
and conso_jour.id_act in $P!{I_ActList}"
THis is a copy/paste of a part of my query viewed under iReport.
I_ActList contains a comma separated list of integers which are the ids of activity fields :
"(6,7,8)" for instance
and it works.
Hope you will success in your environment
Gaby
Gaby38
Project Roles
Posts: 248
Karma: 35
#66426
RE:Issue with Parameters that are from a List 11/06/2009 11:15
So when i pass the parameter to the report i have to place a () before and after teh parameters
How to do multi-select in iReport 3.6 09/10/2009 19:42
I am trying to run a report with a multi-select parameter in iReport 3.6 but cannot get it to run. Parameter name is EmployeeType of type java.util.Collection. My default value expression is java.util.Arrays.asList(new String[]{"Manager","General"})
select Name
from Table1
where $X{ IN, EmpType, EmployeeType}
In the Report Query window, I get an error message saying:
Error: SQL problems: Line 3: Incorrect syntax near 'X'.
If I try using $P I get the message:
Error: SQL problems: The Java type java.util.Arrays$ArrayList is not a supported type.
If I try using $P! I get the message:
Error: SQL problems: Invalid column name 'Manager, General'.
Any ideas on how to solve this?
zamudio
Project Roles
Posts: 4
Karma: 1
#63637
RE:How to do multi-select in iReport 3.6 09/10/2009 20:31
zamudio,
That's a bug in the "Report query" tab. Please enter a bug for it in the tracker.
The work around is simple enough, but it's certainly annoying:
1. Do not use "Automatically Retrieve Fields" (I hate that option anyway.)
2. Remove the $X syntax; click Read Fields; add the $X syntax back.
The report works fine with $X syntax (both in iReport and in JasperServer). It's only the query panel that doesn't work correctly.
Regards,
Matt
mdahlman
Project Roles
Posts: 790
Karma: 153
#63641
RE:How to do multi-select in iReport 3.6 09/10/2009 21:49
That works!
One more question, how do you pass a Collection field to a dataset or a subreport?
zamudio
zamudio
Project Roles
Posts: 4
Karma: 1
#63730
RE:How to do multi-select in iReport 3.6 09/12/2009 21:42
Found it!
To pass a Collection parameter to a subreport or dataset enter it as the following expression:
If your report language is Java, pass the parameter as $P{ParameterName}
If your report language is Groovy, pass the parameter as $P{ParameterName}.toArray()
zamudio
Post Edited by zamudio at 09/12/2009 22:02
zamudio
Project Roles
Posts: 4
Karma: 1
#70420
RE:How to do multi-select in iReport 3.6 02/01/2010 17:06
hi there!
i'm trying to do the same thing as zamudio but in my case, the values are Integer.
i created a parameter called "yearsCollection" of type collection and the Default Value set to new ArrayList(Arrays.asList( new Integer[]{new Integer(2008),new Integer(2009),new Integer(2010)} ))
i have a column in my database called "year" of type int..
i passed the parameter value to the query like this:
$X{ IN, year, yearsCollection}
when i run the report and in the prompt window i click in the "Use Default" button, the reports fills correct, but when i try to set values in the prompt text field (ex: 2008,2009) it crashes and the following error appears:
Implicit conversion from datatype 'CHAR' to 'INT' is not allowed.
=/, i read somewhere that the jasper assumes that the values datatypes passed to the $X{ IN, year, yearCollection} are supposed in the runtime, so i don't know how to manage this issue...
i also tried this way: "year IN ($P!{yearCollection})" with no success
i'm using iReports 3.7 and JasperServer 3.7
i hope someone can help me!