Current Assignment

[Former Member]

Hi Everyone -

I have a report that shows analysts if they have tickets.

Please help with a formula to have a check on this.

I would like Analysts to only be on the report if they are the Owner and have the current/latest Assignment on a ticket.

1. Owner of ticket field {CallLog.Tracker}

2. Assignment field: {Assignment.Assignee) - this field may have multiple Assignments, because the ticket maybe escalated to other analysts - need the latest (analyst) Assignment on the report if they are also the owner of the ticket too.

Thanks for your help!

Gennaro

[abhilash_kumar · Accepted solution]

Hi Gennaro,

Do you have a date field to identify the latest assignment date?

If yes, then you can sort the date field in Descending order (assuming the 'Ticket Number' is a group on the report).

All you need to do is, create a suppression formula based on the Assignee = Ticket Owner condition.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash -

Yes, I have a date field. It is CallLog.ModDate (String).

Thanks.

Gennaro

[abhilash_kumar · Accepted solution]

Do you also want the Ticket to not show when the Owner is not the latest Assignee?

-Abhilash

[Former Member · Accepted solution]

Yes.

I only want the ticket/Analyst/Tracker to show if the Analyst is the Owner and has the current Assignment.

If the Analyst has the latest Assignment, but is not the Owner of the ticket then the ticket will not be on the report.

The ticket field is CallLog.Callid in case you need it.

Thanks.

[abhilash_kumar · Accepted solution]

OK.

1) Create a group on the Ticket Number field

2) Create a formula that converts the CallLog.ModDate to date. Search the forum and there are many examples on how to convert a string to date

3) Go to the Record Sort Expert > Add the above formula to the Sort List and sort in Descending Order

4) Create a formula (@Owner) with this code:

{CallLog.Tracker} = {Assignment.Assignee}

5) Place this on the Group Header and Suppress it. Then go to the Selection Formulas > Group and use this code:

{@Owner}

-Abhilash

[Former Member · Accepted solution]

Hi -

For the date conversion I used CDate ({CallLog.ModDate})

I followed the instructions but my data has disappeared.

Did I do the conversion correctly?

Thx.

G.

[Former Member · Accepted solution]

Hi -

When I add the {@owner} to the group header CallLog.CallID all my data disappears.

Not sure what went wrong. Here's a snap shot of what I have so far.

thanks.

G.

[abhilash_kumar · Accepted solution]

Remove the Group Selection Formula and un-suppress the @Owner formula and the Group Header as well.

What does the formula return for each group?

Please post a snapshot of that well.

-Abhilash

[Former Member · Accepted solution]

Hi -

Here's two snap shots of the report- per your instructions above. I will be in class today so I will try to revert to you today in-between. Many thanks...

and

[abhilash_kumar · Accepted solution]

I think the formula works, doesn't it?

If you look at the Assignee and the Analyst column, I don't see any names that are same.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash -

Only when the tracker and assignee fields are the same - when these 2 conditions are met then the tickets are show on the report.

If these 2 conditions are not met then the ticket will not be on the report.

Thanks.

Gennaro

[abhilash_kumar · Accepted solution]

I guess that's what the formula does, doesn't it?

Are you saying that the report does not show ticket numbers where both the Assignee and the Tracker are same?

-Abhilash

[Former Member · Accepted solution]

If the Analyst/Tracker and the Assignee are the same, then that ticket will be on the report. I am attaching a snap shot and a snap shot of the assignment screen for your review. Thx.

For example, in the snap shot below, ticket # 1151999 is not suppossed to be there because the last assignment is "Service Desk" not Jaro Antonio.

On ticket # 1159058, the analyst and assignee are correct, but this ticket is not supposed to be on the report because they are not the same - the assignee and analyst needs to be the same.

On this ticket, Anton the analyst is the owner, and Jerimiah LaGrappe has the last, number 4, assignment on this ticket. Also, on this ticket Anton/Jaro Antonio has an assignment, number 2. This ticket has 4 assignments.

On Ticket 1161077, the last (6) assignment on this ticket is Keith Filips, not Jaro Antonio. This ticket has 6 assignments. The analyst on this ticket is correct. Jaro Antonio has the 4th assignment on this ticket, not the last. This ticket is not supposed to be on the report. Also, the report is not reporting the last assignment/assignee accurately.

I know this is kinda tough, and anything you can help with would be appreciated.

[Former Member · Accepted solution]

Hi Abhilash -

Sorry, an interesting relelation. The assignee does not have to be the last assignment, but just needs to have an 'Active' assignment on the ticket and be the Owner/Tracker of the ticket.

