Ignoring records based on condition

[2006 IR Open Dicussion]

By: Shireesh Anjal - anjalshireesh

Ignoring records based on condition

2006-03-30 00:02

Hi,

 

We have a requirement wherein the report engine should be able to ignore certain records based on certain criteria. (This criteria can be set as an expression that returns a Boolean value - true or false).

 

If the value of the given expression is true, then that particular record (row from the data source) should be ignored and next row should be fetched and evaluated. e.g. Ignore all rows where value of the field partTranType is "C". (This is just a simple example, there can be more complex conditions).

 

The expression can contain variables, fields, parameters and constant values.

 

Please let me know how this can be achieved with JasperReports.

 

Thanks and regards,

Shireesh Anjal

 

 

 

 

By: Denny Valliant - xenden

RE: Ignoring records based on condition

2006-03-30 16:06

I think what you're looking for is how to use a "printWhenExpression", which does just what you're asking. Look it up, I think you'll like it.

XD

 

-ps less intensive and easier to maintain is just pruning the records prior to sending them to JR.

 

 

 

 

By: Shireesh Anjal - anjalshireesh

RE: Ignoring records based on condition

2006-04-03 10:36

Hi,

 

Setting printWhenExpression will ensure that the record will not be printed on the report. However detail evaluation will still happen for these records, and hence the evaluation of all variables will also happen.

 

My requirement is that whenever the predefined condition is true, the current row should be ignored and none of the variables should be evaluated for this row. The next row should be fetched immediately and the detail evaluation should happen on this row (if the condition is not true for this row).

 

Let me give a small example to explain this.

Let us say the data coming from the data source is as follows:

================

emp_no salary

1001 20000

1002 30000

1003 40000

1004 50000

1005 10000

1006 23000

================

In the report, we need to print all employees whose salary is greater than 25000, and also the sum of salaries of such employess. i.e. the report should look like:

 

========================================

Employees having salary more than 25000:

 

emp_no salary

================

1002 30000

1003 40000

1004 50000

================

Total 120000

========================================

 

For calculating the total of salaries, we need to define a variable (sum_sal) of type sum and expression as $F{salary}

 

If we use printWhenExpression in the detail band to print only those rows where salary is more than 25000, the number of records will be correct, however the variable sum_sal will still contain the total of all the records, which will be 170000.

 

I know that we can still manage by putting a ternary operator in the variable expression - something like:

$F{salary} > 25000 ? $F{salary} : new BigDecimal(0)

 

However this will become really tedious and user unfriendly if there are too many variables in a report. Hence we need a mechanism wherein the user can specify a condition, which being true, those records coming from data source will be ignored - not just while printing the detail band, but also for variable evaluation.

 

It is true that such situations can be best handled by not sending such data from the data source, however it may not always be possible. e.g. Our data source is a custom data source that provides the data from a flat file. And since it (the custom data source) can not access variables/parameters from the report, there is no way it can filter data based on a user specified condition.

 

Probably we need some tag in the JRXML file where user can specify a condition for ignoring records. When this condition evaluates true, the present record should be discarded and the next one should be fetched before any variable evaluation happens. Or at least some API function that I can invoke from my scriptlet (inside beforeDetailEval) so as to move to the next record without evaluating any variables.

 

Thanks and regards,

Shireesh Anjal

 

 

 

 

By: tadzius - tadzius

RE: Ignoring records based on condition

2006-04-04 04:41

maybe try changing the evaluation time of the $V{sum_sal}...

 

 

 

 

By: C-Box - c-box

RE: Ignoring records based on condition

2006-04-04 07:06

What about modifying your query:

 

Select * from salarytable where salary > 25000

 

!?!?!?!? :-)

 

I think that hiding records from printout AND calculation should be done via the WHERE CLAUSE within the query or you should pre-filter your customdatasource if you want just some data within your report...

 

The more condition you make the longer it takes for report creation, isn't it? The database is the fastest way of filtering the data... everything that comes later takes (useless) time.

 

If you put a parameter "$P{SalaryForUserInput}" inside your report and make it "is for prompting" you even don't have to change your design and are very flexible additionally!?!?!? :-)

 

Think about it!

 

hth

C-Box

 

PS: If you don't want to, just create your own "skipable" CustomDatasource where you implement your own next-method that skips records based at your condition! I did something similiar with a "FilterableDTODataSource" to make parent-child relationship possible for Master-SubReport constructs with multiple datasources!

 

 

 

 

By: Shireesh Anjal - anjalshireesh

RE: Ignoring records based on condition

2006-04-05 06:43

Hi C-Box,

 

Thanks for a detailed response.

 

I agree that such filtering can be easily done in the WHERE clause of the database query.

 

However as I have mentioned in my previous post, our reports do not receive data from database queries.

Instead we have a custom data source that reads the data from a pipe separated flat data file.

I do not have control over the generation of this data file.

The same data file may be used for different reports, each of them having different conditions for ignoring certain records.

 

It will be great if I can do it in the "skipable" data source as you have mentioned,

however I do not see any way to access the report parameters/variables inside a custom data source.

I can not hard code the condition inside the data source as the end user (who designs the report) will provide the condition.

 

Also, the condition may not always be as simple as "salary > 25000".

The ignore condition can be an expression containing fields, variables and parameters.

The whole expression needs to be evaluated to decide whether to ignore the record or not.

 

I would also like to mention here that we are trying to upgrade the existing reporting infrastructure in our product to a new

one using JasperReports. Since the existing infrastructure provides the capability of ignoring records based on a user specified condition,

we are looking for a similar feature in JasperReports. We feel this is a generic feature that any reporting framework should provide.

 

Just to summarise,

1. I can not use WHERE clause because the data is not coming from database, but from flat file.

2. The filtering can not be done in the data source as report parameters/variables are not accessible inside a custom data source.

 

I will really appreciate any help on these points.

 

Thanks and regards,

Shireesh Anjal

 

 

 

 

By: C-Box - c-box

RE: Ignoring records based on condition

2006-04-05 07:13

Who says that you can't access fields/variables/parameters ?!?!

 

In my "filterable DataSource" I do have two extra conditions:

1.) filterField

2.) filterValue

As one filter is enough for my purpose I just have two fields (it could also be more flexible with a whole map)

 

So my datasource expression looks something like this:

 

$P{MyDataSourceContainer}.getClonedAndFilteredSubDataSource("SubDataSourceNameForSubReport","FieldNameInSubReportForFilter",$F{FieldFromMasterReportToFilterValue})

 

So as you can see, I do put a DataSourceContainer as parameter to the MasterReport... this Container holds many datasources (for each different SubReport an own datasource) ... with my own Method getClonedAndFilteredSubDataSource(String name, String filterFieldName,Object filterValue) I do clone a datasource (important for reuse in other nested subreports) and pass the filterfieldname and the value that must fullfill the filter....

 

this works great... and I could imagine you could reach your goal with quite the same way... put your datasource is "skipable" where you must pass your skipconditions to the datasource.

 

just try (as I did) :-)

C-Box