How to group by week?

[2002 JI Open Discussion]

By: Mauricio Hernandez - micho2001

How to group by week?

2003-08-29 08:52

Hi!

 

Does anyone out ther what expression can I use to group by week?

 

My query returns a date, and I would like to group the records using weeks!

 

Any help Apreciated!

 

 

 

 

By: G᢯r M󣺡r - moczar

RE: How to group by week?

2003-08-29 09:06

I think you have to group by week your results in your SQL query, not in jasper.

 

 

 

 

By: Mauricio Hernandez - micho2001

RE: How to group by week?

2003-08-29 09:11

Uhm... no because if I group by date i wont be able to show individual records!

 

I was able to do this on Access, but I´m moving onto Jasper cause its Open & Cool!

 

Hints anyone?

 

 

 

 

By: Ryan Johnson - delscovich

RE: How to group by week?

2003-08-29 09:24

Grouping takes work in both the SQL and Jasper - you have to *order* the records properly in the query, and then you can group them in Jasper.

 

Once you know how to order your records by week (db specific), what you have to do in Jasper will depend on the query results: If you pulled the week out of the date by itself, just use that as the groupByExpression. If not, you can use javax.text.SimpleDateFormat to pull the week out of the date in the report for use in the groupByExpression.

 

Good luck,

Ryan

 

 

 

 

By: Mauricio Hernandez - micho2001

RE: How to group by week?

2003-08-29 09:40

thanks!!!

 

It worked!

 

If anyone has SQL Server, heres the function that might help:

 

select datepart(WEEK,payment_date) as myweek from payments

 

You can then use 'myweek' in jasper to group by!

 

cheers and thanks for your help!

[dpeykov]

Hi,

 

I have the same problem ... trying to take the year, the quarter, the month and the week from the date, but I still can't do that.

I have an idea to use MySQL query to take what I want from the date, but I don't know how to combine MySQL in MDX query or in the schema or anywhere.

 

I have already posted a few times the forum but there isn't any answer yet. :(

 

Hope, some of You guys, will help me to solve that problem and go ahead.

 

Best Regards,

D.Peykov