Show name if in two fields, if not, do not show on report.

[gennaroalpha7]

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

 

[pmax9999]

Try this:
[ul]
[li]Create a group on the [CallLog ID] field;[/li]
[li]Sort (Ascending) on the [Assignment Date] field;[/li]
[li]Suppress GH and Details sections, and mover= required fields from Details to GF section;[/li]
[li]Use the following code to conditionally suppress the GF Section

{Assignment.Assignee} <> {CallLog.Tracker}

[/li]
[/ul]

Hope this helps.

Cheers
Pete

 

[gennaroalpha7]

Hi Pete -

Thanks for helping me again...

I will be in and out today and I will try your suggestion and revert back to you.

Many thanks.

Gennaro

 

[gennaroalpha7]

Hi-

I have a clarification, sorry.

The Assignee does not have to be the last assignment, but just needs to have an 'Active', not Closed, assignment on the ticket.

The Analyst/Owner of the ticket must have an Active assignment on the ticket.

Here are the fields that are involved...

1. Analyst/Owner of ticket field {CallLog.Tracker}

2. Assignment field: {Assignment.Assignee)

3. Ticket number field: CallLog.CallID

4. Date ticket recieved: Callog.RecvdDate (String)

5. Assignment Status field, not currently on report: Asgnmnt.AssignStatus

Something like this?

{CallLog.Tracker} = {Assignment.Assignee} and {Asgnmnt.AssignStatus} = Active

Thanks.

Gennaro

 

[pmax9999]

Can a Call have more than 1 "Active" {Asgnmnt.AssignStatus}?

The code you suggested looks like it will work. Did you try it? What was the outcome?

Regards
Pete

 

[gennaroalpha7]

Hi Pete -

Yes, a ticket can have more than 1 active assignment.

I created the above formula with this code and no errors were given. I just don't quite know how to implement the formula into the report.

When I replace the Asgnmnt.Assignee field with this formula it just says 'False'

The report has 2 groups, the analyst/tracker group 1 and the Calllog.callId/ticket number group 2

How can I include a snap shot my report here?

Thx.

G.

 

[pmax9999]

Try adding that code to the Record Selection formula.

Pete

 

[gennaroalpha7]

Hi -

I added this code to the Selection Formula Record, but all the data disappeared. What am I doing wrong?

I know this is not too difficult but I am having a hard time getting my head around this problem.

Your help would be appreciated.

Thx.

G.

 

[gennaroalpha7]

Pete -

I followed the instructions in the second post and the data has disappeared, not sure why. Am I doing something wrong?

Please respond.


Gennaro

 

[pmax9999]

Please post your Record Selection formula and the SQL Query generated by Crystal.

Please also confirm that the report should show all open Calls where the "Owner" ({CallLog.Tracker}) is also an "Assignee" ({Assignment.Assignee}) and the "Assignment" is Active.


Pete

 

[gennaroalpha7]

Hello -

Record Selection Formula:

{CallLog.CallStatus} <> "Closed" and
{CallLog.Tracker} in ["ANTONJ83", "CAMCACX1", "CHAVEV60", "DONESC72", "FAIRLA01", "FLOREV02", "Heldet00", "HERNAE03", "HERREC86", "jauree01", "LIMM01", "mirelj91", "Perezm58", "phant27", "Poste54", "PRICER64", "QUEZAM01", "RIGORAD1", "salesc01", "VAZQUD01"] and
not ({CallLog.KeyWord} in ["FIS", "TP Admin Defect", "TP Capture Defect", "TP Sales Defect", "TP Service Defect", "TP Teller Defect", "TPSS Defect"])

SQL Query generated by Crystal:

