Crystal Reports 11 for RescueNet Dispatch - Average Response in Time 1

[Edearl1]

I am having trouble writing a formula to figure the average response times in minutes for my zones. We figure reponse times from call taken to at scene. I would like to be able to figure the response times for each individual call then the average of all the call per zone with an overall average for all zones. My second question is how do you get crystal to show what the numerical value of the zone is, example: 1 = None, 22 = K3. I have tried if then else statements but the don't work. I have looked at several of the postings on this site and thought I found one that would do what I needed but it states "remaining text does not appear to be part of the formula" Any help would be appreciated.

 

[Turkbear]

Hi,
More info is needed:

How are the times stored in your data records?
What did you try that did not work when decoding the zones?

By grouping your report on Zone you should be able to compute the times and average time, depending on your data structure.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Edearl1]

Turkbar,
This is the formula I found on this site under another thread. Everything shows the formual is ok until it gets to the section below //display dd-hh:mm:ss and state the remaining text does not appear to be part of the formula. As far as I know the data is in time form.

//This is the display of the response time in the (Day,hr,min,sec) dd-hh:mm:ss format

whileprintingrecords;
NumberVar totalsecs:=(((({@Date Time At Scene}-{@Date Time Received})*24)*60)*60);
Numbervar dd:=0;
Numbervar hh:=0;
Numbervar mm:=0;
Numbervar ss:=0;

//for trips that never got on scene
If {Trips.atsdate} = "1900-01-01" then totalsecs:= 0 else totalsecs:=totalsecs;

If totalsecs < 0 then
(
//for trips that had a negative response time
//Days
If totalsecs <= -86400 then (dd:=Truncate (totalsecs/86400,0); totalsecs:=totalsecs - (dd*86400));
-(dd*86400));

//Hours
If totalsecs <= -3600 then (hh:=Truncate (totalsecs/3600,0); totalsecs:=totalsecs-(hh*3600));

//minutes
If totalsecs <= 60 then (mm:=Truncate(totalsecs/60,0); totalsecs:=totalsecs-(mm*60));

//Seconds
ss:=totalsecs;

If dd=0 then
//display hh:mm:ss
ToText(hh,"00",0)+ "-" + ToText(mm,"00",0)+ ":" + ToText(ss, "00",0)

//display dd-hh:mm:ss
ToText(dd,"00",0)+ "-" + ToText(hh,"00",0)+ ":" + ToText(mm,"00",0)+ ":" + ToText(ss,"00",0)
)

Else

(
//for trips with a normal response time
//Days
If totalsecs>= 86400 then (dd:=Truncate (totalsecs/86400,0); totalsecs:=totalsecs-(dd*86400));

//Hours
If totalsecs>= 3600 then (hh:=Truncate (totalsecs/3600,0); totalsecs:=totalsecs-(hh*3600));

//Minutes
If totalsecs>= 60 then (mm:=Truncate (totalsecs/60,0); totalsecs:=totalsecs-(mm*60));

//Seconds
ss:=totalsecs;

If dd=0 then
//display hh:mm:ss
ToText(hh,"00",0)+ ":" + ToText(mm,"00",0)+ ":" + ToText(ss,"00",0)

else

//display dd-hh:mm:ss
ToText(dd,"00",0)+ "-" + ToText(hh,"00",0)+ ":" + ToText(mm,"00",0)+ ":" + ToText(ss,"00",0))

What I am wanting to do is get the average for response times from {trips.calltime} - {trips.astime}. I have tried average {trips.calltime}-{trips.astime} and it gives me an error that a number is expected in the calltime & astime. I may be mistake about the form the data is in. I did find another formula on her that converted the calltime field(string) into an actual time here is that formula

//Converts the calltime field (string) into an actual time field for Crystal.
//This field is the time that the trip was saved in RightCAD
Time(ToNumber(Left({Trips.calltime},2)), ToNumber(Mid({Trips.calltime},4,2)), ToNumber(right({Trips.calltime},2)))

I also had to do it for the astime (at scene time). Is there another formula I need or am I just not doing something right?

 

[Edearl1]

Oh I forgot to say I figured out the zone name issue.

 

[lbass]

You have a missing "else" in your formula, but you can't do an average on a string. You should be using fields converted to datetime, and then use:

datediff("s",{@datetime2},{@datetime1})

...where the second datetime is the most recent. Then you can average this formula. If you want to convert the result to a string, there is a faq for that: fag767-3543.

How could there be a negative response time--logically doesn't make sense.

-LB

 

[Edearl1]

Thank you, I will see if this works.

 

[lbass]

That (obviously) should have been: faq767-3543.

-LB

 

[Edearl1]

Thanks to all you posted replies to my questions. I finally have the response times figured out. My question is how do I get it to give an average response time for each zone then an overall average response time for all zones? I have been trying to find anything about this on here but have had no luck. Any help is greatly appreciated. All my other reports are doing good at this time.

 

[lbass]

You would use a formula that averaged the datediff at the grand total level, instead of at the group level. Then you would convert it to the string format as you did for the group.

-LB