Print Page | Close Window

Custom Time Grouping

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6627
Printed Date: 28 Apr 2024 at 2:58pm


Topic: Custom Time Grouping
Posted By: rftd
Subject: Custom Time Grouping
Date Posted: 08 Jun 2009 at 3:29pm
Hello Everyone,

When grouping data, crystal reports allows one to group date-time fields by second, minute, hour, day, week, etc.

I was wondering if there is a way to create a custom time grouping. For example, if I wanted to group a date-time field for every 15 minutes or every 20 minutes, is that possible?

Thanks in advance,

-rftd


Replies:
Posted By: DBlank
Date Posted: 08 Jun 2009 at 3:33pm
If you can create a formula that returns the value(s) as you want them then you can group on that formula field.
I am not sure what you mean by every 15 minutes though...

Posted By: rftd
Date Posted: 08 Jun 2009 at 3:49pm
Let me clarify what I need to do:

lets say I have a database where each entry has a date-time field. For instance, lets say the following entries were in the database:

6/8/09 12:01
6/8/09 12:05
6/8/09 12:11
6/8/09 12:20
6/8/09 12:35
6/8/09 12:36
6/8/09 12:40
6/8/09 12:46

Then, if I wanted to group for 'every 15 minutes' then there would be the four groups shown below:
1) 12:01, 12:05, 12:11
2) 12:20
3) 12:35, 12:36, 12:40
4) 12:46

In other words, group 1 includes any entry that is within the 15-minute time interval up to 12:15, group 2 is the 15-minute time interval from 12:15 to 12:30, and so on.

Crystal reports already lets you do this by second, minute, hour, week, etc. I was wondering if I could do this based on a custom time interval (e.g. 15 minutes).

Posted By: DBlank
Date Posted: 09 Jun 2009 at 6:47am
I assume you want this for a wider scope of data than you are using in your example.
Here is one way to do that (you may need to tweak this but the concept should be close).
if MInute({table.time})<16 then
dateadd("n",15-minute({table.time}),{table.time}) else
if Minute({table.time})<31 then
dateadd("n",30-minute({table.time}),{ACTIVITIES.STARTTIME}) else
if Minute({table.time})<46 then
dateadd("n",45-minute({table.time}),{table.time}) else
if Minute({table.time})<60 then
dateadd("n",60-minute({table.time}),{table.time})


Print Page | Close Window