Transfer reports - Who Transfered to whom

[sarbarnes]

Can anyone help

I am trying to create a report which shows who has transfered a call to another agent.

Having run a basic Agent Skill group Historical report it shows an agent A handling 2 calls both of which were Transfered in. If I wanted to create a report which would be the best table that would tell me who transfered those calls to Agent A?

In looking at the definitin of Trans In it says

Number of calls transferred into the skill group during the half-hourinterval. - how do you deternine where the call was transfered in from?

As always any help is much appreciated.

[Jameson Gagnepain · Accepted solution]

Sarah,

In order to track who initiated the transfer, you would need to use the Termination_Call_Detail table. Compiling multiple rows together from the same call together in a summarized report is a little tricky, as things will appear differently depending on the transfer type (Announced Transfer, Conference, or Blind Transfer). Not impossible, just tricky.

I've built reports similar to this before, which take a starting Call Type and spit out the various destination digits transferred to, destination call types, and type of transfer, with summarized counts of each. I had to join Termination_Call_Detail back on itself 7 times to get all the various types of transfer call entries together, and did some creative use of CASE statements.

Here's the SQL from my report, perhaps you can use it as a starting point if you can decipher how I put it together:

select TOP 4001

    b.EnterpriseName as CallType,

    a.CallTypeID,

    case

    When a.CallDisposition = 28 then 'Blind Transfer'

    When a.CallDisposition = 29 then 'Announced Transfer'

    when a.CallDisposition = 30 then 'Conferenced'

    else 'Other' end as CallDispositionLabel,

    a.CallDisposition,

    count(a.ICRCallKey) as TotalCalls,

    case

    when c.DigitsDialed is not null then c.DigitsDialed

    when j.DigitsDialed is not null then j.DigitsDialed

    when e.DigitsDialed is not null then e.DigitsDialed

    when g.DNIS is not null then g.DNIS

    else 'unknown' end as DestinationDigits,

    case

    when c.DigitsDialed is not null then d.EnterpriseName

    when j.DigitsDialed is not null then k.EnterpriseName

    when e.DigitsDialed is not null then f.EnterpriseName

    when g.DNIS is not null then i.EnterpriseName

    else m.EnterpriseName end as DestinationCallType,

    case

    when c.DigitsDialed is not null then c.CallTypeID

    when j.DigitsDialed is not null then j.CallTypeID

    when e.DigitsDialed is not null then e.CallTypeID

    when g.DNIS is not null then h.CallTypeID

    else l.CallTypeID end as DestinationCallTypeID

from Termination_Call_Detail a

left join Call_Type b on a.CallTypeID = b.CallTypeID

left join Termination_Call_Detail c on a.RouterCallKeyDay = c.RouterCallKeyDay and a.RouterCallKey = c.RouterCallKey and a.ICRCallKey <> c.ICRCallKey and ((c.DNIS <> 0111118888 and c.DNIS <> 0111119999) or c.DNIS is null) and c.ICRCallKeyParent is null and c.DigitsDialed is not null and a.DNIS = c.ANI and c.CallDisposition <> 19

left join Call_Type d on c.CallTypeID = d.CallTypeID

left join Termination_Call_Detail j on c.DigitsDialed is null and a.RouterCallKeyDay = j.RouterCallKeyDay and a.RouterCallKey = j.RouterCallKey and a.ICRCallKey <> j.ICRCallKey and j.ICRCallKeyParent = a.ICRCallKey and a.DNIS = j.DNIS and a.ANI = j.ANI and j.DigitsDialed is not null and j.DigitsDialed <> a.DigitsDialed

left join Call_Type k on j.CallTypeID = k.CallTypeID

left join Termination_Call_Detail e on c.DigitsDialed is null and j.DigitsDialed is null and a.RouterCallKeyDay = e.RouterCallKeyDay and a.RouterCallKey = e.RouterCallKey and a.ICRCallKey <> e.ICRCallKey and e.DNIS is null and e.ICRCallKeyParent = a.ICRCallKey and a.DNIS = e.ANI and e.DigitsDialed is not null

left join Call_Type f on e.CallTypeID = f.CallTypeID

