How do I create this Formula? 3

[thermalman]

Hi, I want to calculate how many Calls we Missed Fix on based on WorkOrders.TimeCompleted being Greater than LoggedCalls.Timeout where the WorkOrders.WO_No is the Maximum. i.e.

I want it search for the Maximum WO_No and take that Time Completed and measure against the TimeOut.

Is this possible to do for the whole report?


Many Thanks
Thermalman

 

[MisterMo]

Could you give a bit more details eg Database CR Version, Number of tables involved

I could assume that WorkOrders has many WO_No
eg.
WorkOrders WO_No
1 1
1 2
1 3
2 1
2 2
and so on

and then I could assume that the WO_No is linked to the logged calls and fixed or missed stuff

on this assumption is possible to do this from either CR or SQL.


Mo

 

[thermalman]

Sorry MisterMo, I am using CR Ver 10

I have 2 Tables LoggedCalls,WorkOrders

The are linked by GBR_No (CallNo)this is because 1 call can have many WorkOrders

The Data I am trying to extract is based on calculting the Difference between (WorkOrders.TimeCompleted)being Greater than (LoggedCalls.TimeOut) where the WorkOrders.WO_No is the maximum.

For instance:-

GBR_No WO_No TimeOut TimeCompleted Fol/Up

12345 1 01/01/2005 09:00 31/12/2004 13:35 =1
12345 2 01/01/2005 09:00 01/01/2005 09:35 =1
12345 3 01/01/2005 09:00 02/01/2005 10:00 =0

So looking at this example, I want to extract only the TimeCompleted from the WO_No 3(can be any number)where TimeCompleted is > than the TimeOut.

so this should give me:-

GBR_No WO_No TimeCompleted TimeOut
12345 3 02/01/2005 10:00 01/01/2005

The TimeOut does not change for any of the Workorders.

I hope this clarifies what I am trying to achieve, Any problems please let me know.

Regards
Thermalman

 

[Madawc]

You could do a running total, Evaluate using a formula. This could be WorkOrders.TimeCompleted) > (LoggedCalls.TimeOut) - Crystal is very good at compairing dates.

You could also use DateDiff to find the exact difference, if you need that.

Madawc Williams (East Anglia, UK)

 

[MisterMo]

Create a group for GBR_No

place the GBR_No field on the group footer and then right click on the other fields choose inset>Summary select maximum and the group that you have just created.

suppress the detail section.


this should display the dat you want.



Mo

 

[thermalman]

Thanks Madawc

surely this will give me all records and not just the TimeCompleted for the last WO_No that is Greater than the TimeOut?

Thanks
Thermalman

 

[Madawc]

I thought that was what you wanted. Just try it and see what you get. Experiment a bit, to get yourself familiary with Crystal, which is a report tool rather than a language.

Madawc Williams (East Anglia, UK)

 

[CR85user]

You will possibly need to pull all records in order to then suppress all but the maximum for each GBR_no

Try with just select criteria of {workorders.wo_no} > 0 then group on GBR.no and suppress where

not(WorkOrders.TimeCompleted > LoggedCalls.Timeout)

You can also then choose several ways to show only the last wo_no for each gbr_no depending on your needs ( A simple Workorders.wo_no < maximum(Workorders.wo_no) might suffice)

Good luck - J