Crystal Reports - Formula for Hour by Hour totals

[cmsgil]

I am writing a report that requires that I show hour by hour what type of carton is passing through a system. I have 3 groups, ShipStation, Date, Carton Type. How do I get the report to reflect for example:

7am to 8am - 675 package shipped
8am to 9am - 435 pacakges shipped

and so on and so on.....

Then I need to show an average per hour per day.

 

[bdreed35]

Create a formula that returns the hour of your date:

//@Hour
Hour({table.date})

Insert a group on this formula. Once you insert it, it will become the innermost group, which is what you want.
Use this formula to display in the Hour group header:

stringvar result := "";

select {@Hour}
case 0 : result := "12am to 1am"
case 1 to 11 : result := totext({@Hour},0) & "am to " & totext({@Hour}+1,0) & "am"
case 12 : result := "12pm to 1pm"
case 13 to 24 : result := totext({@Hour}-12,0) & "pm to " & totext({@Hour}-11,0) & "pm";

result := result & " - " & toText(sum({table.quantity},{@Hour}),0) & " shipped";

Suppress the detail section and you should be about done.

~Brian

 

[lbass]

Or you could group on {table.datetime} and choose "The section will be printed for each hour." Then choose "Customize group name"->"Use a formula to customize name"->x+2 and enter:

totext(time({Orders.Order Date})) +" to " + totext(time(dateadd("h",1,{Orders.Order Date})))

You might also think about whether it would be better to display non-overlapping groups, e.g., 12:00:00AM to 12:59:59AM, in which case you would change the second part of the formula to:

totext(time(dateadd("s",3599,{Orders.Order Date})))

Then you can right click on {table.qty} and insert a summary on it to get the group subtotals.

To get the average, I think you would need to use a variable. Create two formulas:

//{@accum} to be placed in the group header or footer for the hour group:
whileprintingrecords;
numbervar sumgrpqty := sumgrpqty + sum({table.qty},{table.datetime},"hourly");
numbervar counter := counter + 1;

//{@displayave} to be placed in the report footer:
whileprintingrecords;
numbervar sumgrpqty;
numbervar counter;
sumgrpqty/counter

-LB