left join Termination_Call_Detail g on c.DigitsDialed is null and j.DigitsDialed is null and e.DigitsDialed is null and a.RouterCallKeyDay = g.RouterCallKeyDay and a.RouterCallKey = g.RouterCallKey and a.ICRCallKey <> g.ICRCallKey and g.ICRCallKeyParent = a.ICRCallKey and (g.ICRCallKey <> a.ICRCallKeyChild or a.ICRCallKeyChild is null) and a.DNIS = g.ANI

left join Termination_Call_Detail h on g.RouterCallKey is not null and a.RouterCallKeyDay = h.RouterCallKeyDay and a.RouterCallKey = h.RouterCallKey and a.ICRCallKey <> h.ICRCallKey and h.DNIS = 0111118888 and h.ANI = a.DNIS

left join Call_Type i on h.CallTypeID = i.CallTypeID

left join Termination_Call_Detail l on c.DigitsDialed is null and e.DigitsDialed is null and j.DigitsDialed is null and g.DNIS is null and a.RouterCallKeyDay = l.RouterCallKeyDay and a.RouterCallKey = l.RouterCallKey and a.ICRCallKey <> l.ICRCallKey and l.DNIS = 0111118888 and l.ANI = a.DNIS

left join Call_Type m on l.CallTypeID = m.CallTypeID

where (a.CallDisposition = 28 or a.CallDisposition = 29 or a.CallDisposition = 30) and a.RouterCallKeyDay <> 0

group by b.EnterpriseName, a.CallTypeID, a.CallDisposition, case

    when c.DigitsDialed is not null then c.DigitsDialed

    when j.DigitsDialed is not null then j.DigitsDialed

    when e.DigitsDialed is not null then e.DigitsDialed

    when g.DNIS is not null then g.DNIS

    else 'unknown' end,

    case

    when c.DigitsDialed is not null then d.EnterpriseName

    when j.DigitsDialed is not null then k.EnterpriseName

    when e.DigitsDialed is not null then f.EnterpriseName

    when g.DNIS is not null then i.EnterpriseName

    else m.EnterpriseName end,

    case

    when c.DigitsDialed is not null then c.CallTypeID

    when j.DigitsDialed is not null then j.CallTypeID

    when e.DigitsDialed is not null then e.CallTypeID

    when g.DNIS is not null then h.CallTypeID

    else l.CallTypeID end

order by b.EnterpriseName, a.CallTypeID, a.CallDisposition

When building the Report Definition, I used b.CallTypeID as the key criteria, and created a Filter Field of a.DateTime for date-time filtering.

The references to 0111119999 and 0111118888 will need to be changed to match your Send to VRU labels for CVP and CUCM (in my case, 0111119999 is for CVP call origination, and 0111118888 is for CUCM call origination).

If you wanted to see originating and destination Agents, you could take out all the Call Type references and replace them with joins and references to the Agent and Person tables to get EnterpriseName and Name.

-Jameson

[sarbarnes]

Thanks Jameson,

I didn't think it would be easy, however looking at your code, it is much more difficult than 1st thought...

I have some investigating to do...

Thought I had cracked it by matching those calls that were transfered aout in an agent report and matching against a TCD report I had created, with an advnace filter set against the Call Disposition - just in case the customer wanted to track other Call dispositions.

Interestingly enough i could match up a Blind transfer with an Announced transfer - it would appear that the ANI was the same on both these calls however it didn't match all of the calls, unfortunately they have a very convoluted CAll type Structure.

What would be great was if Cisco could create a transfered from Agent A and to Agent B field in the TCD table or any of the tables!

Is there any way in matching the calls on the Router call key, or one of those parameters?

[Jameson Gagnepain]

Sarah,

Yes, you should be matching multiple call legs on RouterCallKey and RouterCallKeyDay. Later call legs will always have a higher RouterCallKeySequenceNumber. I don't recall why I didn't use RCKSN in the above report... it may have made it simpler if I had.

There are SourceAgentPeripheralNumber and SourceAgentSkillTargetID fields, I don't know if they're populated for all transfers or not.

So... if you just want source and destination agent, you could perhaps look at RouterCallKey<>0 (not equal zero), and match the AgentSkillTargetID (as the destination agent) and SourceAgentSkillTargetID (as the originating agent). If you want the type of transfer, you would have to join with the earlier RCKSN entry with the same RCK and RCKD.

