Group Data by week

[Portlight]

I am trying to format a report that groups the data together by week - with each Sunday being the begining of a new week.

I have included a screen shot to explain what I am looking for.

I have looked at the java commons language and think that my be the answer, however am a little fuzzy on how to format exactly what i am looking for.

variable to group on is starttime.

[Portlight]

I believe that I could use the

getFirstDayOfWeek() any ideas on how to format this in iReports

[mdahlman]

Working with weeks and days-of-the-week are really painful.

No, there's not really a simple way to use getFirstDayOfWeek(). That's a method on a Calendar object. But you don't have a simple way to instantiate a Calendar object to work with. Also, it's easy to work with static methods... but that's not a static method.

You could write your own Java helper class with a static method called getFirstDayOfWeek. Then you could create a group like this: MyHelperClass.getFirstDayOfWeek($V{MyDateVariable}). If you're comfortable writing such a Java class and then compiling it into a .jar and putting that into your classpath, then you're all set.

In my article Date Calculations in JasperReports I include the example "Last day of this week". You can convert this easily enough to "First day of this week". It's way more painful than it ought to be. But I don't know of a better way that doesn't require building an extra .jar file as suggested in the previous paragraph.

Here's how I would calculate the first day of the week:

 

 

DateUtils.addDays ( 
	DateUtils.truncate($P{MyDate},java.util.Calendar.DATE),
	(
		"Sun".equals(new SimpleDateFormat("E",java.util.Locale.US).format($P{MyDate})) ? 0 :
		"Mon".equals(new SimpleDateFormat("E",java.util.Locale.US).format($P{MyDate})) ? -1 :
		"Tue".equals(new SimpleDateFormat("E",java.util.Locale.US).format($P{MyDate})) ? -2 :
		"Wed".equals(new SimpleDateFormat("E",java.util.Locale.US).format($P{MyDate})) ? -3 :
		"Thu".equals(new SimpleDateFormat("E",java.util.Locale.US).format($P{MyDate})) ? -4 :
		"Fri".equals(new SimpleDateFormat("E",java.util.Locale.US).format($P{MyDate})) ? -5 :
		-6
	)
)

 

 

Yeah, it's ugly.

Good luck,
Matt

Don't forget to add commons lang to the classpath and import this in the report definition:
org.apache.commons.lang.time.*

 

Post Edited by mdahlman at 03/09/2011 21:51

[Portlight]

inserting the code you have gives me many errors - I have already tried the file you have for download and it gives me the same errors....DateUtils can not be resolved.

I replaced your paramaters with my params names.

what do i need to look for to resolve these errors

 

[mdahlman]

Don't forget to add commons lang to the classpath and import this in the report definition:

org.apache.commons.lang.time.*

[Portlight]

At the risk of sounding really lost - does not importing the common.lang not add it to the classpath when compiling.

 

[mdahlman]

"does not importing the common.lang not add it to the classpath when compiling."

That sentence isn't completely clear to me.

Reports have a section where you can add imports. The sample report that's included in my article includes this. When you add, for example, "org.apache.commons.lang.time.*" iReport does not automatically find the .jar file from somewhere and add it to the classpath.

Conversely, when you add commons-lang-2.4.jar to iReport's classpath, it does not automatically add an import section to every report that you create.

There are two distinct steps.

Regards,
Matt

[Portlight]

The class issue is taken care of.

here is the situation

1. I cannot add a custom jar file - client restraint

2. I can not change the stored proc - client restraint

 

I have gotten the Gregorian calendar to set using new java.util.Date((new GregorianCalendar(2008, 0, 1)).getTime().getTime())

I need to be able to print the dates (to check that this is working) between the start and end dates passed by the stored proc to do this I need to create a loop which i can't seem to get to work.

If I can create this look then I can convert the dates to dayofweek and group the sums of calls for each week between the dates using a compare code (was able to get a comparison of dates to work but it only looks at data being passed via st proc.

 

Frankly this is a job breaker for me so I am extremely frustrated. I have bought ALL of the manuals - no help

positions is that i am not a strong java programmer more of a database person

company has gone from crystal reports to jasperserver but I am having trouble converting reports.

If I can get this week of range done without having to later the stored procs then I am golden

Please help

$V{evalDate} >= $V{start} ? "good Date" : "bad Date"

 

[Portlight]

here is what i have done so far:

start date converted to long $P{startDate}.getTime()

end date converted to long $P{endDate}.getTime()

if you could just show me how to do a loop then i could possible convert the long back to a date then find the week of

or even If you could help me with a week of year group

[mdahlman]

There is no concept of "loops" in JasperReports. Perhaps this way of thinking about the problem is causing you more trouble than it might otherwise.

If you have a Date object, then you can convert it to text like this: 

new SimpleDateFormat("dd-MMM-yyyy").format($V{EvalDate})

Search on SimpleDateFormat for all the details on how you can format it as you like.

Regards,
Matt

[pjamack]

 So without adding jar files or scriptlets, what is the easiest way to break things up into weeks, month, etc.

If I have input controls for StartDate and EndDate and pass in say July 1 to July 31 2011  into a function that returns fields in a cursor,  how would I go about translating July 1 - 7 as WeekOne, July 8-15 as WeekTwo and so on.   And within those date ranges, I also have to calculate values/counts for certain columns.   It's really not just about displaying a date range but also computing the values in fields for those date ranges.  

 

 To me it would be much easier to do the calculations and computations in Oracle, but not able to do that.  So in Jasper, what is the best way to go about converting a StartDate, EndDate into WeekOne, WeekTwo , etc.