So ticket 1151999 is good, because the analyst has an 'Active' assignment on the ticket.

On Ticket 1159058, The analyst Anton does have an Assignment on the ticket but the Status is 'Closed' and would not be on the report.

On ticket 1161077, The Analyst Anton does have an Assignment on this ticket and the assignment is 'Active' so this ticket would be on the report.

{CallLog.Tracker} = {Assignment.Assignee} and

{Asgnmnt.AssignStatus} = Active

Something like this. Sorry.

Gennaro

[Former Member · Accepted solution]

Abhilash -

What would you suggest I do?

Thx.

G.

[abhilash_kumar · Accepted solution]

Have you tried changing the Group Selection formula to:

{Asgnmnt.AssignStatus} = Active

This will give you records where the status = active.

Once we have the active records we'll see how to further filter the records.

-Abhilash

[Former Member · Accepted solution]

Yes, but there can be more than one active assignment on the ticket.

I need the Analyst/Owner of the ticket and the Assignee to be the same only if the Assignee has an active assignment. The Analyst usually has an assignment on the ticket but it could be active or closed. And if his assignment is active then the ticket should be on the report.

So that's why I was thinking something like this...

{Assignment.Assignee} = {CallLog.Tracker} and

{Asgnmnt.AssignStatus} = "Active"

If this condition is met than the ticket is on the report.

But i dont know how to get this to work?

Thx.

G.

[Former Member · Accepted solution]

I have added {Asgnmnt.AssignStatus} = Active to the Select Expert Record.

Now I need this {Assignment.Assignee} = {CallLog.Tracker}

The {Assignment.Assignee} needs to have an active assignment.

Thx.

G.

[abhilash_kumar · Accepted solution]

First make sure the report shows all assignments - latest to past.

For this you'll need to sort the report by Date (I'm assuming a ticket with the latest date is the most current aissgnment?)

Now, from these records, which tickets do you wish to see on the report?

-Abhilash

[Former Member · Accepted solution]

The ticket # field is CallLog.Callid and date opened field CallLog.RecvdDate, please see my post on May 6, 2014 8:14 AM, the second sreenshot.

I need the tickets on the report only if the Asgnmnt.Assignee has an Active assignment on the ticket and if the Asgnmnt.Assignee is the same as the CallLog.Tracker/Analyst/Owner.

Thx.

G

[abhilash_kumar · Accepted solution]

You mentioned that a ticket, in it's lifecyle, could be assigned to multiple Analysts, didn't you?

However, the screenshot only shows one row for each ticket. I'm trying to understand if you've suppressed the rows to show only the latest ticket?

Are these separate fields:

CallLog.Tracker, CallLog.Analyst and CallLog.Owner?

If yes, then have you tried changing the Owner formula to:

(

{CallLog.Tracker} = {Assignment.Assignee}

OR

{Assignment.Assignee} = {CallLog.Analyst}

OR

{CallLog.Owner} = {Assignment.Assignee}

)

AND

{Assignment.Assignee} = "Active"

-Abhilash

[Former Member · Accepted solution]

I am sorrry. CallLog.Tracker is only one field. This field holds the analyst of the ticket, he is also the owner of the ticket.

There is no CallLog.Analyst or CallLog.Owner fields -  please see my post on May 6, 2014 8:14 AM, the second sreenshot for all the fields on the report.

Thx.

G.

[Former Member · Accepted solution]

I changed the @Owner formula based on your suggestion above with the following adjustments.

(

{CallLog.Tracker} = {Asgnmnt.Assignee}

)

and

{Asgnmnt.Assignee} = "Active"

Please be specific above the placement and the steps to get this to work.

Thx.

Gennaro

[abhilash_kumar · Accepted solution]

OK. Place the formula on the Group Header.

Look at a ticket that has had multiple analysts > See what the formula on the header shows ; True/False.

Groups with False will go off the report.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash -

Currently, I have one group, and it's for the Analyst. Do I need another group? Do I need to move any fields from the Detail Section?

Please be specific. Thanks.

Gennaro

[abhilash_kumar · Accepted solution]

No fields are to be moved from or to the Details Section.

Just place the Owner formula on the Group Header and see what it returns.

If any ticket has multiple assignments, then this could easily tell us where the formula could be failing.

-Abhilash

[Former Member · Accepted solution]

Yes, I did as you asked, but there is one (1163660) ticket that is not supposed to be on the report.

Tickets can have more than one assignment. But if the Analyst of the ticket has also has an assignment, and their assignment is ACTIVE than the ticket should BE on the report.

If the Analyst of the ticket has also an assignment, and their assignment is CLOSED than the ticket should be NOT the report.

