srang Posted April 24, 2015 Share Posted April 24, 2015 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] Link to comment Share on other sites More sharing options...
Solution srang Posted April 27, 2015 Author Solution Share Posted April 27, 2015 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!!! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now