Convert result set to list and use as parameter

[srang]

So I'm using a report with a main data set and a table dataset. The main dataset is just a list of id's based on a parameter passed in at run time. What I would like to do is use the id's retuned in my main dataset in the where clause of my table dataset. Basically I'm doing:

Main dataset = select peopleid from people p where orgid = $P{OrgID}[/code]

, (except the query is actually more complicated than this or I would have just built it into the db view)

 

 

table dataset= select * from sales s where $X{IN, s.peopleid, <mainresult>.peopleid[/code]

 

[srang]

Thanks to a comment on this post I was able to figure it out. So what I ended up doing is creating a report level query to get my base result set of id's. These ids were stored as an integer field. Then I created a report level variable with a class of ArrayList (variables don't have nested classes for some reason). Then in the initial value expression I put 'new ArrayList()' and then calculation I put System. Finally for the actual variable expression I put $V{<myvarname>}.add($F{<myfieldname>}). Then in my table dataset I do the basic $X{IN, <subreportfieldname>, <newparamname>} then create a tabledataset level parameter with the name I just used in my query. And to top it all of I linked up the parameter and report variable in the table configuration. Hope This helps someone!!!