Showing Reason Codes as Columns in real Time

[sarbarnes]

My customer wants to show columns of Agent Reason Codes by Skill group in real time.

I am using the Agent Skill Group Real Time report and I have been using the the following code given to me by James for my WrapupData report for the Reason Code report.

Therefore the code I have been using looks like this,

SUM (CASE Agent_Skill_Group_Real_Time.ReasonCode WHEN 'Lunch' THEN 1 ELSE 0 END) AS Lunch,

SUM (CASE Agent_Skill_Group_Real_Time.ReasonCode WHEN ' ' THEN 1 ELSE 0 END) AS None,

obviously haven't put all of them in as I want to try out one to see if it works.

I am getting this eror code  Invalid Query. Check the syntax. Conversion failed when converting the varchar value 'Lunch' to data type int.

this is in V8 of CUIC.

However I have sucessully created exactly the same report in CUIC v9 without errors.

Also if I just leave in the None line it creates the fields..

Any ideas?

[Jameson Gagnepain]

Sarah,

You're on the right track, however if you look at the Agent_Skill_Group_Real_Time table definition in the UCCE Database Schema, you will see that ReasonCode is an integer field, not a string. You will need to use the integer value of your Lunch reason code, not the string 'Lunch' in your CASE statement. So if 'Lunch' is ReasonCode 1, you would do:

SUM (CASE Agent_Skill_Group_Real_Time.ReasonCode WHEN 1 THEN 1 ELSE 0 END) AS Lunch,

SUM (CASE ISNULL(Agent_Skill_Group_Real_Time.ReasonCode,-999) WHEN -999 THEN 1 ELSE 0 END) AS None,

Alternatively, you can do a join with the Reason_Code table, and use ReasonText in your CASE statements instead of ReasonCode.

-Jameson

[sarbarnes]

Thanks James, When I went back to it I wondered whether I should use numbers! Anyway it seems to be work on some numbers and not on others - but I will look into that.

The other question I have is I have set the Grouping by Skill group - however I am still seeing 10's of lines per skill group as opposed t just on line.

I am using teh SUM case and I also have them set to sun in teh Edit Format. is there anyway I can get just one line or do I need to sum it all again?

Skill Name    Break   Admin   Lunch   Meeting

Skill grp 1      2          3          4             0

Skill grp 2      1           1          2             1

                     3          4          6              1

This is v8 so it may not be possible.

[Jameson Gagnepain]

Sarah,

Can you post your SQL query? I can definitely decipher the issue if I see that.

If I had to guess, though, I would say you may have forgotten to take a field out of your SELECT and GROUP BY statements... perhaps you left the AgentSkillTargetID and/or EnterpriseName in the query?

-Jameson

[sarbarnes]

I have removed everything that I possibly can, and I still have the same issue - I am grouping by

Group BY

Skill_Group.EnterpriseName, Skill_Group.SkillTargetID, Agent_Real_Time.DateTime

And still have however many lines per Skill Group!

[Jameson Gagnepain]

Sarah,

Remove the Agent_Real_Time.DateTime grouping. That DateTime value is going to be different for many, if not all of your Agents.

-Jameson