Skip first record conditionally

[tim.korona]

We are converting our current reports to Jasper and I need to sometimes conditionally skip the first record returned in our result set.  A simple example would be if more than 1 record returned and a specific field is not null skip the first record.  The first record will always have a specific field not null.  I know the simple answer would be to just not return the record.  With out going into too much detail, the row needs to be returned.  In our current reporting tool this is very simple to do...if field null and count(rows) > 1 skip the record.  I have been unable to get this logic to work in Jasper and I am looking for help.

I have been attempting to use the Filter expression editor to perform this logic.  Would seem the logical place.  FIrst question is can you use a conditional expression in the Filter?  Assuming you can, checking for the null field is easy but how do I check/get the total number of rows returned BEFORE processing the report?  I have tried creating a variable using REPORT_COUNT with a reset type of Report.  I am using the following expression in the filter:

!$F{msg}.equals( null ) && $V(record_count) > new java.lang.Integer(1)?$F{msg}.equals( null ):!$F{msg}.equals( null )

In english:

IF msg is not null AND record count > 1

    only use records with msg null

ELSE

    only use records with msg not null

Any help would greatly be appreciated.

 

 

[Thomas Zimmer]

The part of null-checking is easy and correct like you have done it. 

You cannot use any count variable or similar of teh report, because at the time only the first record is treated by the report, the engine doesn´t know how many rows will be in your dataset.

I see two posiblities:

1) bad: have a main report which counts the rows in your dataset, then pass the connection/datasource and the count to a subreport which does what you want (filtering first row)

2) less bad: modify your query (join) to get the count of rows directly with the data. Maybe your database has analytic function (Oracle partition by... ?) to do that efficiently in one query.

Cheers, Thomas

http://www.thomaszimmer.net