How to create summary/running total with odbc Time fields

Posted on 2004-08-30
Last Modified: 2012-05-05

I am trying to make a report where all the hours (in a TIME field HH:MM:SS) of an employee are shown against the projects they have worked on. So for example, an employee can work 8 hours a day on a certain taks in a project.

In this report there is a list of each project and the tasks they did preform on it.
But i can't figure out how to summerize/add up the time records.
I have also made a special field trhough SQL where i translate the HH:MM:SS format to Seconds. But i still can't do it :-|

A project can extend the 23:59:59 Format. there are projects where there are more than 100 Hours.
How do i this, i am kind of new to Crystal Reports (especialy with Time calculations)
I hope somone know the answer ;-)

Best Regards,
Rick Beemsterboer
Question by:rbeemsterboer
  • 5
  • 3
  • 2
  • +1
LVL 42

Expert Comment

ID: 11930062

Can you give a little more information about your report?  I suspect you'll need to use the DateDiff('s',{start},{stop}) formula but I'm not sure what your data looks like and what you want the result to be.

For example, if you have just the ending time for each operation then the time spent on it would be calculated by comparing the ending time to the previous ending time with a formula like this ('s' gives results in seconds):


This is a very simple example though - with more details we can probably give more specific help.

LVL 10

Expert Comment

ID: 11930343
I will say accoridnt to what I understood

you create a group and grroup by project id

Then you get the time field and put it into your details section

You click on insert and running total and then
operation: summary
field to summarize: time field
on chnage of project name
reset; on change of every group

Click OK

You will get the total for each project . If want to get the grand total , you dont need to reset ever

Author Comment

ID: 11930493
Hi Emre,

The problem is that the program (version 8.5) doens't let you summarize/use running totals with the Time Field.
It is restricted. When I try it with a INT field for example it works fine....

I have to use formulas, that's for sure ( I think)
Because i can't use Running Totals for  each project i tried working with seconds. (see TimeSec)
So, i used "SELECT ProjectId, UserId, DateField, TimeField, TIME_TO_SEC(TImeField) AS TimeSec FROM .....") to work around that problem. But after that i don't know how to translate those seconds to a Time format

LVL 10

Expert Comment

ID: 11930524
ther eare lots of time and date functions in crytstal

time(datetime) converts the datetime field into time format(hh:mm:ss)
you ca cretae  a formula and get the running total ther etoo

There is dateadd function to add the dates. Or you can convert everything in seconds ge the totals then convert them into necessay format. Crystall help is very good to learn the date functions


Author Comment

ID: 11930688

I saw the list of functions. But those have there bounderies also.
For example. some functions don't go futher then 24:00:00, because it is limited to only one day.

And i want to show a running total for a whole project (sum al times that are under this project)
All the records for are nicely repeated and are shown nicely on the screen. But i have to have the totals too :-)
there are projects with more then 2000 hours, so a function that is limited to 24:00:00 doens't help

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

LVL 42

Accepted Solution

frodoman earned 500 total points
ID: 11930743
Can you please explain what it is that you want?  If you have the value in seconds and total it, are you just looking for how to translate from seconds into hours?

If that's the case you can't use a built-in Crystal function that I know of because of the boundaries you specify.  You can write your own formula though:

numberVar TotalSeconds := 8000000;
numberVar TotalHours := Int(TotalSeconds / 3600);
TotalSeconds := TotalSeconds - (TotalHours * 3600);
numberVar TotalMinutes := Int(TotalSeconds / 60);
TotalSeconds := TotalSeconds - (TotalMinutes * 60);
cstr(TotalHours,0) + ':' + cstr(TotalMinutes,0) + ':' + cstr(TotalSeconds,0);

LVL 100

Expert Comment

ID: 11930815
Crystal assumes time is a clock and therefore cannot exceed 24 hours or actuall 23:59:59.
There are 2 choices.
1.  Use the Crystal date-time funcstions and display it as days and hours

2. Create your own function something like (I don't have crystal here to get the exact syntax or the opertors)
Use your seconds caluculation

numbervar secs;
numbervar mins;
numbervar hrs;
numbervar temp;

hrs := trunc ({YourSecondsField} / 3600);
temp := {YourSecondsField} mod 3600;
mins := trunc(temp / 60);
secs := temp mod 60;
cstr(hrs) + ":" + cstr(mins) + ":" cstr(secs)


Author Comment

ID: 11939650
Hi Guys,

i've been trying out your solutions, but i'm getting stuck on an other part, where i first thaught it worked. (but it didn't!)
See this screenshot
When i typ SELECT TIME_TO_SEC('02:00:00') in my MySQL client , the result is 7200 (and this is correct).
But when i use this same SQL string in the Crystal SQL Designer/Crystal Reports i'm getting some strange stuff.
So my calculations with summing the seconds from the Time field are getting stuck on this.

We have to sum the timefields otherwise or the BUG must be fixed!!


Author Comment

ID: 11939837
I have reported the Bug, so i am cureius if they reply within a day,with a good answer!

Isn't there an other way to sume fields.
what if we manually substract/explode a time field? can this be done in CR?

Best Regards.
LVL 42

Expert Comment

ID: 11942092
I would try just bringing in the date field from your db and using datediff to calculate the seconds within Crystal.  Then you can sum up the seconds and use a formula like the ones we've provided to convert the sum back to a formatted time.

Please post back the response from B.O. - I'm curious what they will say...


Author Comment

ID: 11971058
I've tryed this, but it also didn't work
SELECT (HOUR(itl.Time)*3600) + (MINUTE(itl.Time) * 60) + SECOND(itl.Time) AS TimeSec

i can't use datediff because where are no dates to compare
all time values are entered manually into the GUI/DB

I hope B.O. Will respond to my service requrest, because i don't have a service contract or something like that.
We've purchased CR several yours ago...

Thanks for the advise.
As i understand the code that you made, i suspect it would work.
I'm going to work on something else know. leaving the C reports alone for a while (have had enough :-) )


Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now