CMS CUSTOM REPORT 3

[NatThomas]

Hi,

I'm looking to create a custom report on CMSv 12 that adds up interval totals and averages over a weekly period. When I run an interval report for the week it comes up like this:

27/03/06 0800-0830
28/03/06 0800-0830
29/03/06 0800-0830

Id like to create one that can add up the days and create a total for that interval for the three days.

Hope that makes sense.

Can anyone help? Can this be done?

Thanks,
Natalie

 

[smurfyIAG]

Hi Natalie. Yes it can be done. You need to change the interval from an individual field to a summary function (e.g.(MAX) ) so that it is not included in the automatic GROUP BY clause in the report designer SQL query.

 

[NatThomas]

Hi,

I really really appreciate your help. Ive logged this query out with our external third line support company, who we pay a fortune for, and they have said they dont know how to do it!

Thank you so much.
natalie

 

[smurfyIAG]

OK, I'll see if I can step through it here - I don't have anything specific written up. My description above was actually slightly incorrect but the theory is the same

You have a report at the moment that may look like this:

ROW_DATE | STARTTIME - STARTTIME+INTERVAL | CALLSOFFERED | ACDCALLS | etc 
27/03/06 |    0800   -    0830            |      10      |  7 
28/03/06 |    0800   -    0830            |      15      |  13
29/03/06 |    0800   -    0830            |      05      |  5

When you enter design mode and edit the queries, your "Data" query items will look something like
hsplit.ROW_DATE
hsplit.STARTTIME
'-'
hsplit.STARTTIME+hsplit.INTERVAL
hsplit.CALLSOFFERED
hsplit.ACDCALLS
etc...

You need to edit or delete each of these items so you end up with
hsplit.STARTTIME
sum(hsplit.CALLSOFFERED)
sum(hsplit.ACDCALLS)
etc...
You will get warning messages about deleting columns that are in your table - you can say OK to these.

Then click on the "Where" button.
Your "where" clause should say something like:
ACD=$acd and SPLIT = [Split/Skill:] and ROW_DATE = [Dates:] and STARTTIME = [Times:]
You need to add the following at the end ...
ACD=$acd and SPLIT = [Split/Skill:] and ROW_DATE = [Dates:] and STARTTIME = [Times:] GROUP BY STARTTIME

If your report has a summary line, there will probably be a "Totals" query which you will need to edit accordingly - should already have sums in there but you'll need to delete the ROW_DATE and STARTTIME+INTERVAL columns to match the data items.

OK your way out of the query editor and make sure the table formats haven't been messed up by removing columns.

Run the report.

Note - the above was written as I stepped through this using CMS v11 however I have read the v12 report designer guide (while evaluating need to upgrade) and am pretty confident everything should be as described.

Sean Murphy
Call Centre Analyst
IAG New Zealand

 

[NatThomas]

Hi,

Thanks for doing this. Its not working though :0)

Im getting this syntax error as soon as I try to change the queries to SUM.

A syntax error has occurred.