Another potential avenue to pursue would be the Route_Call_Detail table... it just has one entry for each target delivered to, and also uses RouterCallKeySequenceNumber so you know when items happened in the same "call"

-Jameson

[sarbarnes]

Thanks Jameson,

I think I have sorted it using what you sent and adding in Agent name, DateTime and a couple of Variables that are needed  Using Destination digits - I think I have what I need - who forwarded a call to a short Dial Call type and whether it went Blind or was an announced transfer!

You have set that the report will only show Call Dispostions 28 - 30, if I wanted to do this through the advance filter would I just take out this line

where (a.CallDisposition = 28 or a.CallDisposition = 29 or a.CallDisposition = 30) and a.RouterCallKeyDay <> 0(a.CallDisposition = 28 or a.CallDisposition = 29 or a.CallDisposition = 30) and a.RouterCallKeyDay <> 0

Hopefully the customer will be happy with what they have and I won't need to start looking at Router call detail tables :-)

have a great evening,

Sarah

[sarbarnes]

Jamieson,

I wonder if you can help out on the above.

I created the report and I believe it is woking fine, however the customer says otherwise!

Effectively I am grouping by the Destination Call type - to show how many calls have been transfered to those particular call type, which show eg 1992

Then the customer is running a TCD report againt those Call types and will see eg 1945

My 1st thought was to take out all the Call dispositions that are not transfer related (28,29,30) in the Transferee report, however that takes the total from 1992 to around eg 1926, which is where i need your help

From a scripting perspective the only way to get to those call types is to be transfered, then I don't understand why there is such a disconect!

I understand that the TCD report that the customer has created will only show calls that are offered to those Call types, however I would expect those numbers to match the refined report, where I am just running it against the transfered called dispositions.

I also understand that the TCD report will show the handled calls - terminated calls.

Everytime I think I have it cracked they come back with something else, also they don't want to run loads of reports they just want to be able to run the report I have created and the one that shows which agent actually took the call.

Appreciate it if you could come back to me...

Thanks Sarah

[Jameson Gagnepain]

Sarah,

Depending on agent behavior, there certainly could be 28,29,30 call dispositions as transferees - what if they transfer the call again? Depending on how you are doing your joins, you could be ending up with multiple extra rows getting summarized, which would be causing your total to be too large (and then too small when you remove the 28,29,30 destinations).

For example, say you have 3 call legs: the original, the first transfer, and the second transfer. The first and second transfer are getting your special call type. If you're joining based on the same RouterCallKey/RouterCallKeyDay combo, where RouterCallKeySequenceNumber is different, you could end up 4 rows getting summarized instead of 2. If you narrow it down more to where RouterCallKeySequenceNumber is just less on the joins, you narrow this down to 3 rows. Ideally, you'd want to join based on the RCK/RCKD combo, along with matching dialed digits on one row to DNIS on another.

The more specific you can get with your joins without excluding the wrong entries, the better. Your joins for this report should only be matching a single destination with a single source.

If you post your SQL, I'd be glad to take a look for you.

-Jameson

[sarbarnes]

Hi Jameson,

I put together a document that sort of shows you the problems, unfortunately I can't add it for some reason, effectively I have taken one of the Destination call types and run my report I have created against a number of call types in the filter (SQL below), I have also run a standard Call Type report against that Desitination Call type, also a Call type Skill Group report against the same Destination Call Type, those 2 reports match up.

Standard Call type Report on just the Destination Call type the results are 141 calls handled 30 abandoned = 171 this is also the same for the Call type Skill group

if I run the transferee report with an Advance filter on Destination Digits to match pattern 118 which is the short code the agent needs to use, as well as without that advanced filter. In some case the 230xx digits dialed has the same routecallkey as a 118xxx digits dialed, however in other instances there is only a 203xxx call record.

Total calls for the Destination Call Type is 178 which includes calls routed to destination digits 230xxx

If I do a Advance Search on matched Pattern 118 it only shows 149 records

When I run a basic TCD report against agent & the destination Call type I get 147 records

Effectively I want to be able to see if a call comes to me and then I transer via a 118 shortcode to you and you answer it that I can match the transfer from my report to a final report.

