07-10-2014 06:02 AM
Can any one help I have 2 customers both if which have similar needs.
They have 100s of call types as they take call from many different businesses. They want to be able to summarise those call types into one line so that they can report for example on 15 business, showing 15 lines, but in reality it is 80 call types.
Has any one done this in v8 or 8,5
Also has anyone done amything similar for Skill Groups.
With regards to the Realtime, they want to be able to show this on a Wallboard as a collective.
Any ideas would be most welcome.
thanks Sarah
Solved! Go to Solution.
07-10-2014 07:52 AM
Ahh, I see. There are a couple different ways I can think of to achieve that effect:
Personally, I would go with #2 if possible, otherwise you'll potentially have to change the report definition whenever a new call type is created.
-Jameson
07-10-2014 06:54 AM
Sarah,
As long as you have access to Report Definitions (Premium or Lab license), this should be pretty straightforward to do with some basic SQL experience. If you've never done a summary in SQL before, take a look at the "Call Type Historical All Fields Daily" report as an example. W3schools.com has a good introductory SQL tutorial if you would like to learn more about SQL commands.
You'll want to make sure that Call_Type.EnterpriseName and CallTypeID are not anywhere in your SELECT statement, and removed from the GROUP BY statement as well. In fact, you should be able to remove any references to the Call_Type table. In order to still be able to filter on Call Type, you can create a Filter Field with the Data Clause of "Call_Type.CallTypeID", and give if the "Call Types" Value List. You can then add that filter field as the Key Criteria Field on the Properties tab.
-Jameson
07-10-2014 07:38 AM
Hi Jameson,
Thanks for replying I have used that before with Skill groups, however my dilema is that they will want to show summaries that look like this :
Offered Handled Abanonded
Business A (which is total 10 call types) 20 15 5
Business B (which is total 5 call types) 15 10 5
Business C (which is total 5 call types) 10 10 0
Total 45 35 10
Hope that makes sense, as doing what you have suggested will only show the Total summary.
Am I right or have I missed something.
Thanks
07-10-2014 07:52 AM
Ahh, I see. There are a couple different ways I can think of to achieve that effect:
Personally, I would go with #2 if possible, otherwise you'll potentially have to change the report definition whenever a new call type is created.
-Jameson
07-17-2014 04:09 AM
Hi Jameson
I have been trying to creat this summary of Call types for the Real Time report - and I can't get it to work, Basically I have taken the stock Call Type Real Time Report Definition and I am tryng to add in the CASE statement and whichever way I try I am getting errors
CASE
WHEN CallTypeID = 5012 AND CallTypeID = 5013 then
'CIM Call Types'
else
'Other' end as CallTypeID,
When I excute the SQL Studio managercoming back with the following
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'CallTypeID'.
Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'CallTypeID'
If I change it to EnterpriseName
CASE
WHEN EnterpriseName = CIM_Blend_Colab.CT AND EnterpriseName = CIM_Chat.CT then
'CIM Call Types'
else
'Other' end as Enterprisename,
I get the following
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "CIM_Blend_Colab.CT" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "CIM_Chat.CT" could not be bound.
If you can guve me any ideas I would be grateful
07-17-2014 05:24 AM
Sarah,
A few issues I'm seeing here...
Either one of those should work, but I would probably use the CallTypeID to future-proof against potential name changes of Call Types. You can also replace your separate "CallTypeID=" statements with a single "CTHH.CallTypeID IN (5012,5013)" statement to simplify things.
-Jameson
07-18-2014 06:33 AM
Hi Jameson, I still can't get this to work in a Call Type Real Time Report!
If i use OR
CASE
WHEN Call_Type_Real_Time.CallTypeID = 5012 OR Call_Type_Real_Time.CallTypeID = 5013 AS CIMCallType
,
Then I still get an error
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near ')'.
I am sure I am missing something, however for the life of me I can't work it out!
any other ideas would be much appreciated.
Thanks Sarah
07-18-2014 06:50 AM
Sarah,
Your CASE statement is incomplete. It needs to be in this syntax:
CASE
WHEN (expression) THEN 'value'
WHEN (another expression) THEN 'another value'
-- as many more WHEN statements as you like...
ELSE 'some default value' END AS NewColumnName
or in this syntax:
CASE (expression)
WHEN (result) THEN 'value'
WHEN (a different result) THEN 'another value'
-- as many more WHEN statements as you like...
ELSE 'some default value' END AS NewColumnName
The whitespace (newlines vs space, tab, etc) doesn't matter, I just use newlines here for clarity.
The "AS NewColumnName" is just one way of providing the column name. I often find that CUIC doesn't treat CASE statements well when AS is used for the column name... so if you build it like this it may be more reliable:
NewColumnName = CASE
WHEN (expression) THEN 'value'
WHEN (another expression) THEN 'another value'
-- as many more WHEN statements as you like...
ELSE 'some default value' END
So you might end up with something like this:
BusinessName = CASE
WHEN CTHH.CallTypeID in (5012,5013) THEN 'Business A'
WHEN CTHH.CallTypeID in (5014,5015,5016) THEN 'Business B'
WHEN CTHH.CallTypeID = 5017 THEN 'Business C'
ELSE 'Unknown Business' END,
-Jameson
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide