Working Fixed Analysis

[CIBS]

Hi

I' Using CR 9

Let me just just start by saying this is a wierd and a pain in the bottom and I need help as I have troied all diferent formula and have no luck.

I have in a nut shell 3 tables containg data

Table 1 - Call

Call_No
Call_Recieved_Date
Call_Close_Date

Table 2 - Event

Call_No
Person_ID
Start_Time
Stop_Time
Visit_No
Status

Table 3 - Person

Person_ID
First_Name
Surname
Area

Data that will be collected for a job

Job 1

Call No - Call_Received_Date - Visit_No - Status - Start_Time - Stop_Time - First_Name - Surname
2588 - 01/03/09 - 1 - Travel-To - 02/03/09 10:30 - 02/03/09 10:45 - Steve - Tomlin
2588 - 01/03/09 - 1 - Work - 02/03/09 10:45 - 02/03/09 11:00 - Steve - Tomlin
2588 - 01/03/09 - 1 - Closed Incomplete - 02/03/09 11:00 - Steve - Tomlin
2588 - 01/03/09 - 2 - Travel-To - 05/03/09 12:30 - 05/03/09 12:45 - Steve - Tomlin
2588 - 01/03/09 - 2 - Work - 05/03/09 12:45 - 05/03/09 15:00 - Steve - Tomlin
2588 - 01/03/09 - 2 - Closed Complete - 05/03/09 15:00 - Steve - Tomlin

Job 2

Call No - Call_Received_Date - Visit_No - Status - Start_Time - Stop_Time - First_Name - Surname
2589 - 01/03/09 - 1 - Travel-To - 04/03/09 10:30 - 04/03/09 10:45 - Steve - Tomlin
2589 - 01/03/09 - 1 - Work - 04/03/04 10:45 - 04/03/09 11:00 - Steve - Tomlin
2589 - 01/03/09 - 1 - Closed Complete - 04/03/09 11:00 - Steve - Tomlin

As you can see from the 2 jobs illustrated above that Job 1 required more then one visit where as job 2 only need the 1 visit

What I want it to do is have a parameter in place based on the "Start_Time" from table 2 and I want a formula to tell me is the job was 1st fix or not

for example if I run the report for 02/03/09 I will be presented with the following

Call No - Call_Received_Date - Visit_No - Status - Start_Time - Stop_Time - First_Name - Surname
2588 - 01/03/09 - 1 - Travel-To - 02/03/09 10:30 - 02/03/09 10:45 - Steve - Tomlin
2588 - 01/03/09 - 1 - Work - 02/03/09 10:45 - 02/03/09 11:00 - Steve - Tomlin
2588 - 01/03/09 - 1 - Closed Incomplete - 02/03/09 11:00 - Steve - Tomlin

this job is obviously not 1st fix as you can see from the original table so what I want to display is the following

Call No - Call_Received_Date - Visit_No - First_Name - Surname - 1st Fix
2588 - 01/03/09 - 1 - Steve - Tomlin - No

So I want it to hide the different status so that I just see the one and it be able to tell me if it's 1st fix or not.

I do know that this will be based on the "Visit_No" and "Status".

I have tried the following Formula

If {Event.Visit_No} = 1 and {Event.Status} = "Closed Complete" Then "YES" Else "NO"

This doesn't seem to work

so if I run the report for the period of March 2009 I should have some thing like the below

Call No - Call_Received_Date - First_Name - Surname - 1st Fix
2588 - 01/03/09 - Steve - Tomlin - No
2589 - 01/03/09 - Steve - Tomlin - YES

Please Help as this is really getting to me know, I know the answer is simple but there is always some thing that the above formula trips on

Kind Regards

Stephen

 

[jdemmi]

Couldn't you group on the call ID, then group on the Vist No and then place your data in the details section?

Then if place in the details secion I think your "1st Fix" formula should work.

Else can you display the results or the erros you're receiving?

-- Jason
"It's Just Ones and Zeros