Does the RouteCallKey number that would be in my report remain the same if ran in another TCD report?

With regards to the SQL I have added  in the other data points that I needed, however fundamentally the SQL is the original one you sent over.

below is the actual SQL Query as you can see i am running it against a multitude of Call types, which could account for some of the decrepancies, but not all.

select 

    b.EnterpriseName as CallType,

    a.CallTypeID,

1. a.DateTime,
2. a.DbDateTime,
3. a.Variable1,
4. a.Variable2,
5. a.Variable10,

        case When a.CallDisposition = 1 then 'Aband Network'

When a.CallDisposition = 2 then 'Aband Local Q'

When a.CallDisposition = 3 then 'Aband Ring'

When a.CallDisposition = 4 then 'Aband Delay'

When a.CallDisposition = 5 then 'Aband Interflow'

when a.CallDisposition = 6 then 'Aband Agent'

When a.CallDisposition = 7 then 'Short'

When a.CallDisposition = 9 then 'Forced Busy'

When a.CallDisposition = 13 then 'Disc/Drop'

when a.CallDisposition = 14 then 'Handled Non-Agent'

when a.CallDisposition = 15 then 'Redirected'

When a.CallDisposition = 22 then 'Time Out'

When a.CallDisposition = 28 then 'Blind Transfer'

When a.CallDisposition = 29 then 'Announced Transfer'

when a.CallDisposition = 30 then 'Conferenced'

When a.CallDisposition = 31 then 'Duplicate Transfer'

When a.CallDisposition = 33 then 'Answering Machine' else 'Other' end as CallDispositionLabel,

    a.CallDisposition,

1. a.RouterCallKey,

    count(a.ICRCallKey) as TotalCalls,

    case

    when c.DigitsDialed is not null then c.DigitsDialed

    when j.DigitsDialed is not null then j.DigitsDialed

    when e.DigitsDialed is not null then e.DigitsDialed

    when g.DNIS is not null then g.DNIS

    else 'unknown' end as DestinationDigits,

    case

    when c.DigitsDialed is not null then d.EnterpriseName

    when j.DigitsDialed is not null then k.EnterpriseName

    when e.DigitsDialed is not null then f.EnterpriseName

    when g.DNIS is not null then i.EnterpriseName

    else m.EnterpriseName end as DestinationCallType,

    case

    when c.DigitsDialed is not null then c.CallTypeID

    when j.DigitsDialed is not null then j.CallTypeID

    when e.DigitsDialed is not null then e.CallTypeID

    when g.DNIS is not null then h.CallTypeID

    else l.CallTypeID end as DestinationCallTypeID,

1. r.EnterpriseName as FullName,

AgentName = (s.FirstName + ' ' + s.LastName)

from nfum_hds.dbo.Termination_Call_Detail a

left join nfum_awdb.dbo.Call_Type b on a.CallTypeID = b.CallTypeID

left join nfum_awdb.dbo.Agent r on a.AgentSkillTargetID = r.SkillTargetID

left join nfum_awdb.dbo.Person s on r.PersonID = s.PersonID

left join nfum_hds.dbo.Termination_Call_Detail c on a.RouterCallKeyDay = c.RouterCallKeyDay and a.RouterCallKey = c.RouterCallKey and a.ICRCallKey <> c.ICRCallKey and ((c.DNIS <> 115001 and c.DNIS <> 115000) or c.DNIS is null) and c.ICRCallKeyParent is null and c.DigitsDialed is not null and a.DNIS = c.ANI and c.CallDisposition <> 19

left join Call_Type d on c.CallTypeID = d.CallTypeID

left join nfum_hds.dbo.Termination_Call_Detail j on c.DigitsDialed is null and a.RouterCallKeyDay = j.RouterCallKeyDay and a.RouterCallKey = j.RouterCallKey and a.ICRCallKey <> j.ICRCallKey and j.ICRCallKeyParent = a.ICRCallKey and a.DNIS = j.DNIS and a.ANI = j.ANI and j.DigitsDialed is not null and j.DigitsDialed <> a.DigitsDialed

left join Call_Type k on j.CallTypeID = k.CallTypeID

