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
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
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide