formula suggestions needed...this one is a doozy to me

[nosajld]

Greetings -

I'm running various reports on our phone system (ShoreTel) through CR XI. We are needing to know when a user transfers a call out to another internal number or external number. Currently ShoreTel only marks a Transfer when you RECEIVE the transfer. The DO NOT mark a status for when you SEND a transfer.

Here is what I have to work with:

Each call record has a PRIKEY of CallTableID.

Here is a small sample of what I have for clarity...

PartyID ConnectReason TableCallID
2298 Called 1,145,395.00 (received an outside call)
19999999999 Originate 1,145,395.00 (This is the incoming external call)
2267 Transfer 1,145,395.00 (2267 receives a transfered call)

Currently all records are grouped on the PartyID.
I have to work backwards from the ConnectReason = "Transfer" to find the corresponding entries within TableCallID which has the ConnectReason of "Called" then return PartyID to a global var which adds 1 for that Party ID for calls transfered out.

Please any questions or assistance is greatly appreciated.

Thank you!


Jason

Currently all records are grouped on the PartyID.

 

[Turkbear]

Hi,
If all this is in one table (lets call it CallTable ), then try someting like this:

Add it a second time ( CR will gives it an alias) .

Link by TableCallID and use a 2 part selection criteria formula:

{CallTable.ConnectReason} = "Transfer"
AND
{AliasedCallTable.ConnectReason} = "Called"

The PartyIDs returned should then only be only those that meet that criteria and can be placed in your variable ( or just counted)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
As an alternative to just returning the specified records, the formula I posted could be used as a count by using it something like this( instead of as selection criteria):

@CountThis
If
({CallTable.ConnectReason} = "Transfer"
AND
{AliasedCallTable.ConnectReason} = "Called"

)
then
1
else
0

You can then sum that formula to determine how many PartyIDs
meet the test out of all the records returned.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[nosajld]

This has helped me get moving BUT I'm having trouble with grabbing the PARTYID that corresponds with the 'ORIGINATE' line.

If I attempt to display the {connect_1.PartyID} or {Connect.PartyID} it returns only the previous PARTYID in the listing which is expected. I need to grab the PARTYID associated with the ORIGINATE line... Sorry for the double talk - lots of caffeine this morning

Thank you for all of your assistance!

 

[nosajld]

Nevermind Figured it out

thank you for all of your help!