Buy or Renew
Buy or Renew
Cisco + Splunk: It’s a new day for your data. Learn more.
 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Start a conversation
1093
Views
0
Helpful
1
Replies

CUIC 9.01 Report Definitions.

Danilo.peguero
Level 1
Level 1

‎07-13-2017 11:46 AM

Hi Team, I'm experiencing some difficulty on my CUIC reporting. I'm trying to create a report that can shoe the HandleTime on this format. hh:mm:sec. I also Would like to see the SL Total,.

Please let me know if you can help. Here is My Custom Report Definitions.

SELECT

CTHH.ReportingInterval,

CTHH.CallTypeID,  CTHH.TimeZone,   CTHH.RecoveryKey,  

sum(isnull(CTHH.RouterQueueWaitTime,0)) as RouterQueueWaitTime,  

sum(isnull(CTHH.RouterQueueCalls,0)) as RouterQueueCalls,

AvgRouterDelay = sum(isnull(CTHH.AvgRouterDelayQ,0)),

sum(isnull(CTHH.RouterCallsAbandQ,0)) as RouterCallsAbandQ,  

sum(isnull(CTHH.RouterQueueCallTypeLimit,0)) as RouterQueueCallTypeLimit,  

sum(isnull(CTHH.RouterQueueGlobalLimit,0)) as RouterQueueGlobalLimit,  

sum(isnull(CTHH.CallsRouted,0)) as CallsRouted,  

sum(isnull(CTHH.ErrorCount,0)) as ErrorCount,  

sum(isnull(CTHH.ICRDefaultRouted,0)) as ICRDefaultRouted,  

sum(isnull(CTHH.NetworkDefaultRouted,0)) as NetworkDefaultRouted,   

sum(isnull(CTHH.ReturnBusy,0)) as ReturnBusy,  

sum(isnull(CTHH.ReturnRing,0)) as ReturnRing,  

sum(isnull(CTHH.NetworkAnnouncement,0)) as NetworkAnnouncement,  

sum(isnull(CTHH.AnswerWaitTime,0)) as AnswerWaitTime,  

sum(isnull(CTHH.CallsHandled,0)) as CallsHandled,  

sum(isnull(CTHH.CallsOffered,0)) as CallsOffered,  

sum(isnull(CTHH.HandleTime,0)) as HandleTime,  

sum(isnull(CTHH.ServiceLevelAband,0)) as ServiceLevelAband,  

sum(isnull(CTHH.ServiceLevelCalls,0)) as ServiceLevelCalls,  

sum(isnull(CTHH.ServiceLevelCallsOffered, 0)) as ServiceLevelCallsOffered,  

sum(isnull(CTHH.ServiceLevel,0)) as ServiceLevel,  

sum(isnull(CTHH.TalkTime,0)) as TalkTime,  

sum(isnull(CTHH.OverflowOut,0)) as OverflowOut,  

sum(isnull(CTHH.HoldTime,0)) as HoldTime,  

sum(isnull(CTHH.IncompleteCalls,0)) as IncompleteCalls,  

CTHH.DateTime,  

Datepart(yy, CTHH.DateTime) as Year,

Datepart(mm, CTHH.DateTime) as Month,

Datepart(ww, CTHH.DateTime) as Week,

Datepart(dy, CTHH.DateTime) as DOY,

Datepart(dw, CTHH.DateTime) as DOW,

CONVERT(char(10),CTHH.DateTime,101) as Date,

Call_Type.EnterpriseName,

sum(isnull(CTHH.ShortCalls , 0))  as ShortCalls,

sum(isnull(CTHH.DelayQAbandTime , 0))  as DelayQAbandTime,

sum(isnull(CTHH.CallsAnswered , 0))  as CallsAnswered,

sum(isnull(CTHH.CallsRoutedNonAgent , 0))  as CallsRoutedNonAgent,

sum(isnull(CTHH.CallsRONA , 0)) as CallsRONA,

sum(isnull(CTHH.ReturnRelease , 0))  as ReturnRelease,

