multi select list parameters

1

  hi there,

           i m nebie to iReport & jasperserver, i m using JasperServer 3.1   here i m facing multi select list input values problem.

the report is working fine for single input whenever i m trying to use multi select list then i facing the problem jasperserver shows "the Report is Empty".

 

in iReport  i m using the following method   Parameter Properties are

--> Parameter Name : MyParam

-->Parameter Class Type : java.lang.String

-->Use as a Prompt: Checked

-->Default value Expression : $P{MyParam}  (using Express Editor)

-->Parameter Properties   : Name - MyParam

                                                  Value - Jan

 

in my query report i m using like this:

 

select a, b, c, d

FROM table1

WHERE a IN ($P{MyParam})                        ( i try like $P!{MyParam / (P!{MyParam})  )

GROUP BY a, b, c, d

ORDER BY a, b, c, d;

 

 

whenever i m using a static query like :

 

select a, b, c, d

FROM table1

WHERE a IN ('Jan','Feb','Mar') 

GROUP BY a, b, c, d

ORDER BY a, b, c, d;

this is working fine, so i want to know clear detail about how to use multi select list parameters dynamically, can any one help me out this?.

 

Thanks in Advance,

Shivac

Abiba Systems Pvt Ltd.,

shivac's picture
32
Joined: Mar 23 2009 - 11:21pm
Last seen: 10 years 8 months ago

16 Answers:

1

Hi,

    Multi Select parameters should have type as Collection or Java Array objects not primitive type. I suggest using $X{} syntax since it will take care, when no values are selected as well. Please see jasperreports definitive guide or search in the forum for $X{ which will guide u further.

Ram

rmukkamalla's picture
Joined: Oct 23 2008 - 1:56pm
Last seen: 11 years 1 month ago
0

 hi 

    Thanks for ur quik replay, i changed type as java.lang.Object and now after gave the input it produce an error massage like  

   "Error filling print... No clause function for id MyParam found

net.sf.jasperreports.engine.JRRuntimeException: No clause function for id MyParam found "

But i gave a param name as MyParam and default value Expresion is also like this $P{MyParam} and i changed the Query editor as   where a = ($X{MyParam})   any suggestion can help me out this.

 

Thanks in advance 

Shivac

shivac's picture
32
Joined: Mar 23 2009 - 11:21pm
Last seen: 10 years 8 months ago
0

Hi,

As rmukkamalla said, you need to define the multi-select parameter as "Collection" and your SQL should look like "WHERE columnname IN ($P!{parametername})

I dont have the sample right now, will provide later

anandharaj's picture
Joined: Oct 18 2006 - 5:02pm
Last seen: 6 years 2 months ago
0

 hi Anandharaj,

                        I tried both the possibilities 

                         "SELECT a,b,c,d FROM table1 WHERE a IN ($P!{MyParam}) GROUP BY a,b,c,d ORDER BY a,b,c,d " -- and parameter class as "java.util.Collection as well as java.lang.Object"  and my default expression is "$P{MyParam}"

as well as "SELECT a,b,c,d FROM table1 WHERE a IN ($X{MyParam}) GROUP BY a,b,c,d ORDER BY a,b,c,d" -- and parameter class as "java.util.Collection as well as java.lang.Object"  and my default expression is "$P{MyParam}"

both are not working. where i commit mistake can u guide me.

if I use Collection then it shows "Error executing SQL statement for : classic"

and lang.jang.Object  it shows  "No clause function for id Month found".

help me out this..

 

Thanks in Advance

shivac

 

shivac's picture
32
Joined: Mar 23 2009 - 11:21pm
Last seen: 10 years 8 months ago
0
Please provide the JRXML file so that i can take a look
anandharaj's picture
Joined: Oct 18 2006 - 5:02pm
Last seen: 6 years 2 months ago
0

 ya i here with attached my .jrxml file.

 

 

Thanks ain advance

Shivac

shivac's picture
32
Joined: Mar 23 2009 - 11:21pm
Last seen: 10 years 8 months ago
0

See here regarding the usage of $X.  What you want is WHERE $X{IN, v_name, MyParam}

HTH,

Lucian

lucianc's picture
7154
Joined: Jul 17 2006 - 1:10am
Last seen: 3 days 14 hours ago
0

Hi Lucianc, Thanks for the info, today only i notice about $X{} but currently all my reports using -> "WHERE columnname IN ($P!{parametername})" and so far its work fine (but i have a method that will do the "clean-up" - put single quote for each value so that it become: 'a','b') Probably this is why my "$P!{parametername}" is working




Post Edited by Anandharaj @ Raj at 03/26/09 01:10
anandharaj's picture
Joined: Oct 18 2006 - 5:02pm
Last seen: 6 years 2 months ago
0

 Hi lucian

              Thanks for ur quick replay, I tried  WHERE $X{IN, columnName, paramName} . This gives all the rows which are there in a table, then Imake a query like this  WHERE v_name $X{IN, columnName, paramName} then it give a error massage that 

Error filling print... Error executing SQL statement for : classic
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : classic 

my query is 

"SELECT v_name,  v_minval,v_maxval, v_defval FROM table1 WHERE  v_name $X{ IN ,v_name,MyParam}

GROUP BY    v_name,     v_minval,     v_maxval,     v_defval

ORDER BY     v_name ASC,     v_minval ASC,     v_maxval ASC,     v_defval ASC"

What the /mistake i commit can u help me please..

 

Thanks in advance

shivac

shivac's picture
32
Joined: Mar 23 2009 - 11:21pm
Last seen: 10 years 8 months ago
0

 Hi Anandharaj

                          For multiple selection parameter,  how can I use $P!{parameterName}. what is clean-up have to use any script to perform clean-up?. can u help me out this, can u post your samples so that I will get clear understand.

 

Thanks in advance

Shivac

shivac's picture
32
Joined: Mar 23 2009 - 11:21pm
Last seen: 10 years 8 months ago
0
your query must be: SELECT v_name, v_minval, v_maxval, v_defval FROM table1 WHERE $X{IN, v_name, MyParam} GROUP BY v_name, v_minval, v_maxval, v_defval ORDER BY v_name,v_minval,v_maxval,v_defval The only way that sql can return the whole table is if you select all the possible values in the input control or all the values for column v_name are the same and you choose that value.
angusmiller's picture
Joined: Jul 24 2008 - 1:00am
Last seen: 11 years 4 months ago
0

anandharaj
Wrote:

Hi Lucianc, Thanks for the info, today only i notice about $X{} but currently all my reports using -> "WHERE columnname IN ($P!{parametername})" and so far its work fine (but i have a method that will do the "clean-up" - put single quote for each value so that it become: 'a','b') Probably this is why my "$P!{parametername}" is working

If you manually prepare the parameter string to contain quotes and commas, $P!{..} would work.  $X{IN, ..} was introduced so that people would no longer need to perform this parameter processing.

Regards,

Lucian

lucianc's picture
7154
Joined: Jul 17 2006 - 1:10am
Last seen: 3 days 14 hours ago
0
Hi Shivac, i think Lucianc's explanation is answered your question. So, just use the $X{} instead of $P!.
anandharaj's picture
Joined: Oct 18 2006 - 5:02pm
Last seen: 6 years 2 months ago
0

 Hi

     Thanks for your Help. $X{} is Working fine for me.

 

Thanks in advacne 

Shivac

shivac's picture
32
Joined: Mar 23 2009 - 11:21pm
Last seen: 10 years 8 months ago
0

  I've tried to use $X in the SQL statement of the report and it works great. The problem is when I try to use multiple value list in expression condition for "sql_adddition_string" hidden parameter to build dynamic SQL. In my case I want to implement "ALL" item in the list. 

p_group is defined as java.util.List

sql_adddition_string is defined as java.lang.String

 

on "sql_adddition_string" I tried the following default values expressions 

1==2 ? " and 1=1" : "and $X{IN, group_id, p_group}" - DOES work and $X is properly translated into "group_id in (choice1, choice2, choice3)

however when i try to use

$P{p_group}.contains("ALL") ? " and 1=1" : "and $X{IN, group_id, p_group}"  - it does NOT work and put "NULL" in place of $P!{sql_adddition_string} in my SQL query.

 

Am I missing something here? Why I can not use boolean conditions on java.util.List type?

 

Thanks a lot,

Vladimir

vstoyak's picture
13
Joined: Dec 23 2008 - 11:05am
Last seen: 10 years 11 months ago
0

vstoyak
Wrote:

however when i try to use

$P{p_group}.contains("ALL") ? " and 1=1" : "and $X{IN, group_id, p_group}"  - it does NOT work and put "NULL" in place of $P!{sql_adddition_string} in my SQL query.

Did you define sql_addition_string after p_group (in the list of parameters)?

Regards,

Lucian

PS:  Please start new threads if your message is not directly related to the original topic of the thread.

lucianc's picture
7154
Joined: Jul 17 2006 - 1:10am
Last seen: 3 days 14 hours ago
Feedback
randomness