left join nfum_hds.dbo.Termination_Call_Detail e on c.DigitsDialed is null and j.DigitsDialed is null and a.RouterCallKeyDay = e.RouterCallKeyDay and a.RouterCallKey = e.RouterCallKey and a.ICRCallKey <> e.ICRCallKey and e.DNIS is null and e.ICRCallKeyParent = a.ICRCallKey and a.DNIS = e.ANI and e.DigitsDialed is not null

left join Call_Type f on e.CallTypeID = f.CallTypeID

left join nfum_hds.dbo.Termination_Call_Detail g on c.DigitsDialed is null and j.DigitsDialed is null and e.DigitsDialed is null and a.RouterCallKeyDay = g.RouterCallKeyDay and a.RouterCallKey = g.RouterCallKey and a.ICRCallKey <> g.ICRCallKey and g.ICRCallKeyParent = a.ICRCallKey and (g.ICRCallKey <> a.ICRCallKeyChild or a.ICRCallKeyChild is null) and a.DNIS = g.ANI

left join Termination_Call_Detail h on g.RouterCallKey is not null and a.RouterCallKeyDay = h.RouterCallKeyDay and a.RouterCallKey = h.RouterCallKey and a.ICRCallKey <> h.ICRCallKey and h.DNIS = 0115001 and h.ANI = a.DNIS

left join Call_Type i on h.CallTypeID = i.CallTypeID

left join nfum_hds.dbo.Termination_Call_Detail l on c.DigitsDialed is null and e.DigitsDialed is null and j.DigitsDialed is null and g.DNIS is null and a.RouterCallKeyDay = l.RouterCallKeyDay and a.RouterCallKey = l.RouterCallKey and a.ICRCallKey <> l.ICRCallKey and l.DNIS = 115001 and l.ANI = a.DNIS

left join Call_Type m on l.CallTypeID = m.CallTypeID

where ((case       when c.DigitsDialed is not null then c.DigitsDialed       when j.DigitsDialed is not null then j.DigitsDialed      when e.DigitsDialed is not null then e.DigitsDialed       when g.DNIS is not null then g.DNIS      else 'unknown' end) like '%118%') and (a.DateTime between '2014-10-01 00:00:00' and '2014-10-31 23:59:59' and DATEPART(dw, a.DateTime) in(2,3,4,5,6,7,1) and DATEPART(hour, a.DateTime) between 00 and 23 and DATEPART(minute, a.DateTime) between 00 and 59 and DATEPART(second, a.DateTime) between 00 and 59) and (a.CallTypeID IN (5605, 5769, 5780, 5772, 5003, 5777, 5775, 5779, 5119, 5118, 5747, 5005, 5325, 5327, 5007, 5329, 5330, 5340, 5341, 5108, 5338, 5339, 5346, 5013, 5332, 5333, 5014, 5335, 5336, 5770, 5054, 5608, 5616, 5617, 5229, 5162, 5774, 5089, 5778, 5776, 5773, 5122, 5121, 5018, 5019, 5191, 5125, 5192, 5124, 5020, 5193, 5126, 5021, 5194, 5127, 5022, 5195, 5128, 5023, 5196, 5129, 5024, 5197, 5130, 5025, 5198, 5131, 5026, 5199, 5132, 5027, 5200, 5133, 5110, 5201, 5134, 5111, 5202, 5135, 5028, 5203, 5136, 5029, 5204, 5137, 5036, 5035, 5030, 5205, 5138, 5031, 5206, 5139, 5207, 5140, 5032, 5033, 5208, 5141, 5211, 5144, 5034, 5209, 5142, 5210, 5143, 5037, 5212, 5145, 5038, 5213, 5146, 5039, 5214, 5147, 5040, 5215, 5148, 5041, 5216, 5149, 5042, 5217, 5150, 5043, 5218, 5151, 5044, 5045, 5219, 5152, 5046, 5220, 5153, 5047, 5221, 5154, 5048, 5222, 5155, 5049, 5223, 5156, 5050, 5224, 5157, 5051, 5225, 5158, 5052, 5226, 5159, 5053, 5227, 5160, 5228, 5161, 5439, 5440, 5441, 5055, 5230, 5163, 5056, 5231, 5164, 5057, 5232, 5165, 5058, 5233, 5166, 5059, 5234, 5167, 5060, 5235, 5168, 5061, 5236, 5169, 5444, 5453, 5484, 5504, 5063, 5239, 5172, 5064, 5307, 5240, 5173, 5065, 5241, 5174, 5066, 5242, 5175, 5067, 5243, 5176, 5068, 5245, 5178, 5069, 5246, 5179, 5070, 5247, 5180, 5071, 5248, 5181, 5494, 5514, 5445, 5072, 5249, 5182, 5496, 5516, 5446, 5497, 5517, 5447, 5498, 5518, 5448, 5499, 5519, 5449, 5500, 5520, 5450, 5501, 5521, 5451, 5502, 5522, 5452, 5503, 5523, 5237, 5170, 5238, 5171, 5062, 5244, 5073, 5250, 5183, 5074, 5251, 5184, 5075, 5252, 5185, 5076, 5253, 5186, 5077, 5254, 5187, 5078, 5255, 5188, 5771, 5083, 5084, 5085, 5745, 5746, 5855, 5853, 5857, 5854, 5856, 5860, 5858, 5859, 5852, 5861, 5863, 5862, 5736, 5607)) and ((case       when c.DigitsDialed is not null then d.EnterpriseName       when j.DigitsDialed is not null then k.EnterpriseName      when e.DigitsDialed is not null then f.EnterpriseName       when g.DNIS is not null then i.EnterpriseName      else m.EnterpriseName end) like '%SP_MID%') and    a.RouterCallKeyDay <> 0

