Contact Center Reporting

« Back to Contact Center Reporting

CUIC - Splitting DateTime into separate fields. (Service Historical)

Combination View Flat View Tree View
Threads [ Previous | Next ]
CUIC - Splitting DateTime into separate fields. (Service Historical)
cuic cuic reporting
Answer
1/4/13 2:17 PM
Hello, I'm trying to change the Service Historical report to allow for Date and Time to be separate fields so that I can group by time (without Date) to allow us to compare time intervals performance across weeks & months. I've tried using 'CONVERT(char(8),CTHH.DateTime,108) as Time' & 'CONVERT(char(10),CTHH.DateTime,101) as Date' and although the report does validate it corrupts the report when trying to view/run them. (It corrupted all reports based on this def and wouldn't let me 'undo' and didn't like it when I restored it to it's previous state - painful - learnt my lesson!) Does anyone have any ideas on how I could get round this or what I should be trying? Any help is much appreciated! Thanks!
Flag

Re: New Message from Kieran Byng in Contact Center Reporting (CCR) - Contac
Answer
1/4/13 2:19 PM as a reply to Kieran Byng.
Postovani,

Trenutno se nalazim na godisnjem odmoru.
Za sve hitne zahteve mozete kontaktirati Lazara Obradovica na email na lobradovic@algotech.rs ili na broj 0653537699.

Hvala na razumevanju

Pozdrav

Vladimir


Dear sender,

Thank you for your email.

I am out of the office till Monday, 08.01.13 and unable to respond at this time.

I will review your message following my return on Monday, 08.01.2013.

Thank you for your understanding.
Best regards,

Vladimir
Flag

RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Answer
1/14/13 8:19 PM as a reply to Kieran Byng.
Did you get it to work?
What version of CUIC are you running?
Flag

RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Answer
1/15/13 10:03 AM as a reply to Luis Yrigoyen.
We're using CUIC version 8.5(2) - I haven't managed to get it to work as yet.
Flag

RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Answer
1/15/13 3:05 PM as a reply to Kieran Byng.
Try this instead select CONVERT(CHAR(5),CTHH.DateTime,8) as Time, this will give you the hour and minutes only.
Flag

RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Answer
1/15/13 3:45 PM as a reply to Dennis Browne.
I added that to the report definition and the query validated / field created successfully.
Then when I tried to run a report based on the new definition it gave a 'dataset error - formula failed' message.
Flag

RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Answer
1/15/13 4:34 PM as a reply to Kieran Byng.
You'll need to check on which field is causing the error.  I have this working in a report.
Flag

RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Answer
1/18/13 10:35 PM as a reply to Kieran Byng.
This worked for me (report attached):
IN YOUR QUERY:
Delete the DateTime field and add these two:
Interval=dateadd(day,  datediff(day,0,  Service_Interval.DateTime), 0),
Time = CONVERT(varchar(8),Service_Interval.DateTime,108),
 
Make sure you add them to your Group By clause:
GROUP BY Service.EnterpriseName,   S
ervice_Interval.SkillTargetID, 
Service_Interval.TimeZone, 
dateadd(day, 
datediff(day,0, 
Service_Interval.DateTime), 0),
CONVERT(varchar(8),Service_Interval.DateTime,108),  
Service_Interval.DateTime,
Service_Interval.RecoveryKey,
Service_Interval.ServiceLevelType,
Service_Interval.DbDateTime
 
Optional -- Add the first one to the order by.      
ORDER BY
Service.EnterpriseName,
Service_Interval.SkillTargetID,
dateadd(day,  datediff(day,0,  Service_Interval.DateTime), 0)
 
Create your fields:
Make sure your Interval and Time fields' format is "1/15/13" and "None" respectively.  
ON THE PROPERTIES TAB: Change the Historical Key Field to Interval (Interval)
 
That's it for the Definition now the report groupings:
 
On the View, the fields the first fields should be EnterpriseName, Time, Interval.
On the report's Grouping: Number of groups = 2 Group 1 = EnterpriseName Group 2 = Time    
Attachments:
Flag

RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Answer
1/15/13 5:00 PM as a reply to Luis Yrigoyen.
I've had success doing:
CONVERT(varchar,,108) as Row_Time
in my Report Definition.  I haven't ever had an issue with it corrupting a report or not working during validation and creating fields.
Flag

RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Answer
1/15/13 5:48 PM as a reply to Kieran Byng.
Just wanted to recommend a best practice here...
There are several different calculations that you can use to arrive at your desired result but the sql box in the report definition isn't a good place to experiment.  I recommend using a query development tool, like sql query analyzer to build your query before using CUIC to develop the report.  I keep documented copies of my sql queries so that I can always revisit the code with my own comments included and then strip them out before pasting the query into CUIC.  The key is to ensure the sql is correct before moving to CUIC.
 
Flag

RE: CUIC - Splitting DateTime into separate fields. (Service Historical)
Answer
2/13/13 11:43 AM as a reply to Luis Yrigoyen.
/* This is the report will give you good idea about how to seperate Date And Time Seperate
This Report is Call Type Historical, bydefault is half hour report. I made it as Hourly and Date and Time are seperate Columns*/.
BEGIN 
SET ARITHABORT OFF 
SET ANSI_WARNINGS OFF 
SET NOCOUNT ON 
SET QUOTED_IDENTIFIER ON 
SET ROWCOUNT 0 
 
DECLARE 
@dtStartDateTime DATETIME, 
@dtEndDateTime DATETIME, 
@sDateRange AS CHAR(1) 
 
 
SET @sDateRange = :daterange 
 
DECLARE @TodaysDate DATETIME 
SET @TodaysDate = GETDATE() 
 
IF @sDateRange = 'D' --DAILY 
BEGIN 
SET @dtStartDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(10),@TodaysDate, 101)) 
SET @dtEndDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(10),@TodaysDate + 1, 101)) 
END 
 
