Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to create summary/running total with odbc Time fields

Posted on 2004-08-30
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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

LVL 42

Accepted Solution

frodoman earned 1500 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 101

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…
Suggested Courses

670 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