Jump to content

How to use "multi-select query"?


Recommended Posts

In JasperServer, if I add an input control that is of type
"multi-select query", then what is the type of the result?

If, for example, I want to have a parameterized report in which the
SQL where cause contains conditions like this

where type="a" or type="b" or type="c"

and I want people to use a checkbox multi-select query to select the
values "a", "b", "c" from a list of many values {"a", "b", "c", "d",
...}, is this possible? How do I translate back the result of the
multi-select query back to the where cause?

I have googled the web and read the JasperServerPro user guide but
could not find an answer.


Link to comment
Share on other sites

  • Replies 13
  • Created
  • Last Reply

Top Posters In This Topic

Yes, is possible...

1. Create the parameter with "collection" type:

   <parameter name="MYPARAM" isForPrompting="true" class="java.util.Collection"/>

2. Now we do some clean-up of input param - put single quote

<parameter name="MYPARAM_PROCESSED" isForPrompting="false" class="java.lang.String">

<defaultValueExpression ><![CDATA[$P{MYPARAM}.toString().replaceAll("[\[\]]", "'").replaceAll(", ", "', '")]]></defaultValueExpression>


3. Finally your SQL where clause should be:


Post Edited by Anandharaj @ Raj at 12/19/08 08:49
Link to comment
Share on other sites

  • 3 weeks later...


I tried it, but the parameter of type java.util.ArrayList got this value when I print $P{param_ArrayList}.toString()


but not the real value of the Jasper parameter $P{param}

More generally, since java.util.ArrayList (an implementation of the interface java.util.Collection) is now a parameterized type, how should we fill in the value for the field "Parameter Class" for this Jasper parameter?

I tried just filling in "java.util.ArrayList" and I got the above mistake.

I tried also filling in "java.util.ArrayList<String>" and I got "java.lang.ClassNotFoundException: java.util.ArrayList<String>"



P.S. I found that (in my Java test program) I had to use a different processing for the string to get the "collection to mysql string" right. The method that I used  was shown in the code section below.

"'" + $P{ha_ip_String_ArrayList}.toString().replaceAll("[\[\]]", "").replaceAll(", ", "', '") + "'"This will get[,]converted into'', ''
Link to comment
Share on other sites

Dear Sheman,

I already did the "ckean up of input param" step (as I said I found that
the original replaceAll() method suggested by Anand (Post #49912) did
not seem to work and I was using a different replaceAll() (see the code
listed in Post #50492).  I think the problem is with how Jasper/iReport
treat Java generics (i.e. Collection<String> instead of just Collection).

Luckily, however, I tried the link that you provide and in that thread
I found out how to use a helper class (Post #16497) (also thank you for
your answer in Post #50672 http://tinyurl.com/8b7kzg) and that solved
my problem.

(And I also resorted to NOT using Java generics but using type cast

BTW, for the record, in case someone else comes to this.  Please note that
after you created the helper class, compiled the .class and package the
.jar file, you need to make the .jar file available to Jasper.  If you use
iReport, this means that you have to do "Tools->Options->Classpath->'Add
JAR'".  If you use JasperServer, follow the instruction of Post #16507
in that thread.

Thanks for all your help!

Link to comment
Share on other sites

  • 2 weeks later...



It sounds like it's all working well for you. For others who may read this post later, I'd also like to mention the $X syntax. It's discussed in more detail in other posts. But the basic idea is that you can use this in your SQL query:

     col1 = $P{SingleValueParam}
     and $X{IN, col2, CollectionValueParam}


It has the logic built-in to expand out your collection into a comma separated list of values. Likewise it can be used with a NOT IN clause. The multi-select input control is designed to handle a collection, so it's easy to use them together. There are examples of reports using these in the samples that ship with JasperServer Professional.



Link to comment
Share on other sites

Interesting ...


So it looks like X is NOT a parameter name, and we have to use $X literally to get this effect, right? So $X is more like a macro that gets expanded to the semantics that you mentioned.


I could only find one example, which is jasperreports-3.1.0/demo/samples/query/QueryReport.jrxml


Note that the above is the non-professional package. In my professional package, I could not even find the same directory .../demo/samples





Link to comment
Share on other sites

Correct, $X{} is very much analogous to $P!{}. It expands out to the required syntax for an IN statement.


You wrote, "In my professional package..." I believe you're referring to JasperReports. But I was referring to JasperServer Professional. You can get that off of the jaspersoft.com site. It includes the sample reports I was referring to.



Link to comment
Share on other sites

Dear Matt,

Thanks for explaining to me the use of $X!

By the way, I do have a JasperServer Professional package.  The installer's name is


But still, in the installed directory (/opt/jasperserver-pro-3.0) (I installed a 3.0.1 and then upgraded it to 3.1, but the top directory did not change) I could not find the same "demo" directory (jasperreports-3.1.0/demo/samples/query/QueryReport.jrxml).  When I did a search by the unix "find" command, I got this

[root@mh-tmp-central jasperserver-pro-3.0]# pwd
[root@mh-tmp-central jasperserver-pro-3.0]# find . -name demo

Thanks anyway!



Link to comment
Share on other sites

Ah, I see what you mean now.


The reports are not easily accessible in the file system. They are samples that get loaded into the repository. Login to JasperServer as jasperadmin. View the repository and look for the folder /SuperMart. You can login as demo/demo to see the reports running in a dashboard.



Link to comment
Share on other sites


Via JasperServer I could see the reports running, but how I can retrieve the source of these reports (the JRXML files)?

There is feature (currently) do this using the web UI, but a simple way to do it is to use the iReport plugin to connect to JasperServer, and to get the JRXMLs in iReport.



Link to comment
Share on other sites

  • 3 years later...

If you have an SQL select with all result set to be passed input controle with diffrent DB, Then use as follows.

define a parameter as SQL Single select and define the same as String then use following in SQL.

select group_concat(id separator '\',\'') as user_id from TABLE_NAME where countrycode = 'INDIA'

I hope that helps ...!



Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...