• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 871
  • Last Modified:

How to create summary/running total with odbc Time fields


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
  • 5
  • 3
  • 2
  • +1
1 Solution

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.

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
rbeemsterboerAuthor Commented:
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

rbeemsterboerAuthor Commented:

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

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);

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)

rbeemsterboerAuthor Commented:
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 http://www.it-projects.org/CrystalSQLDesigner.jpg
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!!

rbeemsterboerAuthor Commented:
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.
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...

rbeemsterboerAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now