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..
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.
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.
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?
I have removed everything that I possibly can, and I still have the same issue - I am grouping by
Skill_Group.EnterpriseName, Skill_Group.SkillTargetID, Agent_Real_Time.DateTime
And still have however many lines per Skill Group!
Remove the Agent_Real_Time.DateTime grouping. That DateTime value is going to be different for many, if not all of your Agents.