Hello,
We are using a report definition called RD Agent Audit Detail in CUIC which utilizes our UCCE Realtime Data source. This report pulls all specific events for each user and is built using an Anonymous Block. We would like to take this report definition and add the option to Filter by the “XferTo” field.
I have tried adding to the where selection to AND XferTo = (:XferTo). My understanding is that this should then create a parameter that will require user input to select string values to filter for.
After I add this value in CUIC give an error indicating “Create the parameters or correct the query syntax and recreate the parameters. Invalid column name 'XferTo'.”. When I look at the columns XferTo is indeed listed as a column so at this point im not quite sure what I am doing wrong.
Below is the Anonymous Block. Before adding AND XferTo = (:XferTo) this report definition works without issue.
SELECT
TCD.DateTime AS DateTimeStamp,
TCDA.EnterpriseName AS AgentName,
AgentActivity = CASE
WHEN PeripheralCallType = '1' THEN 'ACD In'
WHEN PeripheralCallType = '2' THEN 'ACD In'
WHEN PeripheralCallType = '4' THEN 'Transfer In'
WHEN PeripheralCallType = '6' THEN 'Other In'
WHEN PeripheralCallType = '9' THEN 'Agent Outside Call'
WHEN PeripheralCallType = '10' THEN 'Agent Inside Call'
WHEN PeripheralCallType = '11' THEN 'Offered'
WHEN PeripheralCallType = '12' THEN 'Consult'
WHEN PeripheralCallType = '13' THEN 'Consult Offered'
WHEN PeripheralCallType = '14' THEN 'Conference'
WHEN PeripheralCallType = '15' THEN 'Conference'
WHEN PeripheralCallType = '19' THEN 'Supervisor Assist'
WHEN PeripheralCallType = '21' THEN 'Supervisor Monitor'
WHEN PeripheralCallType = '23' THEN 'Supervisor Barge-In'
WHEN PeripheralCallType = '38' THEN 'Non-ACD Call'
WHEN PeripheralCallType = '39' THEN 'Agent Greeting'
ELSE 'Unknown'
END,
ActivityDetails = CASE
WHEN CallDisposition IN (1,2,3,4,5,6) THEN 'Abandoned'
WHEN CallDisposition IN (7) THEN 'Short Call'
WHEN CallDisposition IN (8) THEN 'Busy'
WHEN CallDisposition IN (10) THEN 'Disconnect Drop No Ans'
WHEN CallDisposition IN (13) THEN 'Normal Handled'
WHEN CallDisposition IN (14) THEN 'OB/VoiceMail'
WHEN CallDisposition IN (19) THEN 'RONA'
WHEN CallDisposition IN (28) THEN 'Cold Transfer'
WHEN CallDisposition IN (29) THEN 'Warm Transfer'
WHEN CallDisposition IN (30) THEN 'Conferenced'
ELSE 'Unknown'
END,
TCD.Duration AS ActivityDuration,
CallDirection = CASE
WHEN RouterCallKeyDay = '0' THEN 'Outbound'
ELSE 'Inbound'
END,
SG.EnterpriseName AS SkillGroupName,
XferTo = CASE
WHEN TCD.CallDisposition IN (28,29) THEN
(SELECT DISTINCT(TCDXFER.Variable1) FROM Termination_Call_Detail TCDXFER WHERE TCDXFER.DateTime >= :startdate AND TCDXFER.DateTime <= :enddate AND TCD.ICRCallKey=TCDXFER.ICRCallKeyParent AND TCDXFER.DNIS<>'PlayAgentGreeting')
ELSE ''
END,
TCD.RouterCallKey AS TCDRouterCallKey,
TCD.RouterCallKeyDay AS TCDRouterCallKeyDay,
TCD.RouterCallKeySequenceNumber AS Sequence,
--CAST(TCD.SkillGroupSkillTargetID AS varchar(100)) AS TCDSkillGroupID,
--CAST(TCD.AgentSkillTargetID AS varchar(100)) AS TCDAgentSkillID,
--TCD.PeripheralCallType AS PCallType,
TCD.DigitsDialed AS DigitsDialed,
TCD.CallDisposition AS CallDisposition,
TCD.NetworkTime AS NetworkTime,
TCD.RingTime AS RingTime,
TCD.DelayTime AS DelayTime,
TCD.TimeToAband AS TimeToAband,
TCD.HoldTime AS HoldTime,
TCD.TalkTime AS TalkTime,
TCD.WorkTime AS WorkTime,
TCD.LocalQTime AS LocalQTime,
TCD.NetQTime as NetQTime,
TCD.DNIS AS FROMDNIS,
TCD.InstrumentPortNumber as AgentExtentsion,
TCD.AgentPeripheralNumber AS AgentPeripheralID,
TCD.ANI AS ANI
-- P.FirstName,
-- P.LastName,
FROM Termination_Call_Detail TCD
JOIN Agent TCDA On TCD.AgentSkillTargetID = TCDA.SkillTargetID
-- JOIN Person P On A.PersonID = P.PersonID
JOIN Skill_Group SG ON TCD.SkillGroupSkillTargetID = SG.SkillTargetID
WHERE TCD.DateTime >= :startdate AND TCD.DateTime <= :enddate
AND TCD.AgentSkillTargetID IN (:agents)
UNION ALL
SELECT
AED.DateTime AS DateTimeStamp,
A.EnterpriseName AS AgentName,
AgentActivity = CASE
WHEN Event = '1' THEN 'Login'
WHEN Event = '2' THEN 'Logout'
WHEN Event = '3' THEN 'Not Ready'
END,
ActivityDetails = CASE
WHEN R.ReasonText IS NULL THEN ''
ELSE R.ReasonText
END,
--R.ReasonText AS ActivityDetails,
AED.Duration AS ActivityDuration,
CallDirection = '',
SkillGroupName = '',
XferTo='',
TCDRouterCallKey = '',
TCDRouterCallKeyDay = '',
Sequence = '',
--CAST(TCD.SkillGroupSkillTargetID AS varchar(100)) AS TCDSkillGroupID,
--CAST(TCD.AgentSkillTargetID AS varchar(100)) AS TCDAgentSkillID,
--TCD.PeripheralCallType AS PCallType,
DigitsDialed = '',
CallDisposition = '',
NetworkTime = '',
RingTime = '',
DelayTime = '',
TimeToAband = '',
HoldTime = '',
TalkTime = '',
WorkTime = '',
LocalQTime = '',
NetQTime = '',
FROMDNIS = '',
AgentExtentsion = '',
AgentPeripheralID = '',
ANI = ''
-- P.FirstName,
-- P.LastName,
FROM Agent_Event_Detail AED
JOIN Agent A ON AED.SkillTargetID = A.SkillTargetID
LEFT OUTER JOIN Reason_Code R ON AED.ReasonCode = R.ReasonCode
WHERE AED.SkillTargetID IN (:agents) AND AED.DateTime >= :startdate AND AED.DateTime <= :enddate AND XferTo = (:XferTo)
ORDER BY DateTime
Any help would be appreciated.
Thank you,