Here are couple of snap shots. I apprecicate your help...thank you.

[Former Member · Accepted solution]

Hi -

For the Analyst 'AntonJ83', these tickets are not supposed to be on the report.

1163660

1169734

1168258

1169954

because, the Analyst's assignment is 'Closed', so that's why the ticket is not supposed to be on the report.

But somehow there showing on the report.

Thx.

Gennaro

[abhilash_kumar · Accepted solution]

OK. Let's look at the second screenshot above.

Ticket #1 belongs to Analyst ANTONJ83. So, if I understand this correctly, should we look at the Assignee column and check whether ANTONJ83 exists in this column and if the status on the row this name is found = 'Active'

Do the Analysts have a pseudo name too? Is ANTONJ83 the same person as Jaro Antonio?

-Abhilash

[abhilash_kumar · Accepted solution]

And what about the other tickets for Anton? How can I tell whether the Analyst and Assignee are the same?

-Abhilash

[Former Member · Accepted solution]

Yes, they have a pseudo name, it's their ID. This report is grouped on the Analyst field, which is CallLog.Tracker.

ANTONJ83 is Jaro Antonio

CamCacx1 is Carl Camcam and so on....

thx.

[Former Member · Accepted solution]

As in the formula @Owner if this is met than its on the report, if its not met than its not on the report.

Here's the formula - could prehaps an if else statement work?

(

{Asgnmnt.Assignee} = {CallLog.Tracker}

)

and

{Asgnmnt.AssignStatus} = "Active"

thx.

G.

[abhilash_kumar · Accepted solution]

OK. I understand why Ticket # 1169719 should be on the report - because it both the Assignee and the Analyst are same and it is open/active.

What about, say, Ticket # 1169788? It doesn't belong to the same Assignee Here the Analyst's name and the Assignee's name doesn't match, so should that be on the report too? If not, then why?

We're getting close!

-Abhilash

[Former Member · Accepted solution]

I can't see the ticket 1169788 your referring to on the screen shot...is it another number?

thx. 

[abhilash_kumar · Accepted solution]

Sorry! That would be 1166988. And more:

1167610

1168258 etc

-Abhilash

[Former Member · Accepted solution]

Okay...

1166988 should be on the report because the analyst of the ticket does have an assignment and it is Active, but it is showing Chelsea Dones instead - this person does have an assignment on the ticket but it is Closed.

1167610, AntonJ83 or Jaro Antonio does have an active assignment, but it is showing ATG-Database instead. This ticket should be on the report.

1168258, should not be on the report because AntonJ83 or Jaro Antonio does not have an assignment on this ticket.

1169954, should not be on the report because AntonJ83 or Jaro Antonio does not have an assignment on this ticket.

1169734, should not be on the report because AntonJ83 or Jaro Antonio does not have an assignment on this ticket.

1169547, should be on the report because AntonJ83 or Jaro Antonio does have an assignment and it is Active, but it is showing Andre Fairley instead - this person does have an assignment on the ticket but it is Closed.

1168658, should be on the report because AntonJ83 or Jaro Antonio does have an assignment and it is Active, but it is showing Richard Nery instead - this person also has an Active assignment on this ticket.

I hope this provides a clearer view.

many thanks.

G.

[abhilash_kumar · Accepted solution]

This is even more confusing, I'm so sorry!

Ticket # 1166988, where does it say that Chelsea Dones has an assignment on the ticket and is closed?

Ticket # 1168258, where does it say that Antonio does not have an assigment.

If I look at these two tickets on the screenshot, I really don't see any difference - both have the status as Responded and both are currently assigned to someone other than Antonio, yet only the first one has to be on the report. Which field further differentiates these two tickets?

-Abhilash

[Former Member · Accepted solution]

Hi, the report extracts it's data from a third party tool/database. If I look at the ticket details on that third party application I can see ticket details. Here's two assignment sceen shots of 1166988 ticket detail.

this is okay, as long as the analyst has an assignment and it is Active. The analyst's name doesn't necessarly have to be in the Assignee field. As long as the @Owner formula conditions are met.

Thx.

G.

[Former Member · Accepted solution]

on ticket 1168258, The analyst is AntonJ83 or Jaro Antonio and he is the Analyst, but does not have an assignment. so this ticket should not be on the report. Bryan Do has the assignment and it's Active.

So the Analyst can have or have not an assignment. And that Assignment can be Active or Closed. If the Analyst has an assignment and it's closed than the ticket should not be on the report.

Is this challenging or what?

Thx.

Gennaro

[abhilash_kumar · Accepted solution]

