Use a list parameter in aquery

[libbysharf1]

Hello,

I would like to send a list of accounts as a parameter to the report, and use this list in the report query (using the "IN" key word).

I'm using this in my java code:

        List<String> accountNames = getAccountNames();
        params.put("accounts", accountNames)

        byte[] data = JasperRunManager.runReportToPdf(getJasperRoot() + PdfReportNames.ACTIVITY_STATEMENT_REPORT, params, conn);

 

I expected to be able to define a list parameter in iReport and then use it in my query as in:

select * from SOMETHING where ACCOUNT in $P{accountNames}

but I get an exception:

Error: Report design not valid: 1.Parameter type not supported in query: accountNames class java.util.list
 

[despec]

Check out the documentation for using the special $X variable.

 

David

[libbysharf1]

Thanks David, but I already found another solution.

I'm sending a String parameter with the values of the list seperated by commas. (ie "'a','b','c'") and use it in the query with:

"ACCOUNT_ID" IN ($P!{accountIds})

[sachintaware]

Hello libbysharf1

I am trying to do a similar thing and stuck at a place.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.Also I want to know how did you add the java code to the ireports for the list part.

Please reply at the earliest.

Thanks

Post Edited by sachintaware at 07/28/2011 10:15

[libbysharf1]

Hello sachintaware,

Well, I'm actually a newby to iReport, and I don't quite understand how you used the hyperlink.

But if it helps, I can tell you how I send the value of the parameter "accountIds" and created the PDF report from my java code. (See the Code section)

You would need these jars:

groovy-all-1.7.5.jar

iText-2.1.7.jar

jasperreports-4.0.0.jar

jasperreports-fonts-4.0.0.jar

I didn't use java code in iReport, just definded the parameter "accountIds" as String (in the "properties" window of the parameter) and used it in the query WHERE "ACCOUNT_ID" IN ($P!{accountIds}).

I think that when you use the $P!{} instead of $P{}, it injects the String value of the parameter into the query. So if accountIs = "1,2,3", the result query will be: WHERE "ACCOUNT_ID" IN (1,2,3).

 

 

Code:

    private String createAccountIdsToString(Set<Long> accountIds) {        String accountIdsToString = "";        for (Long id : accountIds) {           accountIdsToString += ",";           accountIdsToString += id;        }        accountIdsToString = accountIdsToString.substring(1);        return accountIdsToString;    }HashMap params = new HashMap();params.put("accountIds", createAccountIdsToString(accountIds));java.sql.Connection conn = getConnection();byte[] data = JasperRunManager.runReportToPdf(reportName, params, conn);

[sachintaware]

Hello libbysharf1

Thaks you very much for quick response.Even I am a new user of jasper/ireports like you.

This is the hyperlink code:In bold are the parameters passed through the hyperlink.

These are two statuses basically.In SQL query I have something lke this: where statustitle in ($P!{statustitle})

<hyperlinkParameter name="statustitle">
                        <hyperlinkParameterExpression><![CDATA["Offer Accepted"+","+"Offer Made"]]></hyperlinkParameterExpression     </hyperlinkParameter>

In the Default expression for the parameter statustitle (parameter name) i give the expression:

"('"+($P{statustitle}.replaceAll( ",", "','" ))+"')"

It works fine in ireports but fails in Jasper server.If I place the parameter over the report it shows the value but just it is not passed

to the query.I am not sure what modifications are needed to the query.

Also it would be helpful if you give me more details regarding how did you use the java code for parameters alongwith ireports.

Thank you

[sachintaware]

Hello libbysharf1

I understood your explanation for the $P!{}

"I think that when you use the $P!{} instead of $P{}, it injects the String value of the parameter into the query. So if accountIs = "1,2,3", the result query will be: WHERE "ACCOUNT_ID" IN (1,2,3)" 

I use this expression in the default field  "('"+($P{statustitle}.replaceAll( ",", "','" ))+"')" 

to bring it to the ,(comma) separated form.

This works in ireport when I enter the values in the parameter prompt.But,when I am using the same query i.e

"where statustitle in ($P!{statustitle})" on the jasperserver it Fails with an error "Error executing SQL statement"

I am using SQL server 2005 as the database.Is there a mistake in the query syntax? or Do I need to make changes in the parameters.

I also posted the same problem on the topic below,but didnt get any response.It is related to the similar problem.

http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=51292

I even tried the $X query but that too didn't work.

Please if you can throw light over it,it will be a good help.

Also,if anyone from jasperadmin can help me would be good as I am stuck here from quiet a while.

Thank you.

 

 

[libbysharf1]

Hi,

I don't think there's an error in your query...

Did you try testing it with a dummy string like:

where statustitle in ('a','b','c')

Also, what is the type of statustitle? If it's a varchar it should be as I stated before, if it's a number it should be as

where statustitle in (1,2,3)

 

Hope this helps,

Libby

[sachintaware]

Hi

Its NVARCHAR,but that shouldn't make a difference according to me.
Also,I tested it on the Database engine(Mssql server) and ireports.

select (column names) from (table names)  
where clientname = 'XYZ' and year=2011 and month='May' and statustitle in ('CV-Sent','Duplicate')

This works fine and also the same in ireport which is like i stated in the above posts.
I don't know why jasperserver throws a error for it (i.e $P!{}) if I remove the '!' symbol it works only for single parameter and not for list.If I print them as a field the values get printed,but are passed as null to the query.

I am just badly stuck over it.In case you have a workaround solution or any link for this pls share it.
Thank you very much for your inputs and prompt reply .

Have a good time.

Sachin
 

[libbysharf1]

what is the type of the parameter statustitle? (if you mark the parameter, it will be shown in the proprties window)

[sachintaware]

 

Ok,sorry for that.

I thought you were talking about the database datatype from where I am fetching them.

In ireports the datatype I have kept to java.lang.String.

And in the default expression i have the logic for sending the comma separated values after entering the values.

Thanks

Sachin

[libbysharf1]

First time I meant in DB, second time in iReport :-)

I'm not sure you can use the default value like that...

Maybe try to define a variable, say $V(status) and give it the value of

"('"+($P{statustitle}.replaceAll( ",", "','" ))+"')" 

then use it as "where statustitle in ($V!{status})"

Also, try to print the value of $P{statustitle} by using a text field and preview the result

[sachintaware]

Thanks

Will try it out and let you know.

 

Sachin

[netopeto]

Hi, I use ireport 5.6.0 and I had a similar problem with a numeric list. My database is Oracle 10g.

I used the follow sentence

       Where nvl2 ( $P{NumericList2string},  INSTR ( ',' || $P{NumericList2string} || ',' ,  ',' || mytable.myNumericField || ',' ) , 1) > 0

nvl2 give two options return value for null or empty string, I return 1 if is null because I need all records when NumericList2string is empty

nvl2 give us two options of return value for null or empty string, I return 1 if is null because I need all records when NumericList2string is empty

Sorry for my English :)