Re: agtskg24 CUIC 8.5 Historical report is not generating more than 3000 rows

RJ5 · ‎03-17-2015

Hi,

As per cisco document, Historical report has an upper limit of 8000 rows.

In our CUIC 8.5 environment, the agtskg24 - Agent Skill Group Performance Summary Daily Report generates only 3000 rows (despite it has more data to show). It is a historical report, but some reason its not generating more than 3000 rows.

Is there any specific reason for this report to have a upper limit as 3000 rows ?

Many Thanks,

Kavya

jacparke · ‎03-17-2015

I just got rid of my 8.5 reports so I cannot check, but that report name does not sound like a stock report. Can you cut and paste the SQL from that report? (Run the report, press the "SQL" button)

Regards,

Jack Parker

RJ5 · ‎03-17-2015

Hi Jack,

Here is the SQL,

BEGIN SET ANSI_WARNINGS OFF SET ARITHABORT OFF SET ARITHIGNORE ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF

DECLARE
@RelativeDate int,
@AbsBegin varchar(30),
@AbsEnd varchar(30),
@BeginDate varchar(30),
@EndDate varchar(30)

SELECT
@AbsBegin = '2015-03-11 00:00:00',
@AbsEnd = '2015-03-11 23:59:00',
@RelativeDate =null
SELECT @BeginDate = CASE @RelativeDate
      WHEN 1 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),GETDATE(),112)))
      WHEN 2 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),DATEADD(d, -1,GETDATE()),112)))
      WHEN 3 THEN CONVERT(Varchar(30),DATEADD(dd,-6,DATEADD(wk,(DATEPART(ww, GETDATE())-0),CONVERT(char(4),DATEPART(yyyy, GETDATE())))))
      WHEN 4 THEN CONVERT(Varchar(30),DATEADD(dd,-6,DATEADD(wk,(DATEPART(ww, GETDATE())-1),CONVERT(char(4),DATEPART(yyyy, GETDATE())))))
      WHEN 5 THEN Convert(Varchar(2), DATEPART(m,GETDATE())) + '/01/' + Convert(Char(4), DATEPART(YY,GETDATE()))
      WHEN 6 THEN Convert(Varchar(2), DATEPART(m,DATEADD(mm,-1,GETDATE()))) + '/01/' + Convert(Char(4), DATEPART(YY,DATEADD(MM,-1,GETDATE())))
      WHEN 7 THEN '01/01/' + Convert(Char(4), DATEPART(yy,GETDATE()))
      WHEN 8 THEN '01/01/' + Convert(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))
      ELSE @AbsBegin
      END

SELECT @EndDate = CASE @RelativeDate
     WHEN 1 THEN CONVERT(Varchar(30), GETDATE(),112) + ' 23:59:59'
     WHEN 2 THEN CONVERT(Varchar(30), DATEADD(d, -1,GETDATE()),112) + ' 23:59:59'
     WHEN 3 THEN CONVERT(Varchar(30),DATEADD(dd,-0,DATEADD(wk,(DATEPART(ww, GETDATE())-0),CONVERT(char(4),DATEPART(yyyy, GETDATE())))) + '23:59:00')
     WHEN 4 THEN CONVERT(Varchar(30),DATEADD(dd,-0,DATEADD(wk,(DATEPART(ww, GETDATE())-1),CONVERT(char(4),DATEPART(yyyy, GETDATE())))) + '23:59:00')
     WHEN 5 THEN DATEADD(d,-1, CONVERT(Varchar(30), DATEPART(m,DATEADD(mm, 1, GETDATE()))) + '/01/' + CONVERT(Char(4), DATEPART(YY,GETDATE())) + ' 23:59:00')
     WHEN 6 THEN DATEADD(d,-1, CONVERT(Varchar(30), DATEPART(m,DATEADD(mm,0, GETDATE()))) + '/01/' + CONVERT(Char(4), DATEPART(YY,DATEADD(MM,-1,GETDATE()))) + ' 23:59:59')
     WHEN 7 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,GETDATE()))+ ' 23:59:59'
     WHEN 8 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))+ ' 23:59:59'
     ELSE @AbsEnd
     END
