CUIC - Agent Historical Summary of Logged On Time

Document created by cdnadmin on Jan 24, 2014
Version 1Show Document
  • View in full screen mode
This document was generated from CDN thread

Created by: Theodore Vaux on 20-04-2012 02:14:05 PM
Hi All
Got a question around the Agent Historical All Fields CUIC report - and happens in Webview migrated reports too.
When you report on an agent it lists times for all skills.  This is fine for Active Time, Reserved, Wrap, Busy Other in the summaries.
However for Log On Duration, Not Active Time, Not Ready Time etc it lists the same amount of time for each skill but in the summary it sums them all togther.
So for example if an agent was logged on for 7 hours and was a member of 3 skills it would show 7 hours logon to each skill which it then summarizes into 21 hours at the bottom when really the agent has only been logged on for 7 hours.
In Webview this worked fine and only showed the actual amount of logged on time, not the time for each skill all added together.

So the question is, how can you get CUIC to behave like webview in the summary for LoggedOnDuration and only show 1, not all 3. And also have it show the correct total report summary??  Does it need the sql changing or can it be done in the summaries?
Attached a picture of what I'm trying to explain, let me know if need more description.
Greatful of some pointers this has been doing my head in all Friday afternoon lol!

Many Thanks

Subject: RE: CUIC - Agent Historical Summary of Logged On Time
Replied by: Senthil Kumar Natarajan on 20-04-2012 02:59:39 PM
Hi All
Got a question around the Agent Historical All Fields CUIC report - and happens in Webview migrated reports too.
When you report on an agent it lists times for all skills.  This is fine for Active Time, Reserved, Wrap, Busy Other in the summaries.
However for Log On Duration, Not Active Time, Not Ready Time etc it lists the same amount of time for each skill but in the summary it sums them all togther.
So for example if an agent was logged on for 7 hours and was a member of 3 skills it would show 7 hours logon to each skill which it then summarizes into 21 hours at the bottom when really the agent has only been logged on for 7 hours.
In Webview this worked fine and only showed the actual amount of logged on time, not the time for each skill all added together.

So the question is, how can you get CUIC to behave like webview in the summary for LoggedOnDuration and only show 1, not all 3. And also have it show the correct total report summary??  Does it need the sql changing or can it be done in the summaries?
Attached a picture of what I'm trying to explain, let me know if need more description.
Greatful of some pointers this has been doing my head in all Friday afternoon lol!

Many Thanks

 
 
You can modify the footer properties of the definiton to get you an 'average' instead of 'sum'. Please check previous post on modification of the report definitions. Please note, you need premium version of CUIC to edit the definitions.

Subject: RE: CUIC - Agent Historical Summary of Logged On Time
Replied by: Theodore Vaux on 21-04-2012 05:44:18 AM
The problem is if you set it to average it will also do average for the whole report.
It needs to show the average for the agent (or the actual logged on time), then sum all the agents together for the report summary.
You can change the footers for each of the groupings, but I can't see a way to do it for the report summary so it sums instead of avearges.

Subject: RE: CUIC - Agent Historical Summary of Logged On Time
Replied by: Theodore Vaux on 21-04-2012 12:42:59 PM
Well you can see it with the normal CUIC Agent Historical All Fields report, compared to say the Webview Agent Performance Summary Daily or Half Hour report.  I tried it by adding Media grouping back in as per the Webview report but it did the same thing.  Webview example attached.
But the sql for the actual (custom) report is:
 
SELECT 
PN.LastName,
PN.FirstName,
AgentName = PN.LastName + ',' + PN.FirstName,
Media = MRD.EnterpriseName,
SG.EnterpriseName + ' (' + Convert(char(6), SG.SkillTargetID) +')' EnterpriseName,
DateCol = Convert(DateTime,(Convert(Varchar(20), ASGH.DateTime, 112))),
CallsAnswered = SUM(ISNULL(CallsAnsweredToHalf, 0)),
AnswerWaitTime = Sum(ISNULL(ASGH.AnswerWaitTimeToHalf, 0)),
asa = ISNULL(SUM(ISNULL(ASGH.AnswerWaitTimeToHalf, 0)) * 1.0 / SUM(ISNULL(CallsAnsweredToHalf, 0)), 0),
RingTime = (select Ringo = sum(ISNULL(TCD.RingTime,0))  from Termination_Call_Detail TCD WHERE TCD.DateTime >= :start_date
       and TCD.DateTime <= :end_date
       and TCD.AgentSkillTargetID = ASGH.SkillTargetID
       and  TCD.SkillGroupSkillTargetID =  SG.SkillTargetID
       and CallDisposition = 13),
