|
I found something...
How to get the sum of multiple time fields in Crystal
Reports
The information in the article refers to:
Seagate
Crystal Reports 8
Applies to:
Reported version and higher
summing time fields
converting seconds to hours, minutes, seconds
Synopsis
A report contains a time field that displays in hh:mm:ss
format.
How do you create a formula in Crystal Reports (CR) to get the sum
of more than one time field
and to display the total of those time fields 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 fields in the
same format (hh:mm:ss), you must complete the
following steps:
1. Convert all the time fields to a common time
unit such as seconds
2. Calculate the total time in seconds
3. Convert the seconds back to hh:mm:ss format.
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;
To convert the string field, with the format of
hh:mm:ss to seconds create a formula similar to the
following:
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;
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:
sum(@ConvertTimeToSeconds, Group)
- OR -
sum(@ConvertTimeStringToSeconds, Group)
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")
|