SELECT
AgentName = Person.LastName + ', ' + Person.FirstName,
Media = MRD.EnterpriseName,
SkillGroup = SG.EnterpriseName + ' (' + MRD.EnterpriseName + ')' ,
Date = Convert(DateTime,Convert(varchar(30),ASGHH.DateTime,112)),
AbandonCallRing = SUM(ISNULL(ASGHH.AbandonRingCallsToHalf, 0)),
   RedirectNoAnswer = SUM(ISNULL(ASGHH.RedirectNoAnsCallsToHalf, 0)),
   AbandonCallHold = SUM(ISNULL(ASGHH.AbandonHoldCallsToHalf, 0)),
SupAssistCalls = SUM(ISNULL(ASGHH.SupervAssistCallsToHalf, 0)),
SupAssistCallsTime = SUM(ISNULL(ASGHH.SupervAssistCallsTimeToHalf, 0)),
EmergencyAssist = SUM(ISNULL(ASGHH.EmergencyAssistsToHalf, 0)),
BargeInCalls = SUM(ISNULL(ASGHH.BargeInCallsToHalf, 0)),
InterceptCalls = SUM(ISNULL(ASGHH.InterceptCallsToHalf, 0)),
Handletime = SUM(ISNULL(ASGHH.HandledCallsTimeToHalf, 0)),
Handled = SUM(ISNULL(ASGHH.CallsHandledToHalf,0)),
AHT = ISNULL(SUM(ISNULL(ASGHH.HandledCallsTimeToHalf, 0)) / SUM(ISNULL(ASGHH.CallsHandledToHalf, 0)), 0),
TotalOnHoldTime = SUM(ISNULL(ASGHH.IncomingCallsOnHoldTimeToHalf, 0)) + SUM(ISNULL(ASGHH.AgentOutCallsOnHoldTimeToHalf, 0)) + SUM(ISNULL(ASGHH.InternalCallsOnHoldTimeToHalf, 0)),
IncomingHoldTime = SUM(ISNULL(ASGHH.IncomingCallsOnHoldTimeToHalf, 0)),
TotalOnHoldCalls = SUM(ISNULL(ASGHH.IncomingCallsOnHoldToHalf, 0)) + SUM(ISNULL(ASGHH.AgentOutCallsOnHoldToHalf, 0)) + SUM(ISNULL(ASGHH.InternalCallsOnHoldToHalf, 0)),
    IncomingHoldCalls = SUM(ISNULL(ASGHH.IncomingCallsOnHoldToHalf, 0)),
    IncomingAvgHoldTime = ISNULL(SUM(ISNULL(ASGHH.IncomingCallsOnHoldTimeToHalf, 0)) / SUM(ISNULL(ASGHH.IncomingCallsOnHoldToHalf, 0)), 0),

TotalMediaAvailTime = MAX(ISNULL(AHH.AvailableStateTime, 0)),
TotalMediaNotReadyTime = MAX(ISNULL(AHH.NotReadyStateTime, 0)),
TotalReportAvailTime = MAX(ISNULL(AHHFull.AvailableStateTime, 0)),
TotalReportNotReadyTime = MAX(ISNULL(AHHFull.NotReadyStateTime, 0)),

TalkTime = SUM(ISNULL(ASGHH.TalkInTimeToHalf, 0)) +
      SUM(ISNULL(ASGHH.TalkOutTimeToHalf, 0)) +
      SUM(ISNULL(ASGHH.TalkOtherTimeToHalf, 0)) +
      SUM(ISNULL(ASGHH.TalkAutoOutTimeToHalf, 0)) +
      SUM(ISNULL(ASGHH.TalkPreviewTimeToHalf, 0)) +
      SUM(ISNULL(ASGHH.TalkReserveTimeToHalf, 0)),
AvailTime   = SUM(ISNULL(ASGHH.AvailTimeToHalf,0)),
HoldTime   = SUM(ISNULL(ASGHH.HoldTimeToHalf, 0)),
NotReadyTime = SUM(ISNULL(ASGHH.NotReadyTimeToHalf,0)),
ReservedTime = SUM(ISNULL(ASGHH.ReservedStateTimeToHalf, 0)),
    WrapTime   = SUM(ISNULL(ASGHH.WorkNotReadyTimeToHalf, 0)) + SUM(ISNULL(ASGHH.WorkReadyTimeToHalf, 0)),
BusyOtherTime = SUM(ISNULL(ASGHH.BusyOtherTimeToHalf, 0))