asaor = ISNULL((select Ringon = sum(ISNULL(TCD.RingTime,0))  from Termination_Call_Detail TCD WHERE TCD.DateTime >= :start_date
       and TCD.DateTime <= :end_date
       and TCD.AgentSkillTargetID = ASGH.SkillTargetID
       and  TCD.SkillGroupSkillTargetID =  SG.SkillTargetID
       and CallDisposition = 13) / SUM(ISNULL(CallsAnsweredToHalf, 0)), 0),
CallsHandled = SUM(ISNULL(ASGH.CallsHandledToHalf, 0)),
Handletime =SUM(ISNULL(ASGH.HandledCallsTimeToHalf, 0)),
AHT = (CASE SUM(ISNULL(CallsAnsweredToHalf, 0)) WHEN 0 THEN 0
                    ELSE ISNULL(SUM(ISNULL(ASGH.HandledCallsTimeToHalf, 0)) * 1.0 / SUM(ISNULL(ASGH.CallsHandledToHalf, 0)), 0) END),
IncomingHoldTime = SUM(ISNULL(ASGH.IncomingCallsOnHoldTimeToHalf, 0)),
IncomingHoldCalls = SUM(ISNULL(ASGH.IncomingCallsOnHoldToHalf, 0)),
avgholdtime = ISNULL(SUM(ISNULL(ASGH.IncomingCallsOnHoldTimeToHalf, 0)) * 1.0 / SUM(ISNULL(ASGH.IncomingCallsOnHoldToHalf, 0)), 0),
LoggedOnTime = SUM(ISNULL(ASGH.LoggedOnTimeToHalf, 0)),
TotalLogOntime = (SELECT SUM(ISNULL(AHH.LoggedOnTimeToHalf, 0)) FROM Agent_Half_Hour AHH            
             WHERE AHH.DateTime >= :start_date and AHH.DateTime <= :end_date and AHH.SkillTargetID = AG.SkillTargetID and AHH.MRDomainID = MRD.MRDomainID),
AllAgentsLogOntime = (SELECT SUM(ISNULL(AHH.LoggedOnTimeToHalf, 0)) FROM Agent_Half_Hour AHH
       WHERE AHH.DateTime >= :start_date and AHH.DateTime <= :end_date and AHH.SkillTargetID in (:peragt_list) ),
ActiveTime = SUM(ISNULL(ASGH.TalkInTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkOutTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkOtherTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkAutoOutTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkPreviewTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkReserveTimeToHalf, 0)),
per_active = ISNULL((SUM(ISNULL(ASGH.TalkInTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkOutTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkOtherTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkAutoOutTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkPreviewTimeToHalf, 0)) +
      SUM(ISNULL(ASGH.TalkReserveTimeToHalf, 0))) * 1.0 / SUM(ISNULL(ASGH.LoggedOnTimeToHalf, 0)), 0),
AvailTime = SUM(ISNULL(ASGH.AvailTimeToHalf, 0)),
TotalAvailtime = (SELECT SUM(ISNULL(AHH.AvailTimeToHalf, 0)) FROM Agent_Half_Hour AHH
       WHERE AHH.DateTime >= :start_date and AHH.DateTime <= :end_date and AHH.SkillTargetID = AG.SkillTargetID and AHH.MRDomainID = MRD.MRDomainID),
AllAgentsAvailtime = (SELECT SUM(ISNULL(AHH.AvailTimeToHalf, 0)) FROM Agent_Half_Hour AHH
       WHERE AHH.DateTime >= :start_date and AHH.DateTime <= :end_date and AHH.SkillTargetID in (:peragt_list) ),
per_avail = ISNULL(SUM(ISNULL(ASGH.AvailTimeToHalf, 0)) * 1.0 / SUM(ISNULL(ASGH.LoggedOnTimeToHalf, 0)), 0),
NotReadyTime = SUM(ISNULL(ASGH.NotReadyTimeToHalf, 0)),
TotalNotReadytime = (SELECT SUM(ISNULL(AHH.NotReadyTimeToHalf, 0)) FROM Agent_Half_Hour AHH
       WHERE AHH.DateTime >= :start_date and AHH.DateTime <= :end_date and AHH.SkillTargetID = AG.SkillTargetID and AHH.MRDomainID = MRD.MRDomainID),
