Filtering dataset

[pradiptab]

Hi All

I am using jasper Report with Servoy. To develop the report using iReport 3.7.1

I have a list placed in a Group footer of  a group. The list is using a Dataset to populate the records.

Created a variable which is keeping the value of the id (key used to create Group) in each repitition of the Group.

Is there any way to filter the Dataset of the list by matching one field of the Dataset with the variable(which is keeping the key value for each repitiion of the group)?

 

Thanks

Pradipta

[cbarlow3]

I believe that you can create a parameter as part of the dataset and make the query of the dataset use that input parameter value to filter what is returned.  Then, right click on the List element that uses the dataset and choose "Edit list datasource".  Click on the "Parameters" tab, add a parameter (you should be able to find your new List parameter in the dropdown) and enter a "Value expression" that you want to map into that list parameter.  The Value expression can include information from the main dataset such as fields, variables, etc.

I haven't tested those steps 100% yet, since the only "List" I have in my reports so far is actually just a single field from the database that is pretty unrelated to my main query, which is why I've chosen to implement it as a list instead of a join on a single row table.  Please let me know if these steps work for you.

Carl

[pradiptab]

Hi Carl

Thanks for the help. Its working fine.

1. I have created a parameter in the Dataset of the List.

2. In the "Edit list Parameter" added that parameter of the dataset. Set the Expression with the Field from the main field list. (according to which the list needed to te filtered.) Now my parameter is ready and taking value from the main field.

3. There was a property "Filter Expression" for the dataset which need to return a boolean value. There I have added

$P{my_dataset_parameter} == $F{my_dataset_field}

 

Now it is filtering the dataset perfectly. :)

 

Thanks,

Pradipta

[pjamack]

 Is it possible to explain what you did?  I'm trying to build subdatasets based on Filtering the date ranges.
I have two crosstabs, one function call, but different parameters(date ranges) passed in.  The problem is the database is kind of off limits. So one call to grab all the data and then create two subdatasets that filter the data into what is needed for a particular crosstab.   So Main dataset calls all ranges,  Subdataset filters data from main, Subdataset2 filters even further from main.

Is this possible?

[cbarlow3]

If I understand you correctly, you're hoping that your subdatasets can access the result rows from the main query as a temporary table instead of making additional queries to the actual database.  I don't do much with crosstabs, but I can see how this could be usefu in making a List element in the Summary band that could for example summarize the subtotals (I think subtotals in a group footer are nice, but some people can't live without seeing that information summarized again all together at the end of the report) without pulling the original data again, but I don't know of a way to do it with a subdataset or with a subreport.  Maybe the answer lies in the mysterious "Connection / Datasource Expression" setting for a List or SubReport.  I always set this to "Use connection expression" with an expression of  $P{REPORT_CONNECTION}, but maybe there's a way to name the main result set and use a Datasource expression instead...but I don't know how and can't find it documented anywhere.

Sorry..that's as close as I've come to an answer.

Carl

[pjamack]

 I've been trying to figure another way where I just have One Main dataset, no subdatasets and then Filter each Crosstab..

BUt I'm stuck on being able to filter them properly.  
What I'm trying to do is the user Puts in a Date Range, lets say for a months worth of data, well CrossTab 1 will Filter that data into Todays Date plus 7 days... 
CrossTab #2 will filter based on Todays Date - 28 days or whatnot.   That way Crosstab # 1 shows the right data for upcoming weeks data and CrossTab#2 shows data for the past month.   But I'm getting errors on filtering by dates as the REPORT_START_DATE etc compared to java.util.date, sysdate, java.util.calendar.date, etc all come back with errors. Lost right now on how to filter crosstab expressions.
 

[pjamack]

 I guess the simplest way to explain is there are two crosstabs.

CrossTab #1 Filtered on  data from today till next Thursday.  

new Boolean($P{REPORT_START_DATE}.after( new Date()-7 ) && $P{REPORT_END_DATE}.before( new Date())).

 

but the-7 never works.

 

 Crosstab #2 filtered on last months worth of data.

new Boolean($P{REPORT_START_DATE}.after( new Date()-28 ) && $P{REPORT_END_DATE}.before( new Date()))

 

Input parameters would be Start_Date is say July 20 and End Date Aug 18.  Then filter in crosstab expressions from that point.

but again, just trying to filter on date range seems to be a complicated mess in Jasper.

Post Edited by pjamack at 08/18/2011 19:19

[pjamack]

 Used this..

new Boolean($P{REPORT_START_DATE}.after(org.apache.commons.lang.time.DateUtils.addDays($P{REPORT_END_DATE},-7)))

 

and other variations and it works...but report comes back as blank...  Tried with Days(), -7 etc and that works too but I'm running against a staging database and there isn't any data in it from the past few months, so new date will just return blank info.

[pjamack]

I got the Crosstab filter expression to work.  However I have two crosstabs and only one displays data. 

The crosstab filter expression used is 

new Boolean($V{theDate}.after(org.apache.commons.lang.time.DateUtils.addDays($F{REPORT_END_DATE},-7 )))

 

  If my inputs are for a months worth of data, I will get the last weeks worth of data subtracting 7 days from Report End Date.
Works like a charm.   The problem is the Second crosstab.
 It's supposed to show data for the past month broken up into Week 11, Week 12, etc.   If I don't even use a filter or if I do user a filter expression it doesn't matter as it just comes up as a blank page.  Not sure why the second crosstab isn't working or showing up.  

 

 I run the report a different way, with multiple datasets, and while there are issues with that, I do get that to display the first Crosstab and the Second Crosstab.   But it's two calls to the database and it's a no no where I'm at. So I need to just use one function call and filter out what is needed.  Any help?