Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Tips and Tricks
Message Icon Topic: time summary Post Reply Post New Topic
Author Message
Nenad
Newbie
Newbie
Avatar

Joined: 22 May 2008
Location: Yugoslavia
Online Status: Offline
Posts: 4
Quote Nenad Replybullet Topic: time summary
    Posted: 22 May 2008 at 6:07am
Hi !

How to insert summary for column @time ?

@time = DateTimeValue ({table1.column_A} - {table1.column_B})

table1.column_A = dd.mm.yyyy hh:mm:ss   (begining of driving)
table1.column_B = dd.mm.yyyy hh:mm:ss   (end of driving)


Report is for drivers and need to know numbers of hours spend on the road  for a month.


Thanx...
IP IP Logged
Nenad
Newbie
Newbie
Avatar

Joined: 22 May 2008
Location: Yugoslavia
Online Status: Offline
Posts: 4
Quote Nenad Replybullet Posted: 23 May 2008 at 4:47am

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")
IP IP Logged
Nenad
Newbie
Newbie
Avatar

Joined: 22 May 2008
Location: Yugoslavia
Online Status: Offline
Posts: 4
Quote Nenad Replybullet Posted: 23 May 2008 at 6:49am
Yes, this is it...

Hope to be helpful to others.

See Ya...
IP IP Logged
nicosiajl
Newbie
Newbie


Joined: 27 May 2008
Online Status: Offline
Posts: 2
Quote nicosiajl Replybullet Posted: 27 May 2008 at 7:43am
your solution is very good,
i did not understand exactly what you mean for 'Group',
Cristal Report indicates error in 'TotalSeconds',
instead of 'Group' what I have to make?

Sorry for English imperfect
     
Thank you for everything
IP IP Logged
Nenad
Newbie
Newbie
Avatar

Joined: 22 May 2008
Location: Yugoslavia
Online Status: Offline
Posts: 4
Quote Nenad Replybullet Posted: 27 May 2008 at 8:12am
This is not my solution... I just found this...



Edited by Nenad - 27 May 2008 at 8:12am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.