multi select input control based on diff table

[manshack_one]

I've got a report in iReport 3.6 that pulls all records from a table based on a date range that I have parameters and input controls for.  This works fantastic.  I need to move one step ahead and create an input control so the users can filter it down to multiple locations.  These locations are stored in a separate table.  I've never used a multi select input control before so I'm not sure how to make this work.  The query designer in iReport is only pulling fields from the one table with the data.  How can I create an input control so the users can select multiple locations from the static list stored in the locations table?

[Giulio Toffoli]

The best solution is to pass the set of locations as Collection (i.e. as java.util.List).

In the query use the sintax:

... where  $X{IN, column_name, param_name}

Look in the forum for an explanation of the $X{} syntax.

param_name is a parameter of type Collection

 

Giulio

[manshack_one]

So what I have works for me in iReport:

Select * from HLD
 Where tbldateentered between $P{begindate} and $P{enddate}
 And $X{IN, tblUnit, unitselect}
 Order by tbldateentered

unitselect is an input parameter set as collection and to be used as a prompt.  This worked and when prompted I could manually type in location names i.e. "Beto, Central, Clemens".  Problem is I want "unitselect" to actually list the available locations.  I'm guessing this is where the input control in Jasperserver comes into play?  Will I need to create the multiselect input control based off a query of the location table to have the list populated?

[manshack_one]

I'm answering my own questions again.  I went into jasperserver and created a multiselect query input control.  I set the query to pull all the location descriptions.  Opened the report and can see the list, choose the ones you want and voila!  Instant ad-hoc report. 

The more I learn the more I like about Jasper.  :)

[manshack_one]

How do you pass the collection to a subreport?  I added it in "copy from master" list in the main report properties, put the x${} syntax in the query but this parameter is a person's name "Manshack, Robert".  I think the problem is that I need to pass the whole thing and it's breaking on the comma and trying to pass "Manshack" and "Robert".  The name is stored "Manshack, Robert" in the database as a varchar.  I can make the list come up in jasperserver but it bombs when it tries to fill the subreport.

 

[manshack_one]

allright, i'm officially having a conversation with myself.  :)

I looked more closely at my error and it was actually saying it couldn't find the subreport.  When I originally uploaded the main report it asked to upload the subreport at that time.  It worked at that point.  After changing the file and going to jasperserver and pulling it into the server from there it apparently changed the path to where it was sitting on my local pc and was error-ing out.  That being said i couldn't tell you if the parameter for that report was going to work or not.  I went back to the original version and just uploaded that report from scratch and added the input controls back in real quick.  I was trying to filter this report by the name instead of the location.  It's not that important but it does raise a question about handling data that has a comma in it.