How to use "multi-select query"?

[leeyuiwah]

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.

Clement
 

[anandharaj]

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>

</parameter>

3. Finally your SQL where clause should be:

WHERE type IN ($P!{MYPARAM_PROCESSED})

Post Edited by Anandharaj @ Raj at 12/19/08 08:49

[leeyuiwah]

Anandharaj,

 

Thanks a lot! Will try your suggestion.

 

Clement

[leeyuiwah]

Hi,

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

((java.util.ArrayList)parameter_param_ArrayList.getValue()).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>"

Thanks!

Clement

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.

Code:

"'" + $P{ha_ip_String_ArrayList}.toString().replaceAll("[\[\]]", "").replaceAll(", ", "', '") + "'"This will get[135.112.1.1, 66.67.1.1]converted into'135.112.1.1', '66.67.1.1'

[swood]

You missed the "clean up of input param" step.

 

Here is a full discussion on the subject of collections and SQL in clauses. http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=15887

 

Sherman

Jaspersoft

[leeyuiwah]

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
instead.)

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!
 

[mdahlman]

Clement,

 

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:

...
WHERE
     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.

 

Regards,
Matt

[leeyuiwah]

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

 

Thanks!

 

 

[mdahlman]

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.

 

Regards,
Matt

[leeyuiwah]

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

jasperserver-pro-3.0.1-linux-installer.bin
 

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
/opt/jasperserver-pro-3.0
[root@mh-tmp-central jasperserver-pro-3.0]# find . -name demo
./java/demo
./scripts/js-catalog/resources/PersonalFolders/demo
./scripts/oracle/js-catalog-oracle/resources/PersonalFolders/demo
 

Thanks anyway!

Clement

 

[mdahlman]

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.

 

Regards,
Matt

[leeyuiwah]

Matt

Thanks for your info.

 

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

 

Thanks!

 

Clement

[lucianc]

leeyuiwah
Wrote:

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.

HTH,

Lucian

[shivasakthi18]

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 ...!

Thanks,

Sivasakthi.