ELSE IF @sDateRange = 'M' -- MONTHLY 
BEGIN 
SET @dtStartDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(2), DATEPART(MM,@TodaysDate)) + '/1/' + CONVERT(VARCHAR(4), DATEPART(YY,@TodaysDate))) 
SET @dtEndDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(10),@TodaysDate + 1, 101)) 
END 
 
ELSE IF @sDateRange = 'C' -- CUSTOM DATE RANGE 
BEGIN 
SET @dtStartDateTime =  :startdate
SET @dtEndDateTime = :enddate  
END 
 
ELSE -- WEEKLY (IF User specified anyother value than these 4 than also Weekly will execute.) 
 
BEGIN 
SET @dtStartDateTime = DATEADD(D, -1 * ((CASE (DATEPART(DW, @TodaysDate) - 2) WHEN -1 THEN 6 ELSE (DATEPART(DW, @TodaysDate) - 2) END)), @TodaysDate) 
SET @dtEndDateTime = CONVERT(DATETIME, CONVERT(VARCHAR(10),@TodaysDate + 1, 101)) 
END 
 
SELECT 
CTHH.CallTypeID,
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,   
avg(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,   
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(DATETIME, CONVERT(VARCHAR(15), CTHH.DateTime , 101)) as Date,
CONVERT(VARCHAR(2), CTHH.DateTime, 108)+':00-'+CONVERT(VARCHAR(2),DATEADD(HOUR,1,(CONVERT(VARCHAR(10), CTHH.DateTime, 108))),108)+':00' AS HOUR,
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.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,
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
FROM Call_Type_Interval CTHH (nolock),   
Call_Type (nolock)    
 
WHERE ( CTHH.CallTypeID = Call_Type.CallTypeID ) AND ( CTHH.DateTime BETWEEN @dtStartDateTime AND @dtEndDateTime ) AND ( CTHH.CallTypeID IN (:Call_Type) )
 
Group By CONVERT(DATETIME, CONVERT(VARCHAR(15), CTHH.DateTime , 101)),
CONVERT(VARCHAR(2), CTHH.DateTime, 108)+':00-'+CONVERT(VARCHAR(2),DATEADD(HOUR,1,(CONVERT(VARCHAR(10), CTHH.DateTime, 108))),108)+':00',
Datepart(yy, CTHH.DateTime) ,
Datepart(mm, CTHH.DateTime) ,
Datepart(ww, CTHH.DateTime) ,
Datepart(dy, CTHH.DateTime) ,
Datepart(dw, CTHH.DateTime) ,
Call_Type.EnterpriseName,
Call_Type.EnterpriseName,
CTHH.CallTypeID,      
CTHH.BucketIntervalID,
CTHH.ServiceLevelType
ORDER BY Call_Type.EnterpriseName,CONVERT(DATETIME, CONVERT(VARCHAR(15), CTHH.DateTime , 101)),
CONVERT(VARCHAR(2), CTHH.DateTime, 108)+':00-'+CONVERT(VARCHAR(2),DATEADD(HOUR,1,(CONVERT(VARCHAR(10), CTHH.DateTime, 108))),108)+':00'
END
Flag