SELECT "CallLog"."CallID", "CallLog"."RecvdDate", "CallLog"."Tracker", "CallLog"."CallStatus", "CallLog"."ModDate", "CallLog"."KeyWord", "Asgnmnt"."Assignee", "Asgnmnt"."AssignStatus"
FROM "heat"."Heatdb"."CallLog" "CallLog" INNER JOIN "heat"."Heatdb"."Asgnmnt" "Asgnmnt" ON ("CallLog"."CallID"="Asgnmnt"."CallID") AND ("CallLog"."DTLastMod"="Asgnmnt"."DTLastMod")
WHERE "CallLog"."CallStatus"<>'Closed' AND ("CallLog"."Tracker"='ANTONJ83' OR "CallLog"."Tracker"='CAMCACX1' OR "CallLog"."Tracker"='CHAVEV60' OR "CallLog"."Tracker"='DONESC72' OR "CallLog"."Tracker"='FAIRLA01' OR "CallLog"."Tracker"='FLOREV02' OR "CallLog"."Tracker"='Heldet00' OR "CallLog"."Tracker"='HERNAE03' OR "CallLog"."Tracker"='HERREC86' OR "CallLog"."Tracker"='jauree01' OR "CallLog"."Tracker"='LIMM01' OR "CallLog"."Tracker"='mirelj91' OR "CallLog"."Tracker"='Perezm58' OR "CallLog"."Tracker"='phant27' OR "CallLog"."Tracker"='Poste54' OR "CallLog"."Tracker"='PRICER64' OR "CallLog"."Tracker"='QUEZAM01' OR "CallLog"."Tracker"='RIGORAD1' OR "CallLog"."Tracker"='salesc01' OR "CallLog"."Tracker"='VAZQUD01') AND NOT ("CallLog"."KeyWord"='FIS' OR "CallLog"."KeyWord"='TP Admin Defect' OR "CallLog"."KeyWord"='TP Capture Defect' OR "CallLog"."KeyWord"='TP Sales Defect' OR "CallLog"."KeyWord"='TP Service Defect' OR "CallLog"."KeyWord"='TP Teller Defect' OR "CallLog"."KeyWord"='TPSS Defect')
ORDER BY "CallLog"."Tracker"

Requirement:

The ticket should be on the report if the Analyst also has an Assignment on the ticket and the assignment is Active, not Closed. Some tickets have more than one Active assignment.

