Cuic Summary Report Divide by Zero error

Shalid Kurunnan Chalil · ‎03-04-2013

Hi Experts,

Request your guidance to resolve below CUIC issues,

CUIC Version : CUIC 8.5.4

License : Standard

our customer is getting data set error intermittenlty when they run Cisco Summary reports.

When we expand the data set error it gives us SQL Exception divide by zero error. And they are not recieving such error when they run cisco stock report for the same selection.

Please advise how can we get ride of it.

Thanks and Regards,

Shalid K.C

Gergely Szabo · ‎03-04-2013

Hi,

there's one way to get rid of it: correct your SQL query so there's no way that a divide by zero situation occurs.

Can you post the query?

G.

Shalid Kurunnan Chalil · ‎03-04-2013

thanks for the quick response ..

It is not a customized report, which was imported from Cisco site and it is cisco summary report (Agent Team Historical All Field)..

And it is happening Intermittently only for the same selection.

Gergely Szabo · ‎03-04-2013

Well I cannot help you unless I can take a look at it.

Shalid Kurunnan Chalil · ‎03-13-2013

Hi ,

Please find below SQL query,

SELECT AgentTeamName= AgentSkillGroup.AgentTeamName ,
Supervisor= AgentSkillGroup.Supervisor,
Media = AgentSkillGroup.Media,
Interval = AgentSkillGroup.Interval,
Year= AgentSkillGroup.Year,
Month = AgentSkillGroup.Month,

