Dynamic List Input control in JasperReport 5

[Rashmi Nagaraja]

I have 100,000 account numbers. I want to randomly give any 5 (as per my requirement. Could be 1 or 2 or 20) account numbers and see the information about them.

I have tried the following.

I tried with string input control, cascading input control with one string and one list parameters. But none of them works.

1) String Input Control: I tried entering the 3 random account numbers comma separated. But my query dosent accept the same since I have to use a list parameter and $X variable. ($X{IN,acct_no,test}).

2) Cascading Input Control: I thought let me take a string input control and a list list input control and use the string input control within the list input control. So I tried like the following.

• Created a parameter and an input control with string datatype (where I can enter comma separated account numbers). The parameter name is $P{account_no}
• Created a list parameter and an input control with list datatype as an multiselect query using the string parameter as below. The parameter name is $P{test}
  • select account_no from customers where account_no IN ($P{account_no}) Note that I have used the first parameter. This works fine for only 1 account number and not for 'n' account numbers.
  • I also tried something like select account_no from customers where ($X{IN,acct_no,account_no}) . For obvious reasons this does not work.

I am using Jasper 5.0.1 Is there a way to dynamically populate a list based on the input values.

Could anyone kindly let me know how to achieve my desired output?

Any help would be appreciated. Many Thanks.

[marianol]

You can enter your list of parameters in a "String" parameter (let's say $P{accountsCSV}) and then parse that into a java.util.List , so you can use it in $X{}

To parse it you create another parameter in the report (let's go with $P{accounts}) set the type as java.util.List and set the default value to something like:

Lists.newArrayList(Splitter.on(",").trimResults().split($P{accountsCSV}))
or 
 new ArrayList<String>(Arrays.asList(string.split(" , ")));

Set your report language to Java :)

 

[alison.oxton]

You can also use regular expression if you are querying the database:

WHERE customer_id IN ( SELECT REGEXP_SUBSTR($P{customer_id_list}, '[^,]+', 1, LEVEL) AS list_of_cids

     FROM   dual  CONNECT BY LEVEL <= REGEXP_COUNT($P{customer_id_list}, ',') + 1)