Fields (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


Thank you.

Gennaro

 

[pmax9999]

Gennaro

In your post of 8 May 14 11:25 you said that you "... added this code to the Selection Formula Record, but all the data disappeared ...". Please confirm that your Record Selection looked like this:

{CallLog.CallStatus} <> "Closed" and
{CallLog.Tracker} in ["ANTONJ83", "CAMCACX1", "CHAVEV60", "DONESC72", "FAIRLA01", "FLOREV02", "Heldet00", "HERNAE03", "HERREC86", "jauree01", "LIMM01", "mirelj91", "Perezm58", "phant27", "Poste54", "PRICER64", "QUEZAM01", "RIGORAD1", "salesc01", "VAZQUD01"] and
not ({CallLog.KeyWord} in ["FIS", "TP Admin Defect", "TP Capture Defect", "TP Sales Defect", "TP Service Defect", "TP Teller Defect", "TPSS Defect"]) and
({CallLog.Tracker} = {Assignment.Assignee} and {Asgnmnt.AssignStatus} = "Active")

Can't see any obvious reason why that would not have worked, but assuming it didn't, please post some sample data from the report.

Cheers
Pete

 

[gennaroalpha7]

Hi Pete -

Confirmed.

Please click the link to view the data. The link is the only way I know how to post data without having to type it all. Thanks.

https://www.dropbox.com/s/j2i3kh7irnjnlhf/SD_Tickets_V0.1_Devel.rpt

I have tried adding (below) this to the record selection, Group Selection, and to the (as a formula) GH #1, but it's not taking for some reason.

({CallLog.Tracker} = {Assignment.Assignee} and {Asgnmnt.AssignStatus} = "Active")

Thx.

G

 

[pmax9999]

OK, I've had a look at your report. The reason the additional code didn't work is that the {CallLog.Tracker} field will never match the {Asgnmnt.Assignee} field. This is because {CallLog.Tracker} is a code (eg "CAMCACX1") and {Asgnmnt.Assignee} is a full name (eg "Carl Camcam").

I presume database has a User look-up table that contains both the codes and the matching names. You will need to include that table so you can match the data.

This really does demonstrate the need to understand the data you are working with.

Hope this helps.

Cheers
Pete

 

[gennaroalpha7]

Hi Pete _

Dam!, thank you very much! I would have never thought of that or looked for that. Thank you for this learning opportunity from a pro such as yourself. It was getting a little frustrating.

I'll will look for that User look-up table and get back you Pete.

Have a good one.

Gennaro

PS: I am in California time.

 

[gennaroalpha7]

Hi-

There's a table called the Tracker table and the first 2 fields are the Login ID, followed by the trackers full name field.

Would my formula now be like this, and where would be the best place to place it?

(

{CallLog.Tracker} = {Asgnmnt.Assignee}, {Tracker.LoginID}

)

and

{Asgnmnt.AssignStatus} = "Active"


Thanks.

G.

 

[pmax9999]

Before you start you will need to verify that the LoginID field is in the same format as (and contains identical data to) the {CallLog.Tracker} field, and the Full_Name field is the same format as (and contains identical data to) the {Asgnmnt.Assignee} field.

You will then need to add the Tracker table and join it to the CallLog table on {CallLog.Tracker} to {Tracker.LoginID} field. Then, replace your existing Record Selection formula with the following Code:

Not ({CallLog.KeyWord} in ["FIS", "TP Admin Defect", "TP Capture Defect", "TP Sales Defect", "TP Service Defect", "TP Teller Defect", "TPSS Defect"]) and
{CallLog.CallStatus} <> "Closed" and
{CallLog.Tracker} in ["ANTONJ83", "CAMCACX1", "CHAVEV60", "COBBS53", "DONESC72", "FAIRLA01", "FLOREV02", "Heldet00", "HERNAE03", "HERREC86", "jauree01", "LIMM01", "mirelj91", "Perezm58", "phant27", "Poste54", "PRICER64", "QUEZAM01", "RIGORAD1", "salesc01", "VAZQUD01"] and
{Asgnmnt.AssignStatus} = "Active" and
{Asgnmnt.Assignee} = {Tracker.Full_Name}

Use the actual column names for {Tracker.Full_Name} and {Tracker.LoginID}.

Hope this helps.

Cheers
Pete

 

[gennaroalpha7]

In the new Record Selection formula above I don't see the {Tracker.LoginID} field?

Because in your comment..."Use the actual column names for {Tracker.Full_Name} and {Tracker.LoginID}".

Thx.

G.

PS: Please click the link to see the added table and links, please comment.

https://www.dropbox.com/s/b4q65wlhierzdfq/Service Desk Ticket Snap Shot 5.15.2014 v0.1.PNG

 

[pmax9999]

The first thing I would suggest is to remove all table links on Modified Date and Time. While it might work most of the time, it will almost certainly cause you problems occasionally. My guess is you have Smart Linking turned on - personally I always turn it off as I find it wastes more time than it saves.

Perusal of the column names clearly shows the "Full Name" field is {Tracker.FullName} and the "ID field" is {Tracker.LoginID}.

Therefore, you need to amend the Record Selection formula to:

Not ({CallLog.KeyWord} in ["FIS", "TP Admin Defect", "TP Capture Defect", "TP Sales Defect", "TP Service Defect", "TP Teller Defect", "TPSS Defect"]) and
{CallLog.CallStatus} <> "Closed" and
{CallLog.Tracker} in ["ANTONJ83", "CAMCACX1", "CHAVEV60", "COBBS53", "DONESC72", "FAIRLA01", "FLOREV02", "Heldet00", "HERNAE03", "HERREC86", "jauree01", "LIMM01", "mirelj91", "Perezm58", "phant27", "Poste54", "PRICER64", "QUEZAM01", "RIGORAD1", "salesc01", "VAZQUD01"] and
{Asgnmnt.AssignStatus} = "Active" and
{Asgnmnt.Assignee} = {Tracker.FullName}

The {Tracker.LoginID} is not required in the Record Selection. Have you checked the data in the Tracker table to ensure the ID and Full Name data will match the contents of the other tables?

Pete