Grouping in 5 mins time interval

[avink]

Hi,

I'm new to Crystal Reports and would seek help on the following.
I have the following report,

ID | Start-Date/Time | End-Date/Time | Activity Performer
123 | 12-03-2012 21:00:00 | 12-03-2012 21:02:34 | James
345 | 12-03-2012 21:00:00 | 12-03-2012 21:05:35 | Tony
435 | 12-03-2012 22:05:00 | 12-03-2012 22:20:34 | James
123 | 12-03-2012 21:00:00 | 12-03-2012 21:02:34 | Rachel
435 | 12-03-2012 22:12:00 | 12-03-2012 22:32:45 | Sam

....and so on

I would like to group by the End-Date/Time column in 5 min interval starting from interval 12:00 - 12:05 and group by activity performer name. Also show the count of items falling under each interval. Expected result would come like this,

<new_group_column> | count | activity performer
12:00 - 12:05 | 0
12:00 - 12:10 | 0
12:10 - 12:15 | 0
. . . . .
. . . . .
. . . . .
21:05 - 21:10 | 1 | James
21:05 - 21:10 | 1 | Rachel
. . . . .
. . . . .
22:15 - 22:20 | 1
. . . . .
. . . . .
22:30 - 22:35 | 1



How can I achieve this in CRXI.

Thanks !
A

 

[Madawc]

DatePart commands would allow you to separately extract the day, the hour and the minutes in separate formula fields.

To turn the minutes into 5-minute intervale, divide by 12 and truncate.

Stick the date, hour and 5-minute interval together in a single field and group by that.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[lbass]

Add any table to your report that has at least 288 records, and add a field to the report header and suppress it. Then create a formula like this:

//{@timegrp}:
whilereadingrecords;
datetimevar st := datetime({?StartDate},time(0,0,0));
numbervar i := i + 1;
datetimevar array dt;
timevar tgrp;
redim preserve dt[288]; //12 5-min intervals per hour x 24
if i <= 288 then(
dt := dateadd("n",(i-1)*5,st);
tgrp := time(dt);
);

Insert a group on this formula and suppress the detail section and group footer.

Create another formula {@mainmin}:

minute({@timegrp})

Then insert a subreport in a group header_b section that is linked to the main report :

{@submin}={?pm-@mainmin}

...where {@min} is this formula created in the sub:

//{@submin}:
int(minute({table.enddatetime})/5)*5

You can group on the performer in the subreport and add the summaries you want per group in the sub. You can't group on anything else in the main report which is essentially just a container for the sub.

-LB