sum(isnull(CTHH.CallsQHandled , 0))  as CallsQHandled,

sum(isnull(CTHH.VruUnhandledCalls , 0)) as VruUnhandledCalls,

sum(isnull(CTHH.VruHandledCalls , 0)) as VruHandledCalls,

sum(isnull(CTHH.VruAssistedCalls , 0))  as VruAssistedCalls,

sum(isnull(CTHH.VruOptOutUnhandledCalls, 0)) as VruOptOutUnhandledCalls,

sum(isnull(CTHH.VruScriptedXferredCalls, 0)) as VruScriptedXferredCalls,

sum(isnull(CTHH.VruForcedXferredCalls , 0)) as VruForcedXferredCalls,

sum(isnull(CTHH.VruOtherCalls, 0)) as VruOtherCalls,

CTHH.ServiceLevelType as ServiceLevelType,

CTHH.BucketIntervalID as BucketIntervalID,

sum(isnull(CTHH.AnsInterval1,0)) as AnsInterval1,

sum(isnull(CTHH.AnsInterval2,0)) as AnsInterval2,

sum(isnull(CTHH.AnsInterval3,0)) as AnsInterval3,

sum(isnull(CTHH.AnsInterval4,0)) as AnsInterval4,

sum(isnull(CTHH.AnsInterval5,0)) as AnsInterval5,

sum(isnull(CTHH.AnsInterval6,0)) as AnsInterval6,

sum(isnull(CTHH.AnsInterval7,0)) as AnsInterval7,

sum(isnull(CTHH.AnsInterval8,0)) as AnsInterval8,

sum(isnull(CTHH.AnsInterval9,0)) as AnsInterval9,

sum(isnull(CTHH.AnsInterval10,0)) as AnsInterval10,

sum(isnull(CTHH.AbandInterval1,0)) as AbandInterval1 ,

sum(isnull(CTHH.AbandInterval2,0)) as AbandInterval2,

sum(isnull(CTHH.AbandInterval3,0)) as AbandInterval3 ,

sum(isnull(CTHH.AbandInterval4,0)) as AbandInterval4,

sum(isnull(CTHH.AbandInterval5,0)) as AbandInterval5,

sum(isnull(CTHH.AbandInterval6,0)) as AbandInterval6,

sum(isnull(CTHH.AbandInterval7,0)) as AbandInterval7 ,

sum(isnull(CTHH.AbandInterval8,0)) as AbandInterval8,

sum(isnull(CTHH.AbandInterval9,0)) as AbandInterval9,

sum(isnull(CTHH.AbandInterval10,0)) as AbandInterval10,

CTHH.DbDateTime, 

sum(isnull(CTHH.RouterCallsAbandToAgent,0)) as RouterCallsAbandToAgent,  

sum(isnull(CTHH.TotalCallsAband, 0)) as TotalCallsAband,  

sum(isnull(CTHH.DelayAgentAbandTime,0)) as DelayAgentAbandTime,  

sum(isnull(CTHH.CallDelayAbandTime,0)) as CallDelayAbandTime,  

sum(isnull(CTHH.CTDelayAbandTime,0)) as CTDelayAbandTime,  

sum(isnull(CTHH.ServiceLevelError ,0)) as ServiceLevelError,  

sum(isnull(CTHH.ServiceLevelRONA,0)) as ServiceLevelRONA,  

sum(isnull(CTHH.AgentErrorCount,0)) as AgentErrorCount,  

sum(isnull(CTHH.VRUTime,0)) as VRUTime,  

ReturnBR = sum(isnull(CTHH.ReturnBusy,0)) + sum(isnull(CTHH.ReturnRing,0)) + sum(isnull(CTHH.ReturnRelease,0)),

sum(isnull(CTHH.CTVRUTime,0))  as CTVRUTime,

