This IS possible, but it will be a bit tedious to set up.
Assuming you're grouping by activity and that for each hour you want a count of how many of that activity are occuring.
For each hour in the day, you're going to create a formula that looks something like this (which I'll call {@Hour1} because it looks at the time between 1 am and 2 am):
NumberVar startHour := Hour({myTable.StartDateTime});
NumberVar endHour := Hour({myTable.EndDateTime});
if startHour < endHour then //all time is in the same day
if startHour <= 1 and endHour >= 1 then 1 else
else //the time spans over more than one day
if startHour <= 1 or endHour >= 1 then 1 else 0
The Hour function returns the hour as a number between 0 and 23. So, for example, 1 pm would return 13 not 1.
I haven't tested this so I'm not absolutely certain it will work, but it should give you a start.
To get the counts for each hour, sum that hour's version of this formula. This sum should work at any grouping level.
-Dell
IP Logged
