need help with summary page

[manshack_one]

I need to compare 2 date fields in my results and count the ones that are more than 1 year apart.  Also, I need to display these totals for the locations those dates correspond to.  So on the summary page I need to see something like this:

{location}  { inrange}  {outsiderange}  {total}  {percent outside range}
     ABC            10                   5                    15                   33%

 

Then once I have that I need to order it by the highest percentage outside the range to the lowest.  This will help me identify the locations that missed the timeframe the most.

The existing report I have does not use any grouping.  It's just a printout of the raw data.  There are date range parameters tied to it so I can pull the data entered during a certain period (quarterly, yearly, etc).  Thanks in advance for your help!

[Giulio Toffoli]

My suggestion is to collect your data using a Scriptlet and display it using a List or a subreport in the summary.

A Scriptlet is a java class that can be used with a report to perform special elaborations, look at the JasperReports and iReport guides for more information about how to use it.

 

Giulio

[manshack_one]

Thanks for your suggestion.  I'll look into using a scriptlet.
However it just seems that since I already have the raw data
I need on the report that I can create some variables to get the data
I need. 

So in the scriptlet I'd be passing in all the raw data (or at least the fields i'm interested in)
and creating an array or something to do my counts on?  I thought I'd be able to just
use some conditional expressions to gather the data I need.  Basically the summary
just needs to group by the location, count the records and compare the dates on each record
for that group.  Then as a group summary show the total count and number outside the acceptable range.

In general I feel like it's one of the areas that Jasper really loses me concerning summary
information.  If you've got all the data there in the report why can't we just use variables?
So far almost every report I've made has required a subreport in the summary because I couldn't figure
out how to use the variables.  Then again I've only been working with Jasper a short while so there are
many things I don't know about yet.

[manshack_one]

What I did so far was to create a new report (that will be used as a subreport).  In it I pulled all the records entered in a certain date range.  I ordered it by the location then added a grouping to the report.  In the group footer I added the location name field, the group count variable and a new count variable.  The count variable is what I'm trying to use to compare the dates for each record in that group.  I'm thinking that just comparing the year is the most simple way to tell if it was completed in the past year. 

What I need help on though is the syntax for comparing the years.  Should I use date1.getYear greater than date2.getYear?  or just check that they're equal?

A few other questions about this, Am I right to be putting a boolean expression in the integer class variable for counting the rows?  Also, does that variable need to be in the group or in the detail section?

[Giulio Toffoli]

If you would just have to count the dates out of the range, it would be easy. But since you need to group that data by location , and print in the summary an ordered list of locations, well... you need a new dataset, this is what you would create using the scriptlet.

With the scriptlet you can collect the data (maybe in the form of JavaBeans with the properties Location and InRange) during your elaboration. This is the great advantage, you are performing a fast elaboration while generating the report without perform slow operations like running a subreport or extra queries.

Anyway your way can be good enough if I understood what you are trying to do.

To check if 2 dates differ for more than an year:   $P{date2}.getTime() - $P{date1}.getTime() < 1000*60*60*24*365

To check if 2 dates have different year:

(new SimpleDateFormat("yyyy")).format(  $P{date1} ).equals(    (new SimpleDateFormat("yyyy")).format(  $P{date2} )    )

 

Giulio

 

[manshack_one]

I ended up just writing the sql query for this one to get the data right.  Should be a little more efficient this way since the server is compiling all the data and jasper is just filling the report.

select tblUnit as unit, tblDateEntered as entered,
   tblMostRecentSBPDate as BP,
   SUM(IF(DATEDIFF(tblDateEntered,tblMostRecentSBPDate)>365,1,0)) as 'Outside Range',
   count(*) as Total,
   (count(*)-(SUM(IF(DATEDIFF(tblDateEntered,tblMostRecentSBPDate)>365,1,0)))) / (count(*)) as 'Comp Ratio'
from CAD
 where tbldateentered between $P{begindate} and $P{enddate}
 And $X{IN, tblUnit, unitselect}
group by unit
order by 6 asc;

I ended up with exactly the right data I was looking for and all the calculations are done in the query.  The only thing in iReport I had to do was format the ratio as a percentage rather than decimal.