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

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

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

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

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

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

Thanks,

Kavya

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

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

Many Thanks,

Kavya