Selecting / Grouping records that are concurrent to each other. 1

[Drummachine24]

Ok all! I have a need to create a report from our Call Manager database that shows all VoIP calls that happen concurrently, and show a count by day by hour of when we reach are concurrent call maximun.

IN our call manager, we're restricted to 8 concurrent calls, meaning 8 people can be on the phone at the same time. Once that limit is reached the nine person that calls in gets a busy signal, or if they try to make a call, they get a fast busy. What I'm trying to do is build a report that will show me when we're reaching our max concurrent calls on a per day basis so I can trend the times of the day that we hit that limit.

In the Call Manager database, its hold the CDR (Call Detail Records) for all the calls, so getting when the call initiated, when the call ended, duration, etc is easily accessible, I'm just not sure how I'd build the report to count all the calls that are happening in the same period of time of each other and then group it by the hour.

Is there any Crystal whizzes out there that could help me out?

Thanks in advance!

Josh

 

[lbass]

Create a formula {@allhours} and insert a group on it:

Whilereadingrecords;
TimeVar start := time(0,0,0);
NumberVar timeDiff := datediff("n",datetime(currentdate,time(0,0,0)),datetime(currentdate,time(23,59,59)));
NumberVar i := i + 60;
if i <= timeDiff then
start := time(DateAdd("n", i, datetime(currentdate,start)))
;
start;

For this to work, you MUST add (any) table to the main report that has at least 24 records. Add a field from this table to the report header and suppress the field.

Then insert a subreport that includes a count of the callID and place it in the group header or footer. Link the sub to the main report by moving {@allhours} to the right for the main report linking field. You will see {?PM-@allhours} in the lower left corner. Do not check "Select data in subreport based on field".

In the subreport, create a parameter {?date} for the date you will be running the report and then go to report->selection formula->record and enter:

(
(
{table.starttime} <={?Pm-@allhours} and
{table.endtime} >= {?Pm-@allhours}
) or
(
time(hour({table.starttime}),0,0) = {?Pm-@allhours} or
time(hour({table.endtime}),0,0) = {?Pm-@allhours}
)
) and
{table.date} = {?date}

This formula will check for all activity at the hour point and also include any calls that start or end within the hour.

Suppress all sections within the subreport except the footer where you are showing the count of the call.

-LB

 

[Drummachine24]

Wow! Thanks for the fast response! I'm going to try to figure out what you mean there, since I'm just a novice at Crystal reports.

I've found another caveat that is slowing me down before even getting that far though - It appears that all the date and time fields in the database are stored as 'universal time and in seconds since January 1st, 1970', so somehow I need the make the field translate to what the date and time actually is, and then figure out how to use the above! Any suggestions?

Also, might you be interested in a webex session to possibly walk me through it live?

Just figured I'd ask - I know they want this report sonner than later and that would surely help A LOT!

Thanks again!

Josh

 

[lbass]

Try converting the values to datetimes, by using:

dateadd("s",{yourfield},date(1970,1,1))

No, we have to stay in the forum. Sorry.

-LB

 

[Drummachine24]

LB,

Thanks! I understand about the forum, but doesn't hurt to ask.

Ok, I acutally just got the date time thing figured out. I created two formula fields with the following formulas:
DateAdd("s", {CallDetailRecord.dateTimeConnect}-14400, #1/1/1970 00:00:00#)

and DateAdd("s", {CallDetailRecord.dateTimeDisconnect}-14400, #1/1/1970 00:00:00#).

That took care of the date and time issue.

I also created the @allhours formula field, but not quite sure what is meant by "insert a group to it".

And the last thing would be how to modify the subreport to use the parameter field to select a range of days, but still have it broken down per day.

For example, the request for this report is they want to see the peak hours during the day, each day for the last 120 days.

thaks A LOT for your help, as this is definitely above my Crystal knowledge.

Thanks!

Josh

 

[lbass]

That changes things. I think you should take an entirely different approach. Create two date parameters, and add a selection formula:

{table.date} >= {?StartDate} and
{table.date} <= {?EndDate}

Insert a group on date, and then add one formula per hour like this:

//{@3:00pm to 3:59pm}
if {table.starttime} < time(16,0,0) and
{table.endtime} >= time(15,0,0) then 1

//{@4:00pm to 4:59pm}:
if {table.starttime} < time(17,0,0) and
{table.endtime} >= time(16,0,0) then 1

Place these in the detail section and then insert sums on these at the date group level. Suppress the detail section and group header -and drag the groupname into the group. footer. You would then have a report that looked like this:
3:00-3:59 4:00-4:59 //etc.
9/22/2010 15 12
9/23/2010 22 10
9/24/2010 14 9

//etc.

-LB

 

[Drummachine24]

Ahhh, I see where you're coming from there - My only question at this point is then - Is this just showing me how many calls were made or received or ended in the hour displayed? If so, it's not quite what I think my requestor is looking for. I think what they want is to see how many calls were online at the same time. So, though there may have been 15 calls in on hour span, maybe only 3 of those calls within that hour were happening at the same time at some point during that hour. I know it seems a bit ridiculous, but... anything for the customer, right

Example:

On 9/22/2010 during 12:00 to 12:59 there were 8 calls-

Call 1 was at 12:01 to 12:15 - Concurrent
Call 2 was at 12:10 to 12:19 - Concurrent
Call 3 was at 12:22 to 12:26 - Not concurrent
Call 4 was at 12:35 to 12:45 - Concurrent
Call 5 was at 12:36 to 12:44 - Concurrent
Call 6 was at 12:41 to 12:52 - Concurrent
Call 7 was at 12:53 to 12:57 - Not concurrent
Call 8 was at 12:58 to 12:59 - Not concurrent

So, looking at the data, while there were 8 calls total within that hour, there were 2 sets of concurrent calls - Call 1 and 2 then call 4, 5, and 6.

So maybe I'm looking at this the wrong way. Is there a way to maybe have Crystal read the records and any calls that happen within the same timeframe, be dynamically grouped?

What are your ideas?

Thanks!,

Josh

 

[lbass]

I get what you are saying and I started out that way, but it means you really have to test almost every minute, since calls will be missed if they fall between the chosen intervals. Also, if you look at your example, what you are calling concurrent is if there is any overlap, but if you tested a specific point in time, there might not be. Look at Call #6. If you tested for concurrency at 12:52, the call would not be deemed concurrent, as no other calls are occurring at that time.

If you just mean overlap by concurrency, then you could insert running totals that count concurrency by counting call#, evaluate using a formula:

(
(
hour({table.starttime}) = hour(previous({table.endtime})) and
{table.starttime} <= previous({table.endtime})
) or
(
hour({table.starttime}) = hour(next({table.endtime})) and
{table.endtime} >= next({table.starttime})
)
) and
{table.endtime} >= time(12,0,0) and
{table.starttime} < time(1,0,0)

Reset on change of date. Place one rt per hour in the date group footer.

-LB

 

[Drummachine24]

Yea, I see what your saying there - Whew, I didn't think it would be this difficult - And really, maybe it isn't, but not knowing a lot about Crystal and /or programming in general, it just looks tough.

I'll see what I can do with putting all this together next week. I really appreciate your time, and if I have anything that I get stuck on, is it ok to ask for your help again? I'm sure I'll need it.

thanks,

Josh