FROM
Agent_Skill_Group_Half_Hour ASGHH,
Agent AG,
Person,
Skill_Group SG,
Media_Routing_Domain MRD,

(SELECT
AgentID = Agent_Half_Hour.SkillTargetID,
MediaID = Agent_Half_Hour.MRDomainID,
AvailableStateTime = SUM(Agent_Half_Hour.AvailTimeToHalf),
NotReadyStateTime = SUM(Agent_Half_Hour.NotReadyTimeToHalf)
FROM
Agent_Half_Hour,
Agent,
Media_Routing_Domain
WHERE
Agent_Half_Hour.DateTime >= @BeginDate AND
Agent_Half_Hour.DateTime <= @EndDate AND
Agent_Half_Hour.SkillTargetID = Agent.SkillTargetID AND
Agent_Half_Hour.MRDomainID = Media_Routing_Domain.MRDomainID
GROUP BY
Agent_Half_Hour.SkillTargetID,
Agent_Half_Hour.MRDomainID) AHH,

(SELECT
MediaID = Agent_Half_Hour.MRDomainID,
AvailableStateTime = SUM(Agent_Half_Hour.AvailTimeToHalf),
NotReadyStateTime = SUM(Agent_Half_Hour.NotReadyTimeToHalf)
FROM
Agent_Half_Hour,
Agent,
Media_Routing_Domain
WHERE
Agent_Half_Hour.DateTime >= @BeginDate AND
Agent_Half_Hour.DateTime <= @EndDate AND
Agent_Half_Hour.SkillTargetID = Agent.SkillTargetID AND
Agent_Half_Hour.MRDomainID = Media_Routing_Domain.MRDomainID
GROUP BY
Agent_Half_Hour.MRDomainID) AHHFull

WHERE
   ASGHH.SkillGroupSkillTargetID = SG.SkillTargetID
AND ASGHH.SkillTargetID = AG.SkillTargetID
AND AG.PersonID = Person.PersonID
AND SG.MRDomainID = MRD.MRDomainID
AND AHH.AgentID = AG.SkillTargetID
AND AHH.MediaID = MRD.MRDomainID
AND AHHFull.MediaID = MRD.MRDomainID
   AND ASGHH.SkillGroupSkillTargetID in ('17370','9884','5343','8095','8096','10107','10108','12034','17328','9841','8331','8332','7952','5508','9786','8351','11469','11473','11470','11474','10101','7698','7699','10102','7191','7192','9008','9005','9006','9007','11598','11797','12005','12004','12006','11932','8564','11394','8629','9486','9487','11409','11410','10342','11281','11282','10343','5335','5336','16256','8995','8996','5339','5342','16804','16803','8712','7280','7281','7278','7277','7279','11618','5344','7144','11482','11481','11497','17208','17335','10065','10581','10577','10583','10579','10582','10578','10584','10580','10066','17336','11597','9648','9643','10827','10130','10105','17326','16570','8932','8933','8928','8929','14243','9943','17441','5361','5325','5328','5366','5362','11835','11730','11599','9606','9607','12002','11374','7193','7194')
   AND ASGHH.DateTime >= @BeginDate
   AND ASGHH.DateTime <= @EndDate

GROUP BY
Person.LastName,
Person.FirstName,
ASGHH.SkillTargetID,
MRD.EnterpriseName,
SG.EnterpriseName,
SG.SkillTargetID,
Convert(DateTime,Convert(varchar(30),ASGHH.DateTime,112))

END

Thanks,

Kavya

jacparke · ‎03-17-2015

Interesting, not a report I am familiar with, but there is nothing in the SQL to limit the report to 3000 rows. If you go to the report definition, click on parameters, there is a check box there to indicate that it is a historical report - which opens the gates to the full 8000 rows, perhaps this report does not have that box checked?

Regards,

Jack Parker

RJ5 · ‎03-17-2015

Since we have a standard license I don't have access to the report definitions.

Thanks,

Kavya

jacparke · ‎03-17-2015

If you export the report, it will export to a zip file, decompress that and check the xml file.

If it has: <historical>false</historical>, that is the check box, set for a realtime report.

I don't have an answer on how to change it without getting TAC involved.

Regards,

Jack Parker

RJ5 · ‎03-17-2015

Thanks for your help. We will take this to TAC.

Many Thanks,

Kavya