Jasper Report: sending part of the query as parameter

Classic Servoy
1

Hi everybody,

I need to send a list of ids (generated by a complex calculation) to my report query. The parameter is called “employee”, and it contains a String made of ids like that “12,15,22,74,88”.
It currently looks like that in the report query:

WHERE T_EXP.fld_date BETWEEN $P{dateFrom} AND $P{dateTo}
AND T_EXP.fld_personnel_id IN ($P{employee})

But when I run it, it tries to run that:

AND T_EXP.fld_personnel_id IN ("12,15,22,74,88")

and I would like to make it run that:

AND T_EXP.fld_personnel_id IN (12,15,22,74,88)
``` No quote.

I have tried with "T_EXP.fld_personnel_id IN (12,15,22,74,88)" as a parameter but same problem.

How can I do? I cannot generate the list of IDs with SQL because it's extremely complicate.
2

Apparently the error I get doesn’t come from the issue I explained in my previous post.
I have changed my report and solution code to work with only one id.
Now it looks like that:

WHERE T_EXP.fld_date BETWEEN $P{dateFrom} AND $P{dateTo}
AND T_EXP.fld_personnel_id = $P{employee}

In my Servoy solution I call my report like that:

function GenReport(arg1,arg2,arg3)
{
		var params = new java.util.HashMap();
		params.put('dateFrom', arg1);
		params.put('dateTo', arg2);
		params.put('employee', arg3);
		params.put('rpt_path',plugins.it2be_tools.server().getApplicationServerDir());

		databaseManager.rollbackEditedRecords();
		databaseManager.rollbackTransaction();
		application.closeFormDialog();
		plugins.jasperPluginRMI.runReport(controller.getServerName(),"rpt_expenses.jrxml" ,null,'viewer',params);

When I test the report in iReport it works fine. But called from Servoy I get the following error:

Unknown hyperlink target 0
Wrapped java.lang.Exception: Unknown hyperlink target 0

The parameter arg3 (in Servoy code) contains an integer and not a string. And other reports are working fine.

Any idea?

3

I managed to find why my report didn’t work with one id.
I forgot to change the ireport plugin library (jar) when I have upgraded iReport to 3.7.2 yesterday :oops:

So now I’m going back to my 1st issue with list of ids interpreted as a String.

4

Hi.
Try this:

WHERE T_EXP.fld_date BETWEEN $P{dateFrom} AND $P{dateTo}
AND T_EXP.fld_personnel_id IN ($P!{employee})

Regards Jan

5

It worked!

Thanks for this tip Jan :D

Cheers.