group by b.EnterpriseName, a.CallTypeID, a.CallDisposition, a.DateTime, a.DbDateTime, a.Variable1, a.Variable2, a.Variable10, r.EnterpriseName, s.FirstName, s.LastName, a.RouterCallKey,

case

    when c.DigitsDialed is not null then c.DigitsDialed

    when j.DigitsDialed is not null then j.DigitsDialed

    when e.DigitsDialed is not null then e.DigitsDialed

    when g.DNIS is not null then g.DNIS

    else 'unknown' end,

    case

    when c.DigitsDialed is not null then d.EnterpriseName

    when j.DigitsDialed is not null then k.EnterpriseName

    when e.DigitsDialed is not null then f.EnterpriseName

    when g.DNIS is not null then i.EnterpriseName

    else m.EnterpriseName end,

    case

    when c.DigitsDialed is not null then c.CallTypeID

    when j.DigitsDialed is not null then j.CallTypeID

    when e.DigitsDialed is not null then e.CallTypeID

    when g.DNIS is not null then h.CallTypeID

    else l.CallTypeID end

order by b.EnterpriseName, a.CallTypeID, a.CallDisposition

[Jameson Gagnepain]

Sarah,

Your Group By statement is humongous and not really doing anything useful... try removing it, and see how the results look.

As for the Where statement, it would be a good idea to simplify that a bit. The Case statements that CUIC is automatically inserting (with your Advanced filtering) are not the best way to do things. You could replace this part:

((case       when c.DigitsDialed is not null then c.DigitsDialed       when j.DigitsDialed is not null then j.DigitsDialed      when e.DigitsDialed is not null then e.DigitsDialed       when g.DNIS is not null then g.DNIS      else 'unknown' end) like '%118%')

With something like this:

(c.DigitsDialed LIKE '118%' OR j.DigitsDialed LIKE '118%' OR e.DigitsDialed LIKE '118%' OR g.DNIS LIKE '118%')

The two are not exactly the same, but it should work the same in this case. The result is a less flexible report, but it sounds like you may be looking for a very specific purpose for it.

-Jameson

[sarbarnes]

Thanks Jameson, whilst not probably the best way, I will keep the Advance filter as the customer does not get access to the report definition and then they can make those changes if they need to.

having run my transferee report and a TCD report against the Destination Call type including teh RouterCallKey, I have seen in the TCD report the RourerCallKey twice in some cases, due to the fact the call has been transefered from the originating Call type to the Destination call type and then that agent as transfered it on again to 'another' Destination Call type. Basically the Destination Call types are not including in the initial Base Filter, therefore I believe by including them the numbers should practically match up.

Hopefully I won't need to bug you any more. thanks for your help, much apprecaited.