Coding Help with Crystal 11

[samcarter1022]

I am trying to create a report that will show me ticket that are outside of the SLA times. Urgent tickets have a completion SLA of 6hrs and High tickets have a completion time of 4hrs.

The following is the code I have, however the issue seems to be in trying to get crystal to understand the hours. The duration is being printed in minutes, and no matter what variation I use to represent hours it seems to choose what it likes. This code results in two tickets obviouly under the SLA at 11 minutes and 23 minutes showing as out of SLA.

Any suggestions for help would be greatly appreciated.

AssignDuration Formula:
stringvar AssignDuration;

if {ASGNMNT.RESOLUTION} = "Open" then AssignDuration := totext(DateDiff("h", {@Assigned_ACK_DateTime}, CurrentDateTime))

else AssignDuration := totext((datediff ("n", {@AssignDateTime}, {@AssignResolved})));

AssignDuration

SLANotes Formula:
stringvar SLANotes;

if {CALLLOG.PRIORITY} = "1-Urgent" then

(
if {@AssignDuration} < "4" then (SLANotes := "Within SLA")
else SLANotes := "Out of SLA"
)

else

if {CALLLOG.PRIORITY} = "2-High" then

(
if {@AssignDuration} < "6" then (SLANotes := "Within SLA")
else SLANotes := "Out of SLA"
);

SLANotes

Thanks,

Kerry.

[jggtz]

This is not the solution to your problem, but it will help you

How do you create a formula in Crystal Reports (CR) that adds more than
one time field and displays the total in the same format: hh:mm:ss?

For example:
1:45:01 + 1:45:01 should display 03:30:02 and not 2:90:02

Solution
In order to get the sum of more than one time field and to display the
total of the time field in the same format (hh:mm:ss) you must complete
the following steps:

A. Convert all the time fields to a common time unit such as seconds
B. Calculate the total time in seconds
C. Convert the seconds back to hh:mm:ss format.


A.-Convert time fields to seconds
--------------------------------
Convert the time fields to seconds so that a common time unit is used to
sum up all three different time units.

If your field is a datetime field, complete the following steps:
1. Create a new formula and call it @ConvertTimeToSeconds
2. To convert the datetime field to seconds, create a formula similar to
the following:

local numbervar hours;  
local numbervar minutes;  
local numbervar seconds;  
 
// Convert the hours to seconds by multiplying by 3600  
hours := hour({@time}) * 3600;  
 
// Convert the minutes to seconds by multiplying by 60  
minutes := minute({@time}) * 60;  
seconds := second({@time});  
 
//add up all the seconds  
hours + minutes + seconds;

If your field is string field with the format of hh:mm:ss to convert to
seconds complete the following steps:
1. Create a new formula and call it @ConvertTimeStringToSeconds
2. Create a formula similar to the following to convert the string field
to seconds:

local numbervar hours;  
local numbervar minutes;  
local numbervar seconds;  
 
// Parse out the hours portion of  the string and multiply by 3600 to convert to seconds  
hours := tonumber({timestringfield}[1 to 2])* 3600;  
 
// Parse out the minutes portion of  the string and multiply by 60 to convert to seconds  
minutes := tonumber({timestringfield}[4 to 5]) * 60;  
 
// Parse out the seconds  
seconds := tonumber({timestringfield}[7 to 8]);  
 
// Add up all the seconds  
hours + minutes + seconds;

B.-Calculate the total time in seconds
------------------------------------
Create a summary formula that will sum the
@ConvertTimeToSeconds or @ConvertTimeStringToSeconds
1. Create a new formula and call it @TotalSeconds
2. To sum up either formula, create a formula similar to the following:
[Code]
sum(@ConvertTimeToSeconds, Group)
- OR -
sum(@ConvertTimeStringToSeconds, Group)


C.-Convert the seconds back to hh:mm:ss format.
--------------------------------------------
Create a formula that converts the @TotalSeconds results back to
hh:mm:ss format:
1. Create a new formula and call it @ConvertTotalSeconds
2. To convert the results from @TotalSeconds back to hh:mm:ss format,
create a formula similar to the following:

local numbervar RemainingSeconds;  
local numbervar Hours ;  
local numbervar Minutes;  
local numbervar Seconds;  
 
//divide the @TotalSeconds by 3600 to calculate  hours. 
//Use truncate to remove the decimal portion.  
Hours := truncate({@TotalSeconds} / 3600);  
 
// Subtract the hours portion to get RemainingSeconds  
RemainingSeconds := {@TotalSeconds} - (Hours * / 3600);  
 
// Divide RemainingSeconds by 60 to get minutes. 
//Use truncate to remove the decimal portion.  
Minutes := truncate(RemainingSeconds/60);  
 
// Subtract the Hours and Minutes and what is left over is seconds.  
Seconds := {@Totalseconds} - (Hours * 3600) - (Minutes * 60);  
 
// Format the hours, minutes, and seconds to hh:mm:ss  
totext(Hours,"00") + ":" + totext(Minutes,"00") + ":" + totext(Seconds,"00")

[samcarter1022]

thanks jggtz, I'll give this a try.

[BrianL]

Is there a reason you couldn't just accept minutes as the format for the AssignDuration, and change the SLANotes formula to:

if {CALLLOG.PRIORITY} = "1-Urgent" then

(
if {@AssignDuration} < "240" then (SLANotes := "Within SLA")
else SLANotes := "Out of SLA"
)

else

if {CALLLOG.PRIORITY} = "2-High" then

(
if {@AssignDuration} < "360" then (SLANotes := "Within SLA")
else SLANotes := "Out of SLA"
);

SLANotes

I understand wanting to make the code work properly, but this seems like a simpler answer to me.