AllAgentsNotReadytime = (SELECT SUM(ISNULL(AHH.NotReadyTimeToHalf, 0)) FROM Agent_Half_Hour AHH
       WHERE AHH.DateTime >= :start_date and AHH.DateTime <= :end_date and AHH.SkillTargetID in (:peragt_list) ),
per_notready =  ISNULL(SUM(ISNULL(ASGH.NotReadyTimeToHalf, 0)) * 1.0 /  SUM(ISNULL(ASGH.LoggedOnTimeToHalf, 0)), 0),
HoldTime = SUM(ISNULL(ASGH.HoldTimeToHalf, 0)),
per_hold = ISNULL(SUM(ISNULL(ASGH.HoldTimeToHalf, 0)) * 1.0 / SUM(ISNULL(ASGH.LoggedOnTimeToHalf, 0)), 0),
Reserved = SUM(ISNULL(ASGH.ReservedStateTimeToHalf, 0)),
per_reserved = ISNULL(SUM(ISNULL(ASGH.ReservedStateTimeToHalf, 0)) * 1.0 / SUM(ISNULL(ASGH.LoggedOnTimeToHalf, 0)), 0),
WrapTime = SUM(ISNULL(ASGH.WorkReadyTimeToHalf, 0)) + SUM(ISNULL(ASGH.WorkNotReadyTimeToHalf, 0)),
per_wrapuptime = ISNULL((SUM(ISNULL(ASGH.WorkReadyTimeToHalf, 0)) +
          SUM(ISNULL(ASGH.WorkNotReadyTimeToHalf, 0))) * 1.0 / SUM(ISNULL(ASGH.LoggedOnTimeToHalf, 0)), 0),
BusyOther = SUM(ISNULL(ASGH.BusyOtherTimeToHalf, 0)),
per_busyother = ISNULL(SUM(ISNULL(ASGH.BusyOtherTimeToHalf, 0)) * 1.0 / SUM(ISNULL(ASGH.LoggedOnTimeToHalf, 0)), 0),
StartDate = CONVERT(DATETIME, :start_date),
EndDate = CONVERT(DATETIME, :end_date)
FROM
Agent_Skill_Group_Half_Hour ASGH,
Agent AG,
Person PN,
Media_Routing_Domain MRD,
/*Agent_Half_Hour AHH,*/
Skill_Group SG

WHERE
/*ASGH.SkillTargetID = AHH.SkillTargetID AND
ASGH.DateTime = AHH.DateTime AND*/
ASGH.SkillGroupSkillTargetID = SG.SkillTargetID AND
SG.MRDomainID = MRD.MRDomainID AND
AG.PersonID = PN.PersonID AND
ASGH.SkillTargetID = AG.SkillTargetID AND
ASGH.SkillTargetID in (:peragt_list) AND
ASGH.DateTime >= :start_date AND
ASGH.DateTime <= :end_date
       
GROUP BY
PN.LastName,
PN.FirstName,
MRD.EnterpriseName,
ASGH.SkillTargetID,
SG.SkillTargetID,
SG.EnterpriseName,
Convert(DateTime,(Convert(Varchar(20), ASGH.DateTime, 112))),
AG.SkillTargetID,
MRD.MRDomainID
ORDER BY
PN.LastName,
PN.FirstName,
MRD.EnterpriseName,
SG.EnterpriseName,
SG.SkillTargetID,
Convert(DateTime,(Convert(Varchar(20), ASGH.DateTime, 112)))
OPTION (MAXDOP 1)

Subject: RE: CUIC - Agent Historical Summary of Logged On Time
Replied by: Senthil Kumar Natarajan on 21-04-2012 10:35:10 AM
can you share your report? or the query you are using ?

Subject: RE: CUIC - Agent Historical Summary of Logged On Time
Replied by: Luis Yrigoyen on 30-04-2012 09:34:50 AM
Are you looking only for total logon time per day?
i.e. agent's first login time and agent's last logout time per day

Attachments

    Outcomes