data=select hsplit.STARTTIME,(sum(hsplit.ABNTIME/sum(hsplit.ABNCALLS) **ERROR** ),hsplit.ACDCALLS,(hsplit.ACDTIME/hsplit.ACDCALLS),(hsplit.ACWTIME/hsplit.
ACDCALLS),hsplit.ABNCALLS,hsplit.MAXOCWTIME,hsplit.INFLOWCALLS,hsplit.
OUTFLOWCALLS,((hsplit.ACWOUTCALLS+hsplit.AUXOUTCALLS)),((hsplit.ACWOUTTIME
+hsplit.AUXOUTTIME)/(hsplit.ACWOUTCALLS+hsplit.AUXOUTCALLS)),hsplit.
DEQUECALLS,hsplit.DEQUETIME / hsplit.DEQUECALLS,(100*((hsplit.I_ACDTIME+hsplit.I_ACWTIME)/hsplit.
I_STAFFTIME)),(100*(hsplit.ACDCALLS/hsplit.CALLSOFFERED)),(hsplit.
I_STAFFTIME/(hsplit.INTRVL*60)),((60*hsplit.INTRVL*hsplit.ACDCALLS)/hsplit
.I_STAFFTIME)from hsplit where ((ROW_DATE>=38806 and ROW_DATE<=38812)) and ((STARTTIME>=0800 and STARTTIME<=2000)) and SPLIT=440 and ACD=4 group by STARTTIME, hsplit.ACDCALLS, hsplit.AVG_ACD_TALK_TIME, hsplit.AVG_ACW_TIME, hsplit.AVG_ACD_TALK_TIME, hsplit.AVG_ACW_TIME, hsplit.ABNCALLS, hsplit.MAXOCWTIME, hsplit.EXT_CALL_OUT, hsplit.AVG_TALK_TIME_OUT, hsplit.INFLOWCALLS, hsplit.OUTFLOWCALLS, hsplit.PERCENT_ACD_TIME, hsplit.PERCENT_CALL_ANS, hsplit.AVG_POS_STAFF, hsplit.CALLS_PER_POS


I tried changing numerous ones to sum and it gives errors on them all. i also tried putting _SUM at the end of the query.

Once I press the Where key this is what i have on totals and data

ACD=$acd and SPLIT=(SPLIT / SKILL and ROW_DATE=(DATES and STARTIME=(TIMES GROUP BY STARTTIME

I also get a "the column must be in GROUP BY List" error

Did it total the colums on your report for the week then?
If it worked on yours its got to work hasnt it?

Thanks so much again... :0)

natalie

 

[smurfyIAG]

Okay, now that I have all your fields I'll write it up for you in full. The problem will be in the calculations e.g. (sum(hsplit.ABNTIME/sum(hsplit.ABNCALLS) **ERROR** )
should be
(sum(hsplit.ABNTIME)/sum(hsplit.ABNCALLS))
notice the additonal ) ?
i.e. the sum of all ABNTIME for the interval in the date range divided by the sum of all the ABNCALLS for the interval in the date range.

Shouldn't take too long...

Sean Murphy
Call Centre Analyst
IAG New Zealand

 

[NatThomas]

Thank you very very much. Your marvelous.

Natalie

 

[smurfyIAG]

Some of the full calculations as shown in the SQL error may not be showing up in your data fields - they will be the CMS standard calculation names
e.g.
you won't see: (sum(hsplit.ABNTIME)/sum(hsplit.ABNCALLS))
it will say hsplit.AVG_ABANDON_TIME which you probably tried to change to sum(hsplit.AVG_ABANDON_TIME) which resulted in the error (it's not smart enough to sum each part of the calculation by itself.

You have two choices, manually over-ride the calculation name with the actual formula as I've typed out below in blue (the bracketing is vital - copy and paste should work ) OR replace them with the appropriate sum calculation where available from the CMS dictionary as typed out in red...
Either way should work.

Using FULL calculations
hsplit.STARTTIME
(sum(hsplit.ABNTIME)/sum(hsplit.ABNCALLS))
sum(hsplit.ACDCALLS)
(sum(hsplit.ACDTIME)/sum(hsplit.ACDCALLS))
(sum(hsplit.ACWTIME)/sum(hsplit.ACDCALLS))
sum(hsplit.ABNCALLS)
max(hsplit.MAXOCWTIME)
sum(hsplit.INFLOWCALLS)
sum(hsplit.OUTFLOWCALLS)
(sum(hsplit.ACWOUTCALLS)+sum(hsplit.AUXOUTCALLS))
((sum(hsplit.ACWOUTTIME)+sum(hsplit.AUXOUTTIME))/(sum(hsplit.ACWOUTCALLS)+sum(hsplit.AUXOUTCALLS)))
sum(hsplit.DEQUECALLS)
sum(hsplit.DEQUETIME) / sum(hsplit.DEQUECALLS)
(100*((sum(hsplit.I_ACDTIME)+sum(hsplit.I_ACWTIME))/sum(hsplit.I_STAFFTIME)))
(100*(sum(hsplit.ACDCALLS)/sum(hsplit.CALLSOFFERED)))
(sum(hsplit.I_STAFFTIME)/(sum(hsplit.INTRVL)*60))
((60*sum(hsplit.INTRVL)*sum(hsplit.ACDCALLS))/sum(hsplit.I_STAFFTIME))
or
SUM calculations (Note: Some do not have "standard" sum calculations)
hsplit.STARTTIME
AVG_ABANDON_TIME_SUM
sum(hsplit.ACDCALLS)
AVG_ACD_TALK_TIM_SUM
AVG_ACW_TIME_SUM
sum(hsplit.ABNCALLS)
max(hsplit.MAXOCWTIME)
sum(hsplit.INFLOWCALLS)
sum(hsplit.OUTFLOWCALLS)
(sum(hsplit.ACWOUTCALLS)+sum(hsplit.AUXOUTCALLS))
AVG_TALK_TIM_OUT_SUM
sum(hsplit.DEQUECALLS)
sum(hsplit.DEQUETIME) / sum(hsplit.DEQUECALLS)
PERCENT_ACD_TIME_SUM
PERCENT_CALL_ANS_SUM
AVG_POS_STAFF_SUM
CALLS_PER_POS_SUM

The Where clause looked fine.

Sean Murphy
Call Centre Analyst
IAG New Zealand

 

[NatThomas]

Thank you very much Sean for you effort with this for me.

I'll have a go at it now and hopefully I'll have a very happy user at the end of it all.

Thank you!!!!!

Natalie

 

[smurfyIAG]

It's a path I went down shortly after we moved from a Nortel Symposium to CMS a couple of years ago - not to sum across intervals as you are doing but to sum across multiple skills. It seems all the standard reports wanted to give us results for one split at a time but most of out sites answered mutiple skills and wanted an overall result.

I had similar vendor issues with getting summed results on our callboards. Suffice it to say "impossible" is not an answer I accept easily

Sean Murphy
Call Centre Analyst
IAG New Zealand

 

[GarethRoynon]

Hi,

Sean, if you are about can I ask you a question about a report im trying to create. I remembered you were a bit of an expert when it came to my last report :0) so thought maybe I could hassle you for some more advice???

Its just im trying to create a historical intrahour report for Call work codes. Its a simple one with the 15 min interval down the left hand side and the call work code used across the top with a total for the period in the corresponding box.

Can it be done??? Its driving me crazy and I hate admitting defeat..:0)

Thanks in advance for any advice you can offer me.

Natalie

 

[GarethRoynon]

PS, Im using my collegues login as mine isnt working for some reason... Its Nat, but im using GARETHS login

Natalie

 

[smurfyIAG]

Hi Natalie, see my reply to you at avayausers.com (I check in there more than here).
I thought you were wanting this by Agent Group? The way you want it should be easy enough but for the reasons I mentioned (lack of in-depth data by agent/skill/group), I don't use any CWCs to be able to test a report. I'll see what I can do with the report designer though and get back to you.

Sean Murphy
Call Centre Analyst
IAG New Zealand

 

[GarethRoynon]

Hi Sean,

If I couldnt have it by agent group as the CWC werent related to Skill/Agent etc then just by using CWC as the input would have been fine.

So the input would have been CWC and then just totaled by how many CWCs per interval (15 mins)

Do you think it could be done this way?