FullName = AgentSkillGroup.FullName,
AgentSkillID= AgentSkillGroup.AgentSkillID,
AgentTeamSkillID= AgentSkillGroup.AgentTeamSkillID,
PriSupervisorSkillTargetID= AgentSkillGroup.PriSupervisorSkillTargetID,
CallsAnswered= AgentSkillGroup.CallsAnswered,
CallsHandled = AgentSkillGroup.CallsHandled,
AbandRingCalls = AgentSkillGroup.AbandRingCalls,
AbandRingCallsTime = AgentSkillGroup.AbandRingCallsTime,
RedirectCalls = AgentSkillGroup.RedirectCalls,
RedirectCallsTime= AgentSkillGroup.RedirectCallsTime,
AbandonHoldCalls= AgentSkillGroup.AbandonHoldCalls,
TransferInCalls = AgentSkillGroup.TransferInCalls,
TransferOutCalls= AgentSkillGroup.TransferOutCalls,
ConsultativeCalls= AgentSkillGroup.ConsultativeCalls,
ConferenceInCalls= AgentSkillGroup.ConferenceInCalls,
ConferenceOutCalls= AgentSkillGroup.ConferenceOutCalls,
OutExtnCalls= AgentSkillGroup.OutExtnCalls,
ShortCalls= AgentSkillGroup.ShortCalls,
SupAssistCalls= AgentSkillGroup.SupAssistCalls,
BargeInCalls= AgentSkillGroup.BargeInCalls,
InterceptCalls= AgentSkillGroup.InterceptCalls,
MonitorCalls= AgentSkillGroup.MonitorCalls,
WhisperCalls= AgentSkillGroup.WhisperCalls,
EmergencyAssistCalls= AgentSkillGroup.EmergencyAssistCalls,
SupAssistCallsTime= AgentSkillGroup.SupAssistCallsTime,
AgentOutCallsOnHold= AgentSkillGroup.AgentOutCallsOnHold,
AgentOutCallsOnHoldTime= AgentSkillGroup.AgentOutCallsOnHoldTime,
InCallsOnHold= AgentSkillGroup.InCallsOnHold,
InCallsOnHoldTime= AgentSkillGroup.InCallsOnHoldTime,
IntCallsOnHold= AgentSkillGroup.IntCallsOnHold,
IntCallsOnHoldTime= AgentSkillGroup.IntCallsOnHoldTime,
TalkTime= AgentSkillGroup.TalkTime,
HandledCallsTime= AgentSkillGroup.HandledCallsTime,
LoggedOnTime= AgentSkillGroup.LoggedOnTime,
HoldTime= AgentSkillGroup.HoldTime,
AvailTime= AgentHalfHour.AvailTime,
NotReadyTime= AgentHalfHour.NotReadyTime,
ReservedTime= AgentSkillGroup.ReservedTime,
WrapTime= AgentSkillGroup.WrapTime,
BusyOtherTime= AgentSkillGroup.BusyOtherTime,
AnswerWaitTime= AgentSkillGroup.AnswerWaitTime,
AutoOutCalls= AgentSkillGroup.AutoOutCalls,
AutoOutCallsTime= AgentSkillGroup.AutoOutCallsTime,
AutoOutCallsTalkTime= AgentSkillGroup.AutoOutCallsTalkTime,
AutoOutCallsOnHold= AgentSkillGroup.AutoOutCallsOnHold,
AutoOutCallsOnHoldTime= AgentSkillGroup.AutoOutCallsOnHoldTime,
PreviewCalls= AgentSkillGroup.PreviewCalls,
PreviewCallsTime= AgentSkillGroup.PreviewCallsTime,
PreviewCallsTalkTime= AgentSkillGroup.PreviewCallsTalkTime ,
PreviewCallsOnHold= AgentSkillGroup.PreviewCallsOnHold,
PreviewCallsOnHoldTime= AgentSkillGroup.PreviewCallsOnHoldTime,
ReserveCalls= AgentSkillGroup.ReserveCalls,
ReserveCallsTime= AgentSkillGroup.ReserveCallsTime,
ReserveCallsTalkTime= AgentSkillGroup.ReserveCallsTalkTime,
ReserveCallsOnHold= AgentSkillGroup.ReserveCallsOnHold,
ReserveCallsOnHoldTime= AgentSkillGroup.ReserveCallsOnHoldTime,
TalkAutoOutTime= AgentSkillGroup.TalkAutoOutTime,
TalkPreviewTime= AgentSkillGroup.TalkAutoOutTime,
TalkReserveTime= AgentSkillGroup.TalkReserveTime,
AgentOutCallsTalkTime= AgentSkillGroup.AgentOutCallsTalkTime,
AgentTerminatedCalls= AgentSkillGroup.AgentTerminatedCalls,
CallbackMessages= AgentSkillGroup.CallbackMessages,
CallbackMessagesTime= AgentSkillGroup.CallbackMessagesTime,
ConsultativeCallsTime= AgentSkillGroup.ConsultativeCallsTime,
ConferencedInCallsTime= AgentSkillGroup.ConferencedInCallsTime,
ConferencedOutCallsTime= AgentSkillGroup.ConferencedOutCallsTime,
HandledCallsTalkTime= AgentSkillGroup.HandledCallsTalkTime,
InternalCallsRcvd= AgentSkillGroup.InternalCallsRcvd,
InternalCallsRcvdTime= AgentSkillGroup.InternalCallsRcvdTime,
InternalCalls= AgentSkillGroup.InternalCalls,
InternalCallsTime= AgentSkillGroup.InternalCallsTime,
TransferredInCallsTime= AgentSkillGroup.TransferredInCallsTime,
TalkOtherTime= AgentSkillGroup.TalkOtherTime,
TalkOutTime= AgentSkillGroup.TalkOutTime,

InterruptedTime= AgentSkillGroup.InterruptedTime,
WorkNotReadyTime= AgentSkillGroup.WorkNotReadyTime,
WorkReadyTime= AgentSkillGroup.WorkReadyTime,
NetConsultativeCalls= AgentSkillGroup.NetConsultativeCalls,
NetConsultativeCallsTime= AgentSkillGroup.NetConsultativeCallsTime,
NetConferencedOutCalls= AgentSkillGroup.NetConferencedOutCalls,
NetConfOutCallsTime= AgentSkillGroup.NetConfOutCallsTime,
NetTransferredOutCalls= AgentSkillGroup.NetTransferredOutCalls,

