Summarising Many Call Types into 1 line summary in Real Time & Historical reports

[sarbarnes]

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

[Jameson Gagnepain]

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

[sarbarnes]

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

[Jameson Gagnepain · Accepted solution]

Ahh, I see. There are a couple different ways I can think of to achieve that effect:

1. Build a SQL query with the CallTypeIDs hard-coded into the WHERE statement. In the SELECT statement, use a CASE on the CallTypeID to output the "Business A", "Business B", "Business C" text, and GROUP BY that same CASE statement.
2. If the different businesses have a consistent naming scheme to their Call Types, you can SELECT and GROUP BY a substring of the Call_Type.EnterpriseName, and proceed as I mentioned in my previous post to provide filter criteria.

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

[sarbarnes]

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

[Jameson Gagnepain]

Sarah,

A few issues I'm seeing here...

• You should be using "OR" in both cases, not "AND". This isn't causing your errors, but when your errors are fixed you will end up with zero results because it would be impossible for the CallTypeID to be both values at once.
• Your first error is due to not specifying which CallTypeID to compare against. You need to explicitly state what table you want to use, for example Call_Type_Half_Hour.CallTypeID or CTHH.CallTypeID
• The second error is due to CIM_Blend_Colab.CT looking like a column name within a table, and not a string. Try 'CIM_Blend_Colab.CT' (with single quotes).

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

[sarbarnes]

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

[Jameson Gagnepain]

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