Calculating Response Compliance Percentage 4

[KalebsDad78]

Good Morning all,

I am trying to calculate my ambulance service response compliance percentage using Crystal XI w/ an ODBC Database. I already have a report created showing response times (which is a formula and showing an average response for each city/county in the Group Footer).

I'm not real sure though how to write a formula to tell me what percentage of calls in each city/county were under 8 minutes & 59 seconds versus the overall total amount of calls for the group.

I have included a screenshot of what I have so far if that helps you.

Thank you in advance for your help.

 

[CoSpringsGuy]

Is response time a field from your database? If it is, what type of field is it? date/time? string? If not, can you show the calculation for the field?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[KalebsDad78]

It's not a field in the database. I created this field using a formula subtracting at scene time/date from call taken time/date.

I can show the calculation if you need it. I'm just on my laptop at the moment but I can get back to my desktop shortly if it will help.

 

[lbass]

Yes, you need to show the formula. And do you have both a county group and a city group or is it one group?

-LB

 

[KalebsDad78]

Here is the "Response Time" formula:

//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 ) );

// 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 )

)

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 )
)

I have a "City" group & a County group as well.

 

[lbass]

You need to show the content of {@Date Time At Scene} and {@Date Time Received}.

-LB

 

[KalebsDad78]

Date Time At Scene:
//Creates a Date Time field for Crystal
{@At Scene Date}+{@At Scene Time}

Date Time Received:
//Creates an actual Crystal DateTime field for Call Received
{@Received Date}+{@Received Time}

At Scene Date:
//Converts the atsdate field (string) into an actual date field
Date(ToNumber(Left({trips.atsdate},4)), ToNumber(Mid({trips.atsdate}, 6,2)), ToNumber(Right({Trips.atsdate},2)));

At Scene Time:
//Converts the atstime field (string) into an actual time field
Time (ToNumber(Left ({Trips.atstime},2)), ToNumber(Mid({Trips.atstime},4,2)), ToNumber(right({Trips.atstime},2)));

Received Date:
//Coverts the calldate field (string) into an actual date field for Crystal.
//This field is the date that the trip was saved in RightCAD
Date(ToNumber(left({Trips.calldate},4)), ToNumber(mid({Trips.calldate},6,2)), ToNumber(right({Trips.calldate},2)))

Received Time:
//Coverts 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)));

 

[CoSpringsGuy]

Lbass or someone else may have a better way but create a formula to flag or give a 1 value for each record where response time was less than 9 minute.

@less_than_nine
if (((({@Date Time At Scene} - {@Date Time Received})*24)*60)*60)
<540 then 1

then another formula for city the city group
@percent_city
sum({@less_than_nine},{@citygroup})
%
count({run#},{@citygroup})

put that in your city group footer....

create another using the county group


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Create formulas like this:

//{@SecsShortResponse} (assuming you mean less than 9 minutes):
if ({@Date Time At Scene} - {@Date Time Received})*86400
< 540 then 1

//{@City%} for the City group:
sum({@SecsShortResponse},{table.city})% count({table.run#},{table.city}) //assuming run# is a distinct ID

//{@County%} for the County group:
sum({@SecsShortResponse},{table.county})% count({table.run#},{table.county})

//{@All%} for the Report Total:
sum({@SecsShortResponse})% count({table.run#})

This assumes that you don't have duplicate data, i.e., that there is one row per run#.

-LB

 

[KalebsDad78]

Thank you guys for your help so far...

CoSpringsGuy - When I get to the point of creating the @percent_city formula, it tells me, "This formula cannot be summarized."

All: To obtain whether the call was "city" or "county", I just created a formula to do a wildcard search for the dispatch zone containing "City of" in the zone so there is not a specific table deciphering whether the call was city or county so I think that hinders a few of the formula's.

Again, thank you for helping me with this. I greatly appreciate it.

 

[lbass]

Sorry CoSpringsGuy--I didn't see your post. We suggested the same thing.

-LB

 

[lbass]

You should be able to summarize your formulas--unless you substituted your response time formula for CoSpringGuy's suggestion. Please try as suggested. Note also that you should remove any "whileprintingrecords" at the beginning of these formulas.

-LB

 

[KalebsDad78]

Ok guys,

The information has worked out great so far. Now, how would I get a percentage for "Life Emergency" calls (8:59 or less response time), which is one of my "Call Type" fields and then a percentage for "Priority 1" (P/1) calls which is another "Call Type". (See screenshot above if necessary)

Our contracts are based on these two types of call types. I've been able to obtain the total compliance percentage from the information above and I can run separate reports I guess and change the call type I'm looking at but I would like the report to show in each Group Footer the compliance % for both.

I hope I'm not asking too much but this is a critical report for my company and I appreciate your time in helping me.

 

[KalebsDad78]

I forgot to include - P/1 call types are required to have a 10:59 response within our city limit boundaries.

 

[CoSpringsGuy]

you can create a flag for any of those scenarios and then use another formula that uses the sum and/or count function to get your desired results.

your flag formula might look like this:
//@Life_Emergency_Flag
if (call_type} = "Life Emergency" then 1

//@Life_Emergency_Flag_short_Response
if (call_type} = "Life Emergency" and (((({@Date Time At Scene} - {@Date Time Received})*24)*60)*60) <540 then 1

//@Life Emergency Short call percent
sum({@Life_Emergency_Flag_short_Response},{@countrygroup})
%
sum(@Life_Emergency_Flag},{@countrygroup})


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[KalebsDad78]

These all worked great. Thank you for the excellent help!

 

[KalebsDad78]

So, I've got all of the formula's working but I created a group to separate calls by month and the "City%" formula I am using and I moved it to the group footer but it still gives me the total percentage and not the monthly percentage.

What am I missing?

 

[lbass]

You need a new formula instead, where you replace the group condition (city) with the date field, as in:

sum({@SecsShortResponse},{table.date},"Monthly")% count({table.run#},{table.date},"Monthly")

-LB

 

[KalebsDad78]

It says a "Group Condition is not allowed here" and highlights the "Monthly" section in the first part of the statement.

 

[KalebsDad78]

And "Monthly" was replaced with "@Cdate" so is that the problem? My monthly group is created by a formula - cdate{trips.tdate} which allows me to do the grouping that I want.