Crystal Report: Hold Time Grouped by Skillset, Agent Login

[question911]

Hello,

I am trying to develop a custom Crystal Report using two views that are linked together. I am trying to sum the "HOLDTIME" which is grouped by skillset and then by agent/csrs. The relationship between skillset and agents/csrs is many to many. That is a CSR can be in one or more skillsets and a skillset can have one or more agents. The two views are iagentperformancestat and iagentbyskillsetstat. These views are inner equal joins based on timestamp and userid fields (keys).

The results of my SQL was not what I had expected. Lets say for example, Jessica (CSR) is assigned to recieve calls from Skillset 1 and Skillset 2. Furthermore, lets say she has a total "HoldTime" of 872 seconds from both skillsets combined. This is what the standard agentperformance report reveals. In my custom Crystal Report, I was expecting to see the total "HoldTime" of 872 broken down by skillsets for Jessica (CSR). So, for example, When Jessica received calls from skillset 1 she would have 500 seconds of "HoldTime" and when Jessica recived those calls from skillset 2, she would have 372 seconds of "HoldTime". This does not show up on custom Crystal Report. The Custom Crystal Reports shows 872 seconds for each skillset Jessica is assigned to. Anyone know whats causing this problem?

SELECT
iAgentBySkillsetStat.Skillset,
iAgentBySkillsetStat.AgentLogin,
SUM (iAgentPerformanceStat.HoldTime)
FROM
blue.dbo.iAgentBySkillsetStat iAgentBySkillsetStat,
blue.dbo.iAgentPerformanceStat iAgentPerformanceStat
WHERE
iAgentPerformanceStat.Timestamp = iAgentBySkillsetStat.Timestamp AND
iAgentPerformanceStat.UserID = iAgentBySkillsetStat.UserID AND
iAgentBySkillsetStat.Timestamp BETWEEN {ts '2004-04-12 00:00:00.000'} AND {ts '2004-04-12 23:45:00.000'}
GROUP BY
iAgentBySkillsetStat.Skillset, iAgentBySkillsetStat.AgentLogin

ORDER BY
iAgentBySkillsetStat.Skillset ASC,
iAgentBySkillsetStat.AgentLogin ASC

 

[ErwinStevanus]

Some info from the NTP - Historical Reporting & Data Dictionary

1. When you link views to generate a custom report, use the Left Outer [=(+), *=] join type. (page 78)

2. Linking views (page 89)
I think the example here is similar to what you did except that the sample uses AgentPerformanceStat and AgentByApplicationStat views.

 

[question911]

Hi Erwin,

Left outer join is not the issue. I just looked at data sets on both VIEWS. This is a design limitation in Symposium 3.0. I am not sure if this will be an issue in 4.0.

Thanks

 

[ErwinStevanus]

Question911,

This is a design limitation in Symposium 3.0. I am not sure if this will be an issue in 4.0

-> As far as I know, Symposium 3.0 is already End Of Life. The Current Symposium is SCCS 4.2 (SCCS 5.0 is coming as well)

Anyway, I think this is still an issue in SCCS 4.2 (Let's hope that SCCS 5.0 address this issue)

 

[mbasimon]

It's because HoldTime belongs to the iAgentPerformanceStat table, and not the iAgentBySkillsetStat table. If it was a skillset field, you could sum it up the way you are thinking. However, a field on the iAgentPerformanceStat is a total for each total, it can't be broken down by skillset.

 

[question911]

As I said earlier... design limitation with 3.0

Also, this is still a problem in 4.2, even though Nortel's copy of "Transitioning from Meridian MAX to Symposium Call Center Server" white paper on page 18 shows "HOLDTIME" added to the I(d)AgentBySkillsetstat view. This is not the case in the 4.2 data dictionary. See for yourself.