asa= case when sum(isnull(CTHH.CallsAnswered,0)) = 0 then 0

            else sum(isnull(CTHH.AnswerWaitTime,0)) * 1.0

            / sum(isnull(CTHH.CallsAnswered,0))

            end,

      per_aban = (sum(isnull(CTHH.TotalCallsAband,0)) * 1.0)

            / (sum(isnull(CTHH.CallsHandled,0))

            + sum(isnull(CTHH.TotalCallsAband,0))

            + sum(isnull(CTHH .IncompleteCalls,0))

            + sum(isnull(CTHH.ReturnBusy,0))

            + sum(isnull(CTHH.ReturnRing,0))

            + sum(isnull(CTHH.ICRDefaultRouted,0))

            + sum(isnull(CTHH.NetworkDefaultRouted,0))

            + sum(isnull(CTHH.OverflowOut,0))

            + sum(isnull(CTHH.CallsRONA,0))

            + sum(isnull(CTHH.ReturnRelease,0))

            + sum(isnull(CTHH.CallsRoutedNonAgent,0))

            + sum(isnull(CTHH.ShortCalls,0))

            + sum(isnull(CTHH.AgentErrorCount,0))

            + sum(isnull(CTHH.ErrorCount,0))),

      avg_aban_delay = case when sum(isnull(CTHH.TotalCallsAband,0)) = 0 then 0

            else (sum(isnull(CTHH.CallDelayAbandTime,0)) * 1.0)

            / sum(isnull(CTHH.TotalCallsAband,0))

            end,

      totalerrorcount= sum(isnull(CTHH.ErrorCount,0))

            + sum(isnull(CTHH.IncompleteCalls,0)) + sum(isnull(CTHH.AgentErrorCount,0)),

      CompletedTasks = sum(isnull(CTHH.CallsHandled,0))

            + sum(isnull(CTHH.TotalCallsAband,0))

            + sum(isnull(CTHH.IncompleteCalls,0))

            + sum(isnull(CTHH.ReturnBusy,0))

            + sum(isnull(CTHH.ReturnRing,0))

            + sum(isnull(CTHH.ICRDefaultRouted,0))

            + sum(isnull(CTHH.NetworkDefaultRouted,0))

            + sum(isnull(CTHH.OverflowOut,0))

            + sum(isnull(CTHH.CallsRONA,0))

            + sum(isnull(CTHH.ReturnRelease,0))

            + sum(isnull(CTHH.CallsRoutedNonAgent,0))

            + sum(isnull(CTHH.ShortCalls,0))

            + sum(isnull(CTHH.AgentErrorCount,0))

            + sum(isnull(CTHH.ErrorCount,0)),

      Other = sum(isnull(CTHH.CallsRONA,0))

            + sum(isnull(CTHH.CallsRoutedNonAgent,0))

            + sum(isnull(CTHH.ShortCalls,0)),  

      per_queued = case when sum(isnull(CTHH.CallsHandled,0)) = 0 then 0

            else sum(isnull(CTHH.CallsQHandled,0)) * 1.0

            / sum(isnull(CTHH.CallsHandled,0))

            End,

            MaxCallsQueued=sum(CTHH.MaxCallsQueued),

            MaxCallWaitTime=sum(CTHH.MaxCallWaitTime)

FROM Call_Type_Interval CTHH (nolock),  

Call_Type (nolock) 

WHERE ( CTHH.CallTypeID = Call_Type.CallTypeID )

Group By CTHH.DateTime,

Call_Type.EnterpriseName,

CTHH.CallTypeID,  

CTHH.TimeZone,  

CTHH.RecoveryKey,  

CTHH.ServiceLevelType ,

CTHH.DbDateTime, 

CTHH.BucketIntervalID,

CTHH.ReportingInterval

ORDER BY Call_Type.EnterpriseName,CTHH.DateTime

Labels:
0 Helpful
1 Reply 1

jacparke
Level 5
Level 5

‎09-04-2017 05:40 AM

Formatting a date time - Once you have the SQL set up, go to the Fields tab, find the datetime field in question, at the bottom of the page is a formatting button, hh:mm:ss is one of the options therein.

