CUIC Report # of Calls Per Hour Over a Month

[Brett2]

Greetings

I have been looking for this without success so if it is already somewhere I apologize. I am trying to generate a report that will show me how many calls my queue took during each hour of the day. That part is easy, what I can't figure out is how can I take the number of calls and total them throughout the month.

 IE. how many calls did we take at 8 AM each day totaled for the month?

Having this would allow us to change staffing during different hours in the day.

Thank you in advance.

[geoff]

Can you write Report Definitions? If so, create a new report with syntax similar to the following. You need to deal with the CallTypeID coming from a Value List and the start and end dates coming from a date picker - but that's standard stuff for any Report Def'n writer. See the Cisco docs.

SELECT CASE DATEPART(hh, cti.DateTime)
	WHEN 0 THEN 'midnight to 1:00AM'
	WHEN 1 THEN '1:00AM to 2:00AM'
	WHEN 2 THEN '2:00AM to 3:00AM'
	WHEN 3 THEN '3:00AM to 4:00AM'
	WHEN 4 THEN '4:00AM to 5:00AM'
	WHEN 5 THEN '5:00AM to 6:00AM'
	WHEN 6 THEN '6:00AM to 7:00AM'
	WHEN 7 THEN '7:00AM to 8:00AM'
	WHEN 8 THEN '8:00AM to 9:00AM'
	WHEN 9 THEN '9:00AM to 10:00AM'
	WHEN 10 THEN '10:00AM to 11:00AM'
	WHEN 11 THEN '11:00AM to 12:00PM'
	WHEN 12 THEN '12:00PM to 3:00PM'
	WHEN 13 THEN '1:00PM to 3:00PM'
	WHEN 14 THEN '2:00PM to 3:00PM'
	WHEN 15 THEN '3:00PM to 4:00PM'
	WHEN 16 THEN '4:00PM to 5:00PM'
	WHEN 17 THEN '5:00PM to 6:00PM'
	WHEN 18 THEN '6:00PM to 7:00PM'
	WHEN 19 THEN '7:00PM to 8:00PM'
	WHEN 20 THEN '8:00PM to 8:00PM'
	WHEN 21 THEN '9:00PM to 8:00PM'
	WHEN 22 THEN '10:00PM to 8:00PM'
	WHEN 23 THEN '11:00PM to midnight'
	ELSE 'Undefined'
	END AS 'Hour Period'
	,ct.EnterpriseName AS 'Call Type'
	,SUM(cti.CallsOffered) AS 'Calls Offered'
FROM Call_Type_Interval AS cti 
INNER JOIN Call_Type AS ct ON (ct.CallTypeID=cti.CallTypeID)
WHERE cti.CallTypeID=5002
AND cti.DateTime >= '2018-01-01' AND cti.DateTime < '2018-02-01'
GROUP BY DATEPART(hh, cti.DateTime), ct.EnterpriseName
ORDER BY DATEPART(hh, cti.DateTime)

The results look like this for a small customer of mine.

 

Regards, Geoff

[Kristopher Tan]

 To clarify what geoff is referring to, if you have UCCX CUIC 10.x, you'll need to get Cisco's not-for-resale UCCE CUIC editor because the UCCX version does not allow you to create custom report definitions. It will need to be installed on a separate server and when you create your custom report, you'll need to export it from UCCE CUIC and import it into UCCX CUIC. It's annoying and sometimes painful but it can be done.

[geoff]

---

@Kristopher Tan wrote:

 To clarify what geoff is referring to, if you have UCCX CUIC 10.x, you'll need to get Cisco's not-for-resale UCCE CUIC editor because the UCCX version does not allow you to create custom report definitions. It will need to be installed on a separate server and when you create your custom report, you'll need to export it from UCCE CUIC and import it into UCCX CUIC. It's annoying and sometimes painful but it can be done.

---

Thanks for clarifying. I only play with UCCE and was not aware of the Express restrictions. 

Regards,

Geoff

[Brett2]

Thank you, we are running 11.6 and from what I can see the way to do it is to import the SQL with an XML file.

[geoff]

Can you create a Report Definition? Ever done that? If so, the following SQL will do what you need. of course the CallTypeID will come from a Value List and the start date-time and end date-time will come from the date picker - but that is standard Cisco stuff. Any Report Def'n writer will know how to do that. See the Cisco documents.

SELECT CASE DATEPART(hh, cti.DateTime)
	WHEN 0 THEN 'midnight to 1:00AM'
	WHEN 1 THEN '1:00AM to 2:00AM'
	WHEN 2 THEN '2:00AM to 3:00AM'
	WHEN 3 THEN '3:00AM to 4:00AM'
	WHEN 4 THEN '4:00AM to 5:00AM'
	WHEN 5 THEN '5:00AM to 6:00AM'
	WHEN 6 THEN '6:00AM to 7:00AM'
	WHEN 7 THEN '7:00AM to 8:00AM'
	WHEN 8 THEN '8:00AM to 9:00AM'
	WHEN 9 THEN '9:00AM to 10:00AM'
	WHEN 10 THEN '10:00AM to 11:00AM'
	WHEN 11 THEN '11:00AM to 12:00PM'
	WHEN 12 THEN '12:00PM to 3:00PM'
	WHEN 13 THEN '1:00PM to 3:00PM'
	WHEN 14 THEN '2:00PM to 3:00PM'
	WHEN 15 THEN '3:00PM to 4:00PM'
	WHEN 16 THEN '4:00PM to 5:00PM'
	WHEN 17 THEN '5:00PM to 6:00PM'
	WHEN 18 THEN '6:00PM to 7:00PM'
	WHEN 19 THEN '7:00PM to 8:00PM'
	WHEN 20 THEN '8:00PM to 8:00PM'
	WHEN 21 THEN '9:00PM to 8:00PM'
	WHEN 22 THEN '10:00PM to 8:00PM'
	WHEN 23 THEN '11:00PM to midnight'
	ELSE 'Undefined'
	END AS 'Hour Period'
	,ct.EnterpriseName AS 'Call Type'
	,SUM(cti.CallsOffered) AS 'Calls Offered'
FROM Call_Type_Interval AS cti 
INNER JOIN Call_Type AS ct ON (ct.CallTypeID=cti.CallTypeID)
WHERE cti.CallTypeID=5002
AND cti.DateTime >= '2018-01-01' AND cti.DateTime < '2018-02-01'
GROUP BY DATEPART(hh, cti.DateTime), ct.EnterpriseName
ORDER BY DATEPART(hh, cti.DateTime)

From a small customer of mine for January this year. 

Regards,

Geoff

[Brett2]

Geoff

Thank you, I have never done that but at least this gives me some more to work with. I will try this and hopefully it works.

Thanks Again!