Calculate average elapsed times for statuses selected at runtime

[tweetie7]

Hi

I am using Crystal 2008, Crystal Reports Server 2008, Oracle 11g.

I am writing a report to display the elapsed times between different statuses for an incident. There are 10 different statuses. The user wants to be able to select the statuses to start and end the elapsed time calculations and this will be done by selecting a Start Timer and End Timer parameter value at run time.

After extracting and displaying the incident elapsed times, I need to display the average elapsed times between the statuses for the incidents for the timers in the timer range selected (if the status is before of after the statuses selected, then don't display the elapsed time calculations for those statuses).

Average Response Times (Start Timer:Open to End Timerroceeding)
Metro Nth Region
Min 5.94 Max 52.59
Pre-Create Open Unviewed Viewed Allocated Proceeding At Scene On Air Finish Close Total
Average x x 4.2 4.31 15.4 0.03 x x x x 23.94

I have created formulas to determine the start timer and the end timer, and to work out how many timers in between these using the paramater values selected, eg (T1,T2,T3,T4...):

@Start_Timer_Value
if ({?Start Timer} = "T1" then table.Precreate_time
else if ({?Start Timer} = "T2" then table.Open_time
else if ({?Start Timer} = "T3" then table.Unviewed_time
.
.
.
@Timer_Calc_Number
ToNumber (Right({?End Timer},1)) - ToNumber (Right({?Start Timer},1))

I'm not sure how to write a formula that will take the selected start timer and calculate each elapsed time for each status change until the selected end timer. Is anyone able to help?

Thanks

 

[Madawc]

Use DateDiff, it will handle time

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[tweetie7]

Hi Madawc

Thanks, but I know how to work out the time differences between 2 timers, but I can't work out how to write a formula to work out the times using the Start Timer and End Timer parameter values selected when the report runs without trying to write a huge if statement for every permutation, eg if Start Timer selected is Queue_time and End Timer is Create Time then the formula would be something like:

if ?Start_timer = "QUEUE" and ?End_timer = "CREATE"
then
Time_Elapsed_1 = DateDiff ("n", table.Queue.time, table.Answer_time)
Time_Elapsed_2 = DateDiff ("n", table.Answer_time, table.create_time)
Total_Elapsed_time = DateDiff ("n", table.Queue_time, table.Create_time)
...
but this would only cover 1 situation, would I have to write similar for each timer difference event, eg time between Queue time and Answer time, times between Queue time and Create time, times between Queue time and Allocated time, times between Queue time and Proceeding time, etc, etc, then do the same for time between Answer time and Create time, times between Answer time and Proceeding time, and so on?

I just thought there might be some way of reading in the Start Timer parameter value and working out the time differences for each time 'step' until the End Timer parameter value is reached.
You would end up with a result similar to:

Start Timer = "Queue_time"; End Timer = "At Scene"

Incident Queue Answer Create Proceeding At Scene...Total
(Time) (Minutes elapsed since start)
12345 08:30am 0.32 1.20 5.25 22.39 29.56

...................................................
Start Timer = "Create_time"; End Timer = "On Air"

Incident Queue Answer Create Proceeding At Scene On Air Total
(Time) (Minutes elapsed since start)
12345 - - 8:31:52 5.25 22.39 15.30

Hope this makes sense.

 

[Madawc]

You might have to do it the long way. You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer. I assume this also applies to Crystal 2008/


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Madawc]

PS. You could have one formula field to check the possibilities for a start tune, another for the end. Then find the difference between the two.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

You should be able to create datediff formulas for each step, like this and place them in the detail section:

//{@prectoopen}:
datediff("s",{table.precreatetime},{table.opentime})

//{@opentounviewed}:
datediff("s",{table.opentime},{table.unviewed})

//etc.

Insert averages on these at the report level in RF_a and then suppress the detail section. Insert a series of Report Footer sections b to ?, and copy the values from RF_A into each, removing the first summary on the left each time, and moving all summaries to the left to fill the space.

Conditionally suppress each section based on the beginning parameter. Then format each field (not section) to conditionally suppress based on the end parameter.

-LB