Jump to content
Changes to the Jaspersoft community edition download ×

Help with groups...


chill_work

Recommended Posts

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

Link to comment
Share on other sites

  • 4 years later...
  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

 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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...