AgentLoggedOnTime= AgentHalfHour.LoggedOnTime,
AHT = ISNULL(AgentSkillGroup.HandledCallsTime /(NULLIF(AgentSkillGroup.CallsHandled, 0), 0),
AHoldT = ISNULL(AgentSkillGroup.InCallsOnHoldTime / (NULLIF(AgentSkillGroup.InCallsOnHold, 0), 0),
perActiveTime = ISNULL(AgentSkillGroup.TalkTime * 1.0 / (NULLIF(AgentHalfHour.LoggedOnTime,0), 0),
perHoldTime = ISNULL(AgentSkillGroup.HoldTime * 1.0 / (NULLIF(AgentHalfHour.LoggedOnTime, 0), 0),
perNotActive = ISNULL(AgentHalfHour.AvailTime * 1.0 / (NULLIF(AgentHalfHour.LoggedOnTime, 0), 0),
perNotReady = ISNULL(AgentHalfHour.NotReadyTime * 1.0 / (NULLIF(AgentHalfHour.LoggedOnTime, 0), 0),
perReserved = ISNULL(AgentSkillGroup.ReservedTime * 1.0 / (NULLIF(AgentHalfHour.LoggedOnTime, 0), 0),
perWrapup = ISNULL(AgentSkillGroup.WrapTime * 1.0 / (NULLIF(AgentHalfHour.LoggedOnTime, 0), 0),
perBusyOther = ISNULL(AgentSkillGroup.BusyOtherTime * 1.0 / (NULLIF(AgentHalfHour.LoggedOnTime, 0), 0),

Week= AgentSkillGroup.Week

FROM         (SELECT
Agent_Team.EnterpriseName AS AgentTeamName,
Supervisor=(SELECT Person.LastName + ', ' + Person.FirstName FROM Agent (nolock), Person (nolock) WHERE Agent.SkillTargetID = Agent_Team.PriSupervisorSkillTargetID AND Agent.PersonID = Person.PersonID),
Media_Routing_Domain.EnterpriseName AS Media, dateadd(week, datediff(week,0, ASGHH.DateTime), 0) AS Interval,
DATEPART(yy, ASGHH.DateTime) AS Year,
DATEPART(mm, ASGHH.DateTime) AS Month,

Person.LastName + ', ' + Person.FirstName AS FullName,
Agent.SkillTargetID AS AgentSkillID,
Agent_Team.AgentTeamID AS AgentTeamSkillID,
Agent_Team.PriSupervisorSkillTargetID,
SUM(ISNULL(ASGHH.CallsAnswered, 0)) AS CallsAnswered,
SUM(ISNULL(ASGHH.CallsHandled, 0)) AS CallsHandled,
SUM(ISNULL(ASGHH.AbandonRingCalls, 0)) AS AbandRingCalls,
SUM(ISNULL(ASGHH.AbandonRingTime, 0)) AS AbandRingCallsTime,
SUM(ISNULL(ASGHH.RedirectNoAnsCalls, 0)) AS RedirectCalls,
SUM(ISNULL(ASGHH.RedirectNoAnsCallsTime, 0)) AS RedirectCallsTime,
SUM(ISNULL(ASGHH.AbandonHoldCalls, 0)) AS AbandonHoldCalls,
SUM(ISNULL(ASGHH.TransferredInCalls, 0)) AS TransferInCalls,
SUM(ISNULL(ASGHH.TransferredOutCalls, 0)) AS TransferOutCalls,
SUM(ISNULL(ASGHH.ConsultativeCalls, 0)) AS ConsultativeCalls,
SUM(ISNULL(ASGHH.ConferencedInCalls, 0)) AS ConferenceInCalls,
SUM(ISNULL(ASGHH.ConferencedOutCalls, 0)) AS ConferenceOutCalls,
SUM(ISNULL(ASGHH.AgentOutCalls, 0)) AS OutExtnCalls,
SUM(ISNULL(ASGHH.AgentOutCalls, 0)) AS AgentOutCalls,
SUM(ISNULL(ASGHH.ShortCalls, 0)) AS ShortCalls,
SUM(ISNULL(ASGHH.SupervAssistCalls, 0)) AS SupAssistCalls,
SUM(ISNULL(ASGHH.BargeInCalls, 0)) AS BargeInCalls,
SUM(ISNULL(ASGHH.InterceptCalls, 0)) AS InterceptCalls,
SUM(ISNULL(ASGHH.MonitorCalls, 0)) AS MonitorCalls,
SUM(ISNULL(ASGHH.WhisperCalls, 0)) AS WhisperCalls,
SUM(ISNULL(ASGHH.EmergencyAssists, 0)) AS EmergencyAssistCalls,
SUM(ISNULL(ASGHH.SupervAssistCallsTime, 0)) AS SupAssistCallsTime,
SUM(ISNULL(ASGHH.AgentOutCallsOnHold, 0)) AS AgentOutCallsOnHold,
SUM(ISNULL(ASGHH.AgentOutCallsOnHoldTime, 0)) AS AgentOutCallsOnHoldTime,
SUM(ISNULL(ASGHH.IncomingCallsOnHold, 0)) AS InCallsOnHold,
SUM(ISNULL(ASGHH.IncomingCallsOnHoldTime, 0)) AS InCallsOnHoldTime,
SUM(ISNULL(ASGHH.InternalCallsOnHold, 0)) AS IntCallsOnHold,
SUM(ISNULL(ASGHH.InternalCallsOnHoldTime, 0)) AS IntCallsOnHoldTime,
sum(isnull(ASGHH.TalkInTime,0)) + sum(isnull(ASGHH.TalkOutTime,0)) +
sum(isnull(ASGHH.TalkOtherTime,0)) + sum(isnull(ASGHH.TalkAutoOutTime,0)) +
sum(isnull(ASGHH.TalkPreviewTime,0)) + sum(isnull(ASGHH.TalkReserveTime,0))AS TalkTime,
SUM(ISNULL(ASGHH.HandledCallsTime, 0)) AS HandledCallsTime,
SUM(ISNULL(ASGHH.LoggedOnTime, 0)) AS LoggedOnTime,
SUM(ISNULL(ASGHH.HoldTime, 0)) AS HoldTime,
SUM(ISNULL(ASGHH.AvailTime, 0)) AS AvailTime,
SUM(ISNULL(ASGHH.NotReadyTime, 0)) AS NotReadyTime,
SUM(ISNULL(ASGHH.ReservedStateTime, 0)) AS ReservedTime,
SUM(ISNULL(ASGHH.WorkNotReadyTime + ASGHH.WorkReadyTime, 0)) AS WrapTime,
SUM(ISNULL(ASGHH.BusyOtherTime, 0)) AS BusyOtherTime,
SUM(ISNULL(ASGHH.AnswerWaitTime, 0)) AS AnswerWaitTime,
SUM(ISNULL(ASGHH.AutoOutCalls, 0)) AS AutoOutCalls,
SUM(ISNULL(ASGHH.AutoOutCallsTime, 0)) AS AutoOutCallsTime,
SUM(ISNULL(ASGHH.AutoOutCallsTalkTime, 0)) AS AutoOutCallsTalkTime,
SUM(ISNULL(ASGHH.AutoOutCallsOnHold, 0)) AS AutoOutCallsOnHold,
SUM(ISNULL(ASGHH.AutoOutCallsOnHoldTime, 0)) AS AutoOutCallsOnHoldTime,
SUM(ISNULL(ASGHH.PreviewCalls, 0)) AS PreviewCalls,
SUM(ISNULL(ASGHH.PreviewCallsTime, 0)) AS PreviewCallsTime,
SUM(ISNULL(ASGHH.PreviewCallsTalkTime, 0)) AS PreviewCallsTalkTime,
SUM(ISNULL(ASGHH.PreviewCallsOnHold, 0)) AS PreviewCallsOnHold,
SUM(ISNULL(ASGHH.PreviewCallsOnHoldTime, 0)) AS PreviewCallsOnHoldTime,
SUM(ISNULL(ASGHH.ReserveCalls, 0)) AS ReserveCalls, SUM(ISNULL(ASGHH.ReserveCallsTime, 0)) AS ReserveCallsTime,
SUM(ISNULL(ASGHH.ReserveCallsTalkTime, 0)) AS ReserveCallsTalkTime,
SUM(ISNULL(ASGHH.ReserveCallsOnHold, 0)) AS ReserveCallsOnHold,
SUM(ISNULL(ASGHH.ReserveCallsOnHoldTime, 0)) AS ReserveCallsOnHoldTime,
SUM(ISNULL(ASGHH.TalkAutoOutTime, 0)) AS TalkAutoOutTime,
SUM(ISNULL(ASGHH.TalkPreviewTime, 0)) AS TalkPreviewTime,
SUM(ISNULL(ASGHH.TalkReserveTime, 0)) AS TalkReserveTime,
SUM(ISNULL(ASGHH.AgentOutCallsTalkTime, 0)) AS AgentOutCallsTalkTime,
SUM(ISNULL(ASGHH.AgentTerminatedCalls, 0)) AS AgentTerminatedCalls,
SUM(ISNULL(ASGHH.CallbackMessages, 0)) AS CallbackMessages,
SUM(ISNULL(ASGHH.CallbackMessagesTime, 0)) AS CallbackMessagesTime,
SUM(ISNULL(ASGHH.ConsultativeCallsTime, 0)) AS ConsultativeCallsTime,
SUM(ISNULL(ASGHH.ConferencedInCallsTime, 0)) AS ConferencedInCallsTime,
SUM(ISNULL(ASGHH.ConferencedOutCallsTime, 0)) AS ConferencedOutCallsTime,
SUM(ISNULL(ASGHH.HandledCallsTalkTime, 0)) AS HandledCallsTalkTime,
SUM(ISNULL(ASGHH.InternalCallsRcvd, 0)) AS InternalCallsRcvd,
SUM(ISNULL(ASGHH.InternalCallsRcvdTime, 0)) AS InternalCallsRcvdTime,
SUM(ISNULL(ASGHH.InternalCalls, 0)) AS InternalCalls,
SUM(ISNULL(ASGHH.InternalCallsTime, 0)) AS InternalCallsTime,
SUM(ISNULL(ASGHH.TransferredInCallsTime, 0)) AS TransferredInCallsTime,
SUM(ISNULL(ASGHH.TalkOtherTime, 0)) AS TalkOtherTime,
SUM(ISNULL(ASGHH.TalkOutTime, 0)) AS TalkOutTime,

SUM(ISNULL(ASGHH.InterruptedTime, 0)) AS InterruptedTime,
SUM(ISNULL(ASGHH.WorkNotReadyTime, 0)) AS WorkNotReadyTime,
SUM(ISNULL(ASGHH.WorkReadyTime, 0)) AS WorkReadyTime,
SUM(ISNULL(ASGHH.NetConsultativeCalls, 0)) AS NetConsultativeCalls,
SUM(ISNULL(ASGHH.NetConsultativeCallsTime, 0)) AS NetConsultativeCallsTime,
SUM(ISNULL(ASGHH.NetConferencedOutCalls, 0)) AS NetConferencedOutCalls,
SUM(ISNULL(ASGHH.NetConfOutCallsTime, 0)) AS NetConfOutCallsTime,
SUM(ISNULL(ASGHH.NetTransferredOutCalls, 0)) AS NetTransferredOutCalls,
DATEPART(Week, ASGHH.DateTime) AS Week

FROM
Agent (nolock),
--Agent_Skill_Group_Half_Hour ASGHH (nolock),
Agent_Skill_Group_Interval ASGHH (nolock),
Agent_Team_Member (nolock),
Agent_Team (nolock),
Skill_Group (nolock),
Person (nolock),
Media_Routing_Domain (nolock)

WHERE

Agent_Team_Member.AgentTeamID in (5027)
AND ASGHH.DateTime >= '2013-01-27 00:00:00'
AND ASGHH.DateTime <= '2013-01-28 23:59:59'
AND

( ASGHH.SkillGroupSkillTargetID = Skill_Group.SkillTargetID) and
( Media_Routing_Domain.MRDomainID = Skill_Group.MRDomainID) and
( Skill_Group.SkillTargetID NOT IN (SELECT BaseSkillTargetID FROM Skill_Group (nolock) WHERE (Priority > 0) AND (Deleted <> 'Y')))
and
( Agent.SkillTargetID = ASGHH.SkillTargetID ) and
( Agent.SkillTargetID = Agent_Team_Member.SkillTargetID ) and
( Agent_Team_Member.AgentTeamID = Agent_Team.AgentTeamID ) and
( Agent.PersonID = Person.PersonID )

GROUP BY

Agent_Team.AgentTeamID,
Agent_Team.EnterpriseName,
Agent_Team.PriSupervisorSkillTargetID,
Agent.SkillTargetID,
Person.LastName,
Person.FirstName,
Media_Routing_Domain.EnterpriseName,
DATEPART(yy, ASGHH.DateTime) ,
DATEPART(mm, ASGHH.DateTime) ,
DATEPART(ww, ASGHH.DateTime),

dateadd(week, datediff(week,0, ASGHH.DateTime), 0)
) AgentSkillGroup,

(SELECT
Media = Media_Routing_Domain.EnterpriseName,
LoggedOnTime =sum( AHH.LoggedOnTime),
AvailTime = SUM(ISNULL(AHH.AvailTime, 0)),
NotReadyTime = SUM(ISNULL(AHH.NotReadyTime, 0)),
AgentSkillID = AHH.SkillTargetID,
dateadd(week, datediff(week,0, AHH.DateTime), 0)
AS Interval

FROM
Agent (nolock), Agent_Interval AHH (nolock), Media_Routing_Domain (nolock)

WHERE   Agent.SkillTargetID= AHH.SkillTargetID AND
   AHH.MRDomainID = Media_Routing_Domain.MRDomainID
       AND (AHH.DateTime >= '2013-01-27 00:00:00')
     AND (AHH.DateTime <= '2013-01-28 23:59:59')

GROUP BY

AHH.SkillTargetID,Media_Routing_Domain.EnterpriseName,
dateadd(week, datediff(week,0, AHH.DateTime), 0)
) AgentHalfHour

WHERE

AgentSkillGroup.AgentSkillID = AgentHalfHour.AgentSkillID and AgentSkillGroup.Interval = AgentHalfHour.Interval and AgentSkillGroup.Media = AgentHalfHour.Media

ORDER BY AgentSkillGroup.AgentTeamName,AgentSkillGroup.FullName,AgentSkillGroup.Media,AgentSkillGroup.AgentSkillID,AgentSkillGroup.Interval

***********

We have proposed to change denominator value by using NULLIF.

Could any one please let us know is there any latest summary report for CUIC 8.5.4 version.

Regards,

Shalid K.C

Shalid Kurunnan Chalil · ‎07-27-2013

Hi All,

it is corrected by adding the below lines in summary reports. identified by my colleague.

1. open the affected report.xml file and modify the version number for the reported defenition (otherwise you will get a mismatch error). and ensure that do not changing the version number of report.

2. add the line “SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON“ and save the report. 

3. And also add the NullIF expression before all the division operation in the SQL.        and after that we havent experienced divide by zero error in cuic.                  hope this will help you guys.  Rate this post if it help.