Passing collection parameter in drill-down reports

[tdi120]

Hi guys,

first of all, amazing job! I just love JasperReports, and how it all integrates with ireport and JasperServer.

I have used this solution for building up a load testing reporting server, and until now, everything run just perfect.

Last week i started optimizing the reports, and i got myself in the following problem, for which i could not find a solution till now.

First of all, i am running Ireport and JasperServer 3.5.0

What i am trying to do is the following:

I have an input control in the repository. the type is "multi select query"

This query returns some id's from the database. (like 1,2,3,4,5,6...)

My first report uses this input control, and is the starting report. the user can select one id, or multiple, depending on what he wants to analyse.

Suppose i would like to see report 1 with id's 3,4 and 5.

Everything works flawless here.

I am now trying to do a drill down report, by sending the input control above as a collection.

Therefore, i have my item linking with the following parameters:

Hyperlink Type: ReportExecution

run_id: $P{run_id}

 

Where run_id is the input control, and is defined in the first and second report as a parameter of type " collection "

The query in the second report looks like this:

select id,testrun_id from performance
where $X{IN,testrun_id,run_id}
order by id

 

Now, when trying to run the second report from the first report, using the hyperlink, i can clearly see that the collection values are not passed in the url.

When opening the second report, the run_id isn't selected (as an input control) If i print out the value, using $P{run_id}.toString(), the value is null

I have tried this also with lists, still no result.

If i use a String parameter in the first report, and assign it the value $P{run_id}.toString(), and use this in the hyperlink parameter (as string of course), the url looks like this:

http://jasperserver:8080/........&string_run_id=[1,+2]

The notation is already unusable, so i cannot use this workaround.

Another thing that i tried, was assigning a local parameter, of type list, with the default expression of:

new ArrayList(Arrays.asList(
new Integer[] {new Integer(25260), new Integer(25261)}
))

When i run the second report with this parameters, again, the values are not transmitted.

 

I see two problems here:

1. Is it possible at all to send collections as parameters, and use them furtheron in a query, with expressions  like $X(IN,val_to_compare,passed_val)
2. If yes, should the values of the collections be visible in the url?
3. How should a collection look like ? If i try putting values manually, it works with one value, like "&run_id=24", but any combination of multiple values, like "&run_id=24,25,26" will fail"

I gave this problem more than two days of my time, and i begin to think that there is a bug somewhere.

I also want to mention that collection parameters WORK in SUBREPORTS ( as i said,my problem is with drill-down reports)

Keep up the good work.

Hope somebody can answer my problem. all other forums and blogs did not.

Regards,

Alex

 

 

 

 

[Giulio Toffoli]

Hi Alex,

I actually never tries using a Collection over a drill down, but I think the best solution would be to transform your collection in a string when it is passed as parameter, and converted in a collection when you are in the report.

Here is my hint:

1. Create a simple class to transform in a string a collection of ids:  the result should be something like 1,2,3,4,5,6,7

2. Create simple class to transform in a Collection a string of ids [hint:  Arrays.toList(  mystring.split(",") ) ]

Now we have a way to send a collection into a parameter of type String, and a way to populate a second parameter from the first one:

$P{param1}  <-- String

$P{param2} <-- Collection,   Default expression   (($P{param1} != null)  ? MyClass.toCollection( $P{param1} ) : null

This should solve the problem.

Giulio

[tdi120]

Hi Giulio,

thanks for replying.

I think your solution makes sense. I will try that as soon as i find some spare time.

In the meanwhile, i went with the simpler solution:

I am converting the collection into a string, and format it accordingly for my query. As noticed when printing out the collection into a string, the format is:

[value, +value1, +value2]

So the only thing i needed to do was to get rid of the brackets. The pluses and whitespaces will make no difference in the sql query.

Therefore i used a string parameter as hyperlink parameter, with the following expression:

(($P{myid}.toString()).replace(‘[',' ')).replace(']‘,’ ‘)

This will now return the parameters i need in the proper format, something like:

http://jasperserver:8080/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=%2Freports%2FPerformance%2FClassloading&coltostring=+25261%2C+25260%2C+25259+&reportLocale=en

Problem solved, will return with an answer for your solution also.

Thanks a lot once more!

Alexandru Ersenie

[semmelhaack]

I work with Jaspersoft Studio 5.6 and try to pass multiple parameters to an X{IN ..} clause. Is there a new solution? Or do I have the described solution? If yes how to I create the collection?

[akash.sky96]

Hi Alexandru Ersenie,

I am also stuck the same  problem but I have a question if iI passed URL by converting collection parameter to string and removing brackets it still not select parameter from multi-select for e.g my parameter after your simpler solution looks like 

http://jasperserver:8080/jasperserver/flow.html?view&runRemote=true&LocationID=1&coltostring=4060,4070,4080

still not selecting any value from muliselect parameter value.

please reply back alexander.

Thanks a lot once more!

Akash Yadav