parameter: single list: String in "IN" operator

0

Hi,

I would like to use String list in IN operator of a query without using multiselect list as input.

In the JasperServer when I run the report I would like to use more Stirng values separating them with comma in "single value" input parameter:

value1, value2, value3,value4

... so without apostrophes. These values should be placed in the query of the report in this way:

'value1', 'value2', 'value3','value4'.

I have tried to use $P!{param}: because it is substituted in the report.

($P{param}  = java.lang.String, example query: select * from table where col1 in $P!{param}. I used default variable expression in iReport for adding apostrophes to the input string : "('"+$P{param}.replaceAll( ",", "','" )+"')". When I run the report in the Jasperserver it did not do the replacement. The report works if I write this into the parameter field:  'value1', 'value2', 'value3','value4':

After that I defined a new parameter in the iReport, let's say:  $P{pre_param} (java.lang.String) and I used the same default variable expression for this : "('"+$P{param}.replaceAll( ",", "','" )+"')".  Here I used different query in the definition of the report in JasperServer: select * from table where col1 in $P!{pre_param}. I supposed that the value in the $P{pre_param} would be automatically replaced when I ran the report. It did not work either in the JasperServer, however it worked well in iReport.

As I can see it the Jasperserver uses the default value of the parameters which are defined once in the jrxml file and it cannot be changed.

My questions are:

  1. is there any way to "force" JasperServer for applying dinamically the Default Value Expression when the report is run?
  2. Do you have any other idea how can I use comma separated string list as input parameter without apostrophes? e.g. converting comma separated values from java.lang.String to Java.util.List and than using $X!{col1,param}...

Thank you for your help in advance.



Post Edited by asdsa asdsa at 02/04/09 20:38
asdsa's picture
13
Joined: Nov 29 2008 - 11:40am
Last seen: 11 years 2 months ago

7 Answers:

0

asdsa
Wrote:

It did not work either in the JasperServer, however it worked well in iReport.

Did you have an input control defined in JasperServer for pre_param?  If so, remove it and JasperServer would not set any value for the parameter, hence allowing the default value to be used.  Also make sure that pre_param is declared after param in the JRXML.

Regards,

Lucian

lucianc's picture
7184
Joined: Jul 17 2006 - 1:10am
Last seen: 5 days 6 hours ago
0

Hi Lucian,

Thank you very much, it works! I am very grateful for your help.

Best rergards,

asds

asdsa's picture
13
Joined: Nov 29 2008 - 11:40am
Last seen: 11 years 2 months ago
0

Hi Lucian,

I checked your proposal too quickly...

It works if I print the pre_param on the screen as a field, but it does not work when I use the pre_param in this way: where col1 in $P!{pre_param}. In this case the JasperServer replaces the pre_param to null, while I can see proper value of the pre_param on the screen: ('ValueA','ValueB',''ValueC').

Could you help me please?

Thank you for your help in advance.

Best regards,

asdsa

asdsa's picture
13
Joined: Nov 29 2008 - 11:40am
Last seen: 11 years 2 months ago
0

Did you set the default for the parameter in the JRXML?

 

Sherman

Jaspersoft

swood's picture
2552
Joined: Jun 21 2006 - 12:48pm
Last seen: 7 years 4 months ago
0

Yes, if I understood you clearly... I defined the pre_param default value in JRXML (in iReport) in this way: "('"+$P{param}.replaceAll( ",", "','" )+"')"

When I run the report in JasperServer, it shows the pre_param correctly on the screen, but if I use it in the WHERE clause of the report query (where col1 in $P!{pre_param}) it is null.

Thank you,

asdsa

 

 

asdsa's picture
13
Joined: Nov 29 2008 - 11:40am
Last seen: 11 years 2 months ago
0

Hi,

I realized that it was my mistake: I defined the SQL in the JasperServer and not in the iReport. So, when I used the SQL within the iReport and I choosed the "None" at the query definition option of the Jasperserver, the report worked fine.

Thank you so much for your help in this issue.

Best regards,

asdsa

asdsa's picture
13
Joined: Nov 29 2008 - 11:40am
Last seen: 11 years 2 months ago
0

If you are still reading this.

I am facing a similar problem.I can printthe value of the parameter but it is replaced by null in the QUERY!!!

I am sending a list of values using Hyperlink and want to catch those values in the query using the IN operator.I cant use multiselect here.

The query uses this,  where status in ($P!{statustitle})

So,I want to pass multiple status values to the query through hyperlink(works fine for a single status value).I tried the same thing suggested by you but did not work.Please give me details,or cde snipet if possible of how you have got it working.

Please reply at the earliest.

Thanks

sachintaware's picture
Joined: Jul 28 2011 - 2:58am
Last seen: 8 years 6 months ago
Feedback