Calculate maximum of summed fields 1

[aks12]

Hello, I'm using CR 10 with Oracle. I've got what is a fairly common problem I

imagine, but I can't find any posts here that address it. I want to calculate the maximum of sums and put them in group headers. My group footers are designed to be blank and suppressed.

I have data that looks like this:

HANDLE_TIME START_TIME
943 06/19/2006 11:00:00
1110 06/19/2006 11:30:00
234 06/19/2006 12:00:00
3077 06/19/2006 12:00:00
539 06/20/2006 12:30:00
655 06/21/2006 12:30:00
1893 06/21/2006 12:30:00

In my report, I have groups for:
Quarter
Month
Week
Day

In each group header I want the maximum of the sum of HANDLE_TIME at the half-hour level. Each group is set up as a drill-down.


Expected output would be:

Time period Max Handle Time

Quarter 3311
Month 3311
Week 3311
Day
6/19 3331
6/20 539
6/21 2548


Thanks for any help provided.

 

[lbass]

Create a formula {@30min} like:

if minute({table.datetime}) < 30 then
datetime(date({table.datetime}),time(hour({table.datetime}),0,0)) else
datetime(date({table.datetime}),time(hour({table.datetime}),30,0))

Insert a group #5 on this formula and then create these formulas:

//{@reset} to be placed in the (day) group header #4:
whileprintingrecords;
numbervar max;
if not inrepeatedgroupheader then
maxsum := 0;

//{@accum} to be placed in the {@30min} group header or footer:
whileprintingrecords;
numbervar maxsum;
if sum({table.handle_time},{@30min}) >= maxsum then
maxsum := sum({table.handle_time},{@30min});

//{@display} to be placed in the group footer #4:
whileprintingrecords;
numbervar maxsum;

-LB