multi select list parameters

[shivac]

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

[rkm258]

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

[shivac]

 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

[anandharaj]

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

[shivac]

 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

 

[anandharaj]

Please provide the JRXML file so that i can take a look

[shivac]

 ya i here with attached my .jrxml file.

 

 

Thanks ain advance

Shivac

[lucianc]

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

HTH,

Lucian

[anandharaj]

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

[shivac]

 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]

 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

[angusmiller]

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.

[lucianc]

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

[anandharaj]

Hi Shivac, i think Lucianc's explanation is answered your question. So, just use the $X{} instead of $P!.

[shivac]

 Hi

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

 

Thanks in advacne 

Shivac

[vstoyak]

  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

[lucianc]

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.