Go Premium for a chance to win a PS4. Enter to Win


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
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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 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!!


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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

885 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