Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Report that groups by a time period, not db field Post Reply Post New Topic
Author Message
martinbanks
Newbie
Newbie


Joined: 09 May 2012
Online Status: Offline
Posts: 18
Quote martinbanks Replybullet Topic: Report that groups by a time period, not db field
     Posted: 25 Jun 2012 at 4:40am
I need to make a report that counts how many calls were active at each second in time so that I can see the largest amount of calls we had going at any one point in time. All other details are really of little concern, all I need to determine is the highest amount of lines used and then I will adapt in things like how often etc at a later time.

To achieve this I have envisioned a report that groups by every second for say the last month but I have no idea how to do this. I cannot use one of the time fields, such as call start time because then I need to have a count formula tell me how many calls started before this time and subtract the calls that ended before this time.

I have a date and time field for both call start and call end.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 25 Jun 2012 at 10:52am
You will probably need a separate table that has an entry for each second during the day - depending on how your data is set up in your other tables this could take two forms:
 
1.  If you have time in a separate field from date in your call data, you can create a table that just has the time elements.  This will work best if all of the time in the system is formatted based on a 24-hour clock rather than AM/PM.
 
2.  If your call data has dates and times combined, you'll need a table that has an entry for every date/time combination that you want to display. 
 
3.  Rather than "joining" from this new table to the call data, you'll need to create a formula in the selection criteria that looks something like this:
{NewTable.TimeField} between {CallData.StartTime} and {CallData.EndTime}
 
-Dell
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum