CUIC - Agent Historical Summary of Logged On Time

Version 1
    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