Webview to CUIC of agtskg20

Version 1
    This document was generated from CDN thread

    Created by: Neil Ambrosen on 29-03-2012 11:53:58 AM
    Hi all
    I'm trying to copy the above report into CUIC, and almost there as per Luis's instructions on his other thread, however its not quite displaying correctly, I've played around but still can't get it quite right and I'm probably missing something really obvious!
    The webview report looks like this:
    http://i51.photobucket.com/albums/f400/SkyJawa/wv.jpg
     
    CUIC looks like this:
    http://i51.photobucket.com/albums/f400/SkyJawa/cuic.jpg
    And the SQL is below:
    SELECT
    Agent_Real_Time.Extension,  
       Agent_Skill_Group_Real_Time.SkillGroupSkillTargetID,  
       Agent_Skill_Group_Real_Time.DateTimeLogin,  
       Agent_Skill_Group_Real_Time.AgentState ,
       Agent_Skill_Group_Real_Time.DateTimeLastStateChange,  
    ReasonCode = CASE WHEN Agent_Skill_Group_Real_Time.ReasonCode = 0 THEN 'None'
      ELSE CONVERT(VARCHAR, Agent_Skill_Group_Real_Time.ReasonCode) END,
       SkillGroup = Skill_Group.EnterpriseName +' (' + Convert(Char(6),Skill_Group.SkillTargetID) + ')',  
       Agent_Real_Time.Destination, 
          CASE Agent_Real_Time.Destination When 0 THEN 'Not Applicable'
      WHEN 1 THEN 'ACD' WHEN 2 THEN 'Direct' WHEN 3 THEN 'Auto Out' WHEN 4 
      THEN 'Reserve' WHEN 5 THEN 'Preview' ELSE 'Not Applicable' END, 
    RequestedSupervisorAssist,
       Agent_Real_Time.Direction,
          CASE Agent_Real_Time.Direction When 0 THEN 'Not Applicable'
      WHEN 1 THEN 'In' WHEN 2 THEN 'Out' WHEN 3 THEN 'Other'
      ELSE 'Not Applicable' END,
    Duration =
      DATEDIFF(ss,Agent_Skill_Group_Real_Time.DateTimeLastStateChange,
         CASE
       WHEN (DATEDIFF(ss, Agent_Skill_Group_Real_Time.DateTimeLastStateChange,
       (SELECT NowTime from Controller_Time))  <= 0 )
        THEN Agent_Skill_Group_Real_Time.DateTimeLastStateChange
         ELSE (SELECT NowTime FROM Controller_Time)
      END),
    RouterCallsQ = isnull(Skill_Group_Real_Time.RouterCallsQNow,0) + isnull(Skill_Group_Real_Time.CallsQueuedNow,0),
    activetasks = Agent_Skill_Group_Real_Time.CallsInProgress,
    Agent_Real_Time.AvailableInMRD,
    Media = Media_Routing_Domain.EnterpriseName ,
    AgentName = Person.LastName + ', ' + Person.FirstName
    FROM
    Agent_Real_Time,
    Agent,
    Skill_Group,
    Person,
    Skill_Group_Real_Time,
    Agent_Skill_Group_Real_Time,
    Media_Routing_Domain
    WHERE
    Agent_Real_Time.SkillTargetID = Agent_Skill_Group_Real_Time.SkillTargetID
    AND Agent.SkillTargetID = Agent_Real_Time.SkillTargetID
    AND Skill_Group.SkillTargetID = Agent_Skill_Group_Real_Time.SkillGroupSkillTargetID
    AND Agent_Real_Time.MRDomainID=Media_Routing_Domain.MRDomainID
    AND Skill_Group.SkillTargetID = Skill_Group_Real_Time.SkillTargetID
    AND Skill_Group.MRDomainID=Media_Routing_Domain.MRDomainID
    AND Agent.PersonID=Person.PersonID
    AND Skill_Group.SkillTargetID NOT IN
      (SELECT BaseSkillTargetID FROM Skill_Group WHERE Priority > 0) AND
           Agent_Skill_Group_Real_Time.SkillGroupSkillTargetID IN
      ( :agtskg_list )
    GROUP BY
    Person.LastName + ', ' + Person.FirstName,
    Skill_Group.EnterpriseName,
    Agent.EnterpriseName,
    Agent_Real_Time.Extension,
      Agent_Skill_Group_Real_Time.SkillGroupSkillTargetID,  
       Agent_Skill_Group_Real_Time.DateTimeLogin,  
       Agent_Skill_Group_Real_Time.AgentState ,
       Agent_Skill_Group_Real_Time.DateTimeLastStateChange,
    Agent_Skill_Group_Real_Time.ReasonCode,
    Skill_Group.SkillTargetID,
    Agent_Real_Time.Destination,
    RequestedSupervisorAssist,
       Agent_Real_Time.Direction,
    Agent.SkillTargetID,
    Skill_Group_Real_Time.RouterCallsQNow,
    Skill_Group_Real_Time.CallsQueuedNow,
    Agent_Skill_Group_Real_Time.CallsInProgress,
    Agent_Real_Time.AvailableInMRD,
    Media_Routing_Domain.EnterpriseName
    ORDER BY
    Skill_Group.EnterpriseName,
    Person.LastName+', '+Person.FirstName

    Can anyone point out how to keep the skill group and queued calls on a separate line and then all the agents listed below as per webview - as it stands it looks like each agent would have calls queued to them not the skill - misleading to the customer if there are 3 calls queued to the skill but 3 show to each agent!!

    Thanks
    Neil

    Subject: RE: Webview to CUIC of agtskg20
    Replied by: Senthil Kumar Natarajan on 29-03-2012 06:32:19 PM
    Hi all
    I'm trying to copy the above report into CUIC, and almost there as per Luis's instructions on his other thread, however its not quite displaying correctly, I've played around but still can't get it quite right and I'm probably missing something really obvious!
    The webview report looks like this:
    http://i51.photobucket.com/albums/f400/SkyJawa/wv.jpg
     
    CUIC looks like this:
    http://i51.photobucket.com/albums/f400/SkyJawa/cuic.jpg
    And the SQL is below:
    SELECT
    Agent_Real_Time.Extension,  
       Agent_Skill_Group_Real_Time.SkillGroupSkillTargetID,  
       Agent_Skill_Group_Real_Time.DateTimeLogin,  
       Agent_Skill_Group_Real_Time.AgentState ,
       Agent_Skill_Group_Real_Time.DateTimeLastStateChange,  
    ReasonCode = CASE WHEN Agent_Skill_Group_Real_Time.ReasonCode = 0 THEN 'None'
      ELSE CONVERT(VARCHAR, Agent_Skill_Group_Real_Time.ReasonCode) END,
       SkillGroup = Skill_Group.EnterpriseName +' (' + Convert(Char(6),Skill_Group.SkillTargetID) + ')',  
       Agent_Real_Time.Destination, 
          CASE Agent_Real_Time.Destination When 0 THEN 'Not Applicable'
      WHEN 1 THEN 'ACD' WHEN 2 THEN 'Direct' WHEN 3 THEN 'Auto Out' WHEN 4 
      THEN 'Reserve' WHEN 5 THEN 'Preview' ELSE 'Not Applicable' END, 
    RequestedSupervisorAssist,
       Agent_Real_Time.Direction,
          CASE Agent_Real_Time.Direction When 0 THEN 'Not Applicable'
      WHEN 1 THEN 'In' WHEN 2 THEN 'Out' WHEN 3 THEN 'Other'
      ELSE 'Not Applicable' END,
    Duration =
      DATEDIFF(ss,Agent_Skill_Group_Real_Time.DateTimeLastStateChange,
         CASE
       WHEN (DATEDIFF(ss, Agent_Skill_Group_Real_Time.DateTimeLastStateChange,
       (SELECT NowTime from Controller_Time))  <= 0 )
        THEN Agent_Skill_Group_Real_Time.DateTimeLastStateChange
         ELSE (SELECT NowTime FROM Controller_Time)
      END),
    RouterCallsQ = isnull(Skill_Group_Real_Time.RouterCallsQNow,0) + isnull(Skill_Group_Real_Time.CallsQueuedNow,0),
    activetasks = Agent_Skill_Group_Real_Time.CallsInProgress,
    Agent_Real_Time.AvailableInMRD,
    Media = Media_Routing_Domain.EnterpriseName ,
    AgentName = Person.LastName + ', ' + Person.FirstName
    FROM
    Agent_Real_Time,
    Agent,
    Skill_Group,
    Person,
    Skill_Group_Real_Time,
    Agent_Skill_Group_Real_Time,
    Media_Routing_Domain
    WHERE
    Agent_Real_Time.SkillTargetID = Agent_Skill_Group_Real_Time.SkillTargetID
    AND Agent.SkillTargetID = Agent_Real_Time.SkillTargetID
    AND Skill_Group.SkillTargetID = Agent_Skill_Group_Real_Time.SkillGroupSkillTargetID
    AND Agent_Real_Time.MRDomainID=Media_Routing_Domain.MRDomainID
    AND Skill_Group.SkillTargetID = Skill_Group_Real_Time.SkillTargetID
    AND Skill_Group.MRDomainID=Media_Routing_Domain.MRDomainID
    AND Agent.PersonID=Person.PersonID
    AND Skill_Group.SkillTargetID NOT IN
      (SELECT BaseSkillTargetID FROM Skill_Group WHERE Priority > 0) AND
           Agent_Skill_Group_Real_Time.SkillGroupSkillTargetID IN
      ( :agtskg_list )
    GROUP BY
    Person.LastName + ', ' + Person.FirstName,
    Skill_Group.EnterpriseName,
    Agent.EnterpriseName,
    Agent_Real_Time.Extension,
      Agent_Skill_Group_Real_Time.SkillGroupSkillTargetID,  
       Agent_Skill_Group_Real_Time.DateTimeLogin,  
       Agent_Skill_Group_Real_Time.AgentState ,
       Agent_Skill_Group_Real_Time.DateTimeLastStateChange,
    Agent_Skill_Group_Real_Time.ReasonCode,
    Skill_Group.SkillTargetID,
    Agent_Real_Time.Destination,
    RequestedSupervisorAssist,
       Agent_Real_Time.Direction,
    Agent.SkillTargetID,
    Skill_Group_Real_Time.RouterCallsQNow,
    Skill_Group_Real_Time.CallsQueuedNow,
    Agent_Skill_Group_Real_Time.CallsInProgress,
    Agent_Real_Time.AvailableInMRD,
    Media_Routing_Domain.EnterpriseName
    ORDER BY
    Skill_Group.EnterpriseName,
    Person.LastName+', '+Person.FirstName

    Can anyone point out how to keep the skill group and queued calls on a separate line and then all the agents listed below as per webview - as it stands it looks like each agent would have calls queued to them not the skill - misleading to the customer if there are 3 calls queued to the skill but 3 show to each agent!!

    Thanks
    Neil


     
     
    Would the attached work for you?
     
    -sen

    Subject: RE: Webview to CUIC of agtskg20
    Replied by: Neil Ambrosen on 30-03-2012 04:38:14 AM
    Hi Sen

    Thanks for that - absolutely that would work, its the other way I was looking at to do it.  What am I missing?

    Thanks
    Neil

    Subject: RE: Webview to CUIC of agtskg20
    Replied by: Senthil Kumar Natarajan on 30-03-2012 09:23:55 AM
    Here is what i did,
    1. Run the report
    2. Click 'edit'
    3. Click 'Groupings'. For no. of groups select 2 from the radio button. For Group 1, select the 'SkillGroup' from the dropbox, For Group 2, select 'RouterCallsQ' and enable the show summary.
    4. Click 'Ok'
    5. Click Save

    -Sen

    Subject: RE: Webview to CUIC of agtskg20
    Replied by: Neil Ambrosen on 30-03-2012 10:38:05 AM
    See I told you I was missing something obvious and staring me in the face!!

    Thanks!

    Subject: RE: Webview to CUIC of agtskg20
    Replied by: Luis Yrigoyen on 03-04-2012 02:33:55 PM
    Neil,
    What does this report show? The real-time state of an agent with reason code of the state? per skill group?

    is that correct?

    thanks

    Subject: RE: Webview to CUIC of agtskg20
    Replied by: Neil Ambrosen on 04-04-2012 04:54:35 AM
    Hi Luis

    Pretty much yes.  Its the agtskg20 - ICM Agent Skill Group Real Time report.  Basically shows real time status of agents by skill group.  Provides number of calls queued to the skill and then the agents logon time, state, duration in state, reason code etc

    Cheers
    Neil