Help with groups...

[chill_work]

Hello,

 

I have a report that has multiple groups(bands). Each group represents a range in time, i.e. week, month, quarter, YTD. How do I adhere that specific date range to the group. I tried adding it to the Group expression but I am not sure what I need to put. Does it need to be an if condition that returns true or false? Any help on this would be appreciated.

 

Thanks,

Calvin

[pjamack]

 Has anybody figured out how to set up this without writing a scriptlet or java code?  

  For a report I'm working on, somebody enters StartDate, EndDate and lets say that's from July 1 to Aug 1 2011. 

Is there an easy way in Jasper to break this down into Weekly Ranges?  AKA July 1 - July 7 is WEEK ONE and so on.
ANd along with that, having the ability to show the data and rows within those date ranges and then total them at the end?

[cbarlow3]

I didn't have a lot of luck creating a JasperSoft group with the evaluation expression being some fancy Groovy expression, but I did find that I was able to accomplish this pretty easily using my SQL query itself:

SELECT
  ACCOUNT.ACCOUNT_NUMBER AS ACCOUNT_NUMBER,
  ACCOUNT.OPEN_DATE AS OPEN_DATE,
  CAST ((days( ACCOUNT.OPEN_DATE ) - days( COALESCE($P{StartDate}, '1800-01-01') )) / 7 AS INT) AS WEEKSSINCESTART
FROM
  CORE.ACCOUNT AS ACCOUNT
WHERE
  ACCOUNT.OPEN_DATE>=COALESCE($P{StartDate}, '1800-01-01') AND
  ACCOUNT.OPEN_DATE<=COALESCE($P{EndDate}, '2999-12-31')
ORDER BY
  ACCOUNT.OPEN_DATE

This finds all accounts that have an OPEN_DATE within the date range $P{StartDate} to $P{EndDate}, orders them all by the OPEN_DATE field, and builds a pseudo-field that is an integer number of weeks since the StartDate.  0 to 6 days from StartDate?  Then you are assigned a WEEKSSINCESTART value of 0.  7 to 13 days from StartDate?  You get a value of 1.  You can then use this field as your group value and proceed as usual with groups.

You say you'd rather have the startdate and enddate for each group (say, the start of each week as the value that controls the group and the end of that week as just a value to be displayed in the group header)?  It would look messy, but I don't see why you couldn't instead of WEEKSSINCESTART create two date pseudo-fields called STARTWEEK and ENDWEEK that are $P{StartDate} + 7 * the number of weeks calculated earlier "Days" ( + 6 Days for ENDWEEK).

One problem you might run into with this solution is that it will skip ranges that don't have any rows returned from your database.  So if you're expecting to see a JasperSoft group for every week in the date range, regardless of whether or not there is any data that qualifies for that group, you'll be disappointed.

Hope that helps!

Carl