Issue with Parameters that are from a List

[lrewell]

Hello

I am using the JasperClient c# library to call the report webservices.

When I pass parameters that are in a list and indicate this by setting the value to true the report does not work correctly.

I have set up a parameter as follows

Parameter Name

A

Parameter Class Type

java.util.Collection

In my report query I have this statement

WHERE contractdate::date BETWEEN $P{startDate}::date AND $P{endDate}::date

AND value IN $P{A}

This is the error I get

com.jaspersoft.jasperserver.api.JSExceptionWrapper: net.sf.jasperreports.engine.
JRException: Incompatible java.lang.String value assigned to parameter A
in the ReportByDate dataset.

I have seen on the forums this solution

Create another parameter B of type java.lanfg.string with this default expression

org.apache.commons.lang.StringUtils.join($P{A}.toArray(),",")

However I cannot get this to work either.

What is it I am doing wrong?

Any help much appreciated.

Thanks Lee

 

 

 

 

 

 

 

 

 

[Gaby38]

Hello Lee,

I had a similar issue. I tell you how I fixed it : there are certainly other ways, this is mine :

- I use a string parameter between parenthesis for my list of value : e.g "(1,2,3,4)"

- in the report query I use the specific parameter expression "$X{}" : for instance in your case suppose that your parameter AA has been initialized with the string "(1,2,3,4)", then in the where clause you put something like that :

"where $X{IN,table.value,AA}"      if you want the field "value" of "table" to take its values in (1,2,3,4)

You can do a search in jasperserver forum on "$X" to get more info about the use of this specific kind of parameter.

To use a string instead of a collection allows to transmit it in a URL : it's why I chose this method

Hope this will help you

Gaby

 

[lrewell]

Hello Gaby

Thanks for you reply.

I saw the use of the $X parameter on the forums and tried to get it to work previously with out much luck.

So when I saw your solution I was glad as I seem to be on the right track.

When I tried what you suggested I cannot get it to work.

My query now looks like this

WHERE contractdate::date BETWEEN $P{startDate}::date AND $P{endDate}::date
AND $X{IN,value,A}

Paramter defined as

A

java/lang.string

When I create the values in code I have

"(value1,value2)"

When I run the report I get this error.

        at java.lang.Thread.run(Thread.java:595)
net.sf.jasperreports.engine.JRRuntimeException: Invalid type + java.lang.String
for parameter A used in an IN clause; the value must be an array or a col
lection.

It seems like it is expecting a collection even though I specified the parameter A as a string. Is this something to do with the IN clause and how java handles it?

Any help would be much appreciated.

Thanks Lee Rewell

 

 

 

[Gaby38]

Lee,

I apologize! I was wrong because I mixed the two cases : string parameter and collection!

I first tried with a collection and , as far as I remember, it worked but as I said I needed to pass the parameters through an URL.

The $X{} parameter form needs effectively a collection.

What I used for the string parameter is the $P!{} form (notice the !) which expands the parameter as it.

So in your query you should try something like that :

" where table.value in $P!{A}" if A contains the string "(1,2,3)"

I am sure I used it and it worked. Now I am not in the environment where I can access my jasperserver so I cannot do cut and paste of the real query I used.

As soon as I am in the good environment, I will post you more details !

[Gaby38]

Lee,

I confirm my previous post. I have a report under jasperserver which is launched via URL and which has one of his parameters "I_ActList"  which is a list of "activities id" passed as a string and in my query I have in the where clause :

"where stat.QUARTIER_Adresse.GdQuartier=$P{GdQuartier}
and conso_jour.id_act in $P!{I_ActList}"

THis is a copy/paste of a part of my query viewed under iReport.

I_ActList contains a comma separated list of integers which are the ids of activity fields :

"(6,7,8)"  for instance

and it works.

Hope you will success in your environment

Gaby

[mateen83]

So when i pass the parameter to the report i have to place a () before and after teh parameters

[peter.rich]

Lists in url don't work in url so..................

To find all matching cities from a list of capital cities (in Australia)

I used the following to get around the need to use text in the url:

Select statement include the next line (find the city column in the locations table)

   and ( regexp_like(locations.city, $P!{p_city}) or $P{p_city} is null)

 

Note here that if the city parameter is null then all cities are included

 

In the calling program I needed to enclose within single quotes, then add each repeating value of the parameter separated by a '|' character

I ended up with the following embedded in the url:

 

&p_city='ADELAIDE|CANBERRA'

 

the single quote can be replaced with %27 and  '|' with %7C

 

Hope this helps

 

Cheers Peter

[etech2013]

Hello Irewell,

"When I run the report I get this error.

        at java.lang.Thread.run(Thread.java:595)
net.sf.jasperreports.engine.JRRuntimeException: Invalid type + java.lang.String
for parameter A used in an IN clause; the value must be an array or a col
lection."
It caused by Multiple options, so if you used the Single option with the syntax: "where $X{IN,table.value,AA}", it was wrong. Maybe you should try to use  like this:
where $P{Parameter} = '#string' 
Hope It's helpful