Displaying The 1st Person In A Multiple Visit

[CIBS]

Hi

I Am Using CR9 (Soon To Be CR10) To Pull Data From A Oracle Database.

What I want to archive is a report that tells my the 1st fix YES/NO and who the 1st person to deal with the issue.

The informaion is coming from the the following fields/Tables

Call.CallNo (This is the Call Number, Number Field)

Job.ID (This is a Field Displaying A-Z Depending on the Issue being closed and if reopened, String Field)

Job_Event.ResponseNo (This is the number of attempts at resolving the issue, Number Field)

Person.PersonID (The Name Of The Person Dealing With A Issue, Number Field)

Job.Status (This is the Status field, Number Field)

The status has no's reprosenting actions son on the report that I willbe greating I will be putting a If formula in to convert to a string value but there is about 20 different staus that it can be

for exmaple I have a table like the below (1st Fix Field Is A Formula using SQL Expression, I am still only basic in SQL though)

Data I Have

CallNo ID ResponseNo PersonID Status 1st Fix
3487 A 1 56 0 YES
5631 A 1 67 1 NO
5631 A 2 56 0 NO
5631 B 1 55 0 NO
5678 A 1 22 0 YES


What I hope to achive

CallNo ID ResponseNo PersonID Status 1st Fix
3487 A 1 56 0 YES
5631 A 1 67 1 NO
5678 A 1 22 0 YES


what I am currently getting

CallNo ID ResponseNo PersonID Status 1st Fix
3487 A 1 56 0 YES
5631 A 1 1 NO
5678 A 1 22 0 YES


As you can see the problem I am having is getting a formula to display the Person ID based on the ID and Status fields

If Anyone could please help with then

Kind Regards

Stephen

 

[Gramm]

I am not sure I am following you.... is the problem that there is no data for the Person ID for callno 5631?

 

[CIBS]

what is happening is that with there being more then one entry and it not being a 1st fix it leaves blank which is a pain what I want it to do is no matter if it's a 1st fix or not look for the 1st person to deal with it and display there ID.

Hope that a little more clearer

Kind Regards

Stephen

 

[lbass]

You could group by call number, sort by {%1stFix} in descending order, and then place all fields in the group header and suppress the detail and group footer sections.

-LB

 

[CIBS]

Hi

Tried that but unfortunately when there has being more then one person dealing with the issue the person ID field is blank in the report

Stephen

 

[lbass]

If you sort descending the blank field would sort second, and not appear in the group header. Why is it blank in the first place? Do you have some sort of suppression on the personID field? Or is the person ID field actually a formula?

-LB

 

[CIBS]

I am using a formula at the moment that is like below

if (trim({Job.ID})="A" and {Job_Event.ResponseNo}=1 )
then {Person.PersonID}

Kind Regards

Stephen

 

[lbass]

You don't really need to use that formula (although it doesn't hurt), but you do need to sort by {%1st} in descending order. Please follow the steps I suggested above.

-LB