Apples and Oranges on one report?

[Sandman83]

Hi Everyone,

I have a report that currently reports against call data like so...

Offered Handled Avg Length
10,000 9,845 00:01:58

I now need to add agent data to the report like so...

Offered Handled Avg Length Avail %
10,000 9,845 00:01:58 5.6%

Avail % is the time an agent sits waiting for a call. The call information is grouped several different times in the report. By date, application, and site. All the call information is in a call detail table, with a record for each call. The agent data is in an agent table that sums up the entire day into one record for each agent. The only common fields between them are site, date, and agent extension. (None of the fields are named the same though, and the agent extension field in the call detail record is not always an extension.) I tried adding the agent table to the infoview used in the report and linking the date and site fields, but it counts each call record hundreds one times. I tried linking the call detail table to the agent table with left joins and the report doesn't run at all. I know this report is possible in Access, and can't believe it is not possible in Crystal 8.0.1.

I have thought about creating a second sub-report, but don't think it will work with the grouping. Any ideas would be greatly appreciated.

Thanks, Tim.

 

[question911]

Tim,

What ACD software package (Nortel, Avaya, Siemens, ....) is your company using?

Also is the data represented via views or are you able to read the data directly from the tables?

 

[Sandman83]

Thanks for the reply.

We are using Aspect ACD. The reports are completely custom. I can create views or read the data directly from the tables.

 

[question911]

Tim,

I'm assuming that an agent and application relationship is 1:1. Also, the business is NOT requiring Avail% to be grouped by agent. Last of all, I am assuming the report you are creating is a Daily Summary report, not a Interval Daily Summary report. For example, on May 26, the business doesn't need to see the stats broken down by hour on May 26, just a summary of stats for May 26.

In the example below make sure you have the agent and call detail tables linked together with inner join based on common fields. Do NOT use left or right outer joins.

Try using the MAXIMUM function on the Avail % field. So for example, create a formula like this:

MAXIMUM ({agent.availpercent})

Whats happening is that the Aspect ACD seems be have a process that automaticlly summarzies stats on the agent table for each agent on a daily basis, as oppose to a interval basis like every 15 minutes on the call detail table. Again, you mentioned this before. By doing a left outer join or inner join in this situation causes the records in the call detail table to be counted hundreds of times.

By adding the Maximum function into a formula based on the agent table for the avail% field, it will only count the records in the agent table once.

Let me know if this works for you.

 

[Sandman83]

Thaks for the reply, I'll give it a try, but am not sure if it will work because the agent and the call/application reocrds are NOT a 1:1 relationship. Generally, but not always, there will be over 100 call/application records for every one agent record. Also, to get the Avail %, I use a formula.

agent.avail_time % agent.signon_time.

 

[question911]

Tim,

Make sure your formula looks like this for your DATE Group Section. Also, follow the same convention if you have other Group Sections.

If MAXIMUM({agent.signon_time}, {agent.date}) <= 0 Then
0
Else
MAXIMUM({agent.avail_time}, {agent.date}) % MAXIMUM({agent.signon_time}, {agent.date})

 

[Sandman83]

Well, I've tried using the formula and still get the same problem. I did some more testing though, and I don't think the formula is the issue. I ran the report in it original format (without the agent table) and it runs fine. If I add the agent table to the infoview that the report uses and don't change or add anything to the report itself, I still get the issue with records being counted more than once. So I am pretty sure that the problem is with the infoview. I have tried left joins, right joins, and equal joins and none of that seems to make a difference.