Is there a field in your database that you can get into the report to show whether each ticket number is active or closed? For eg Chelsea dones' ticket shows closed on the app but shows Responded on the ticket.

-Abhilash

[Former Member · Accepted solution]

Yes, as on the @Owner formula...

(

{Asgnmnt.Assignee} = {CallLog.Tracker}

)

and

{Asgnmnt.AssignStatus} = "Active"

Asgnmnt.AssignStatus is the field for Active or Closed. In the Sreenshots I just posted it would be the Status box. But this field is not on the report canvas, just in the formula.

[Former Member · Accepted solution]

In your post of (6th post, from the top) May 5, 2014 12:32 PM

On your instructions - the only thing I have done is create the formula @Owner and place it on the CallLog.Tracher GH #1 section. Just to be clear.

G

[abhilash_kumar · Accepted solution]

The issue with this formula is that it'll only show rows where the Assignment = Assignee and the ticket status = Active.

Which means tickets like 1167610 will Not show on the report.

You said in your reply yesterday that :

1167610, AntonJ83 or Jaro Antonio does have an active assignment, but it is showing ATG-Database instead. This ticket should be on the report.

Why should this be on the report when the Assignment and the Assignee fields are clearly not the same?

-Abhilash

[Former Member · Accepted solution]

Good Morning Abhilash -

Yes, 1167610 should be on the report because it meets the requirement. But, I don't know why ATG -Database is showing instead of the Analyst, which has an Active Assignment.

ATG -Database also has an Active assignment on this ticket.

Thx.

G.

[abhilash_kumar · Accepted solution]

What are all the conditions for a ticket to show up on the report?

Clearly, Assignment = Assignee and Assignment Status = Active alone won't be enough as in most of the examples above they're not same and are yet supposed to be on the report.

-Abhilash

[Former Member · Accepted solution]

Hi, another condition that can be met is:

{CallLog.Tracker} = {Asgnment.HeatLogin}

The above condition could be added to:

(

{Asgnmnt.Assignee} = {CallLog.Tracker}

)

and

{Asgnmnt.AssignStatus} = "Active" and

{CallLog.Tracker} = {Asgnment.HeatLogin}

?

Thanks.

Gennaro

[abhilash_kumar · Accepted solution]

It'll still fail because of this statement : {Asgnmnt.Assignee} = {CallLog.Tracker}

In your previous reply you said:

"ATG -Database also has an Active assignment on this ticket."

Let's get back to the basics. Here's what I understand:

- A Ticket is first assigned to an Analyst. So, both the Analyst and the Assignee would be the same(?)

- This ticket could then be either closed by the same person or

- Transferred/escalated to someone else in which case it gets a new Assignee(?)

- This new assignee can then close this ticket or further transfer it to someone else

In which of these situations would you want the ticket to be on the report?

Look at it as a group of records and try to answer that as to why you'd want this group of records to be or not be on the report

-Abhilash

[Former Member · Accepted solution]

Hi -

Tickets can have multiple assignments - so that means because there are multiple assignments there are also multiple assignees. Each assignment has an assignee. A ticket could be first assigned to someone else other than the Analyst/CallLog.Tracker of the ticket.

For example on ticket 1167610, the Analyst has the 3rd assignment and ATG-Database has the 4th assignment. Both assignments are Active. Tickets are escalated/assigned to individuals or Departments i.e. ATG-Database.

Currently, the report is grouped by Analyst's records/tickets. I want tickets shown on the report for each analyst only if the Analyst also has an assignment and it is Active.

Here are the fields for the report from left to right....

Open Date: CallLog.RecvdDate (string)

Last Update: CallLog.ModDate (string)

Ticket #: CallLog.CallID

Analyst: Calllog.Tracker

Assignee: Asgnmnt.Assignee

Ticket Status: CallLog.CallStatus

Key Word: CallLog.Keyword

Active: Asgnmnt.AssignStatus, not currently on the report canvas

Thx.

G.

[abhilash_kumar · Accepted solution]

OK. Easy question - how do you find out whether an Analyst has had an assignment on a ticket?

Take 1167610 as an example.

-Abhilash

[Former Member · Accepted solution]

You have to go to the third party ticket application to view the assignments - you can click the tab 'Assignment' and scroll through the assignments. Please see the screenshot below.

Crystal gets it's data from the backend of this tool.

We may not be able to fulfill this requirement.

Thx.

Gennaro

[abhilash_kumar · Accepted solution]

We can, if you could somehow get this important piece of info into the report.

Unless CR has the data, there is no way it can tell whether an Analyst has ever been the Assignee for that ticket.

-Abhilash