SL calculation is tricky - especially at the summary level as there are many things that affect how it is computed.  I suggest leveraging the logic from one of the stock reports which do that calculation.  The SQL is something like:

SLType = min(isnull(Skill_Group.ServiceLevelType,0)),

        DoNotUseSLTopTo5 = CASE min(isnull(Skill_Group.ServiceLevelType,0))

   WHEN 0 THEN CASE (SELECT min(isnull(CallTypeServiceLevelType,0)) from ICR_Globals)

      WHEN 1 THEN sum(isnull(SGRT.ServiceLevelCallsTo5,0)) * 1.0

      WHEN 2 THEN sum(isnull(SGRT.ServiceLevelCallsTo5,0)) * 1.0

      WHEN 3 THEN (sum(isnull(SGRT.ServiceLevelCallsTo5,0)) + sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0))) * 1.0

      ELSE 0 END

   WHEN 1 THEN sum(isnull(SGRT.ServiceLevelCallsTo5,0)) * 1.0

   WHEN 2 THEN sum(isnull(SGRT.ServiceLevelCallsTo5,0)) * 1.0

   WHEN 3 THEN (sum(isnull(SGRT.ServiceLevelCallsTo5,0)) + sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0))) * 1.0

  ELSE 0 END,

DoNotUseSLBottomTo5 = CASE min(isnull(Skill_Group.ServiceLevelType,0))

   WHEN 0 THEN CASE (SELECT min(isnull(CallTypeServiceLevelType,0)) from ICR_Globals)

      WHEN 1 THEN (sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) - sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0)))

      WHEN 2 THEN sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0))

      WHEN 3 THEN sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0))

      ELSE 0 END

   WHEN 1 THEN (sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) - sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0)))

   WHEN 2 THEN sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0))

   WHEN 3 THEN sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0))

   ELSE 0 END,

servicelLevelTo5 = CASE min(isnull(Skill_Group.ServiceLevelType,0))

   WHEN 0 THEN CASE (SELECT min(isnull(CallTypeServiceLevelType,0)) from ICR_Globals)

      WHEN 1 THEN (CASE WHEN (sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) - sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0))) = 0 THEN 0

         ELSE sum(isnull(SGRT.ServiceLevelCallsTo5,0)) * 1.0 /

(sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) - sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0))) END)

      WHEN 2 THEN (CASE WHEN sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) = 0 THEN 0

        ELSE sum(isnull(SGRT.ServiceLevelCallsTo5,0)) * 1.0 /

sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) END)

      WHEN 3 THEN (CASE WHEN sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) = 0 THEN 0

        ELSE (sum(isnull(SGRT.ServiceLevelCallsTo5,0)) + sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0))) * 1.0 /

sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) END)

      ELSE 0 END

   WHEN 1 THEN (CASE WHEN (sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) - sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0))) = 0 THEN 0

    ELSE sum(isnull(SGRT.ServiceLevelCallsTo5,0)) * 1.0 /

(sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) - sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0))) END)

   WHEN 2 THEN (CASE WHEN sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) = 0 THEN 0

     ELSE sum(isnull(SGRT.ServiceLevelCallsTo5,0)) * 1.0 /

sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) END)

   WHEN 3 THEN (CASE WHEN sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) = 0 THEN 0

     ELSE (sum(isnull(SGRT.ServiceLevelCallsTo5,0)) + sum(isnull(SGRT.ServiceLevelCallsAbandTo5,0))) * 1.0 /

        sum(isnull(SGRT.ServiceLevelCallsOfferedTo5,0)) END)

   ELSE 0 END,ServiceLevelCallsOfferedTo5 = SUM(ISNULL(SGRT.ServiceLevelCallsOfferedTo5, 0)),


Then you set up a custom formula for the SL summary line.  Again - leverage one of the existing reports. "Peripheral Skill Group Real Time All Fields" comes to mind.


Regards,

Jack Parker

0 Helpful
Customers Also Viewed These Support Documents