Solved

How to create summary/running total with odbc Time fields

Posted on 2004-08-30
11
859 Views
Last Modified: 2012-05-05
Hi,

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
Netherlands
0
Comment
Question by:rbeemsterboer
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 11930062
Rick,

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

   DateDiff('s',Previous({table.endingTime}),{table.endingTime});

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

frodoman
0
 
LVL 10

Expert Comment

by:ebolek
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
Regards
Emre
0
 

Author Comment

by:rbeemsterboer
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



0
 
LVL 10

Expert Comment

by:ebolek
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
Regards
Emre


0
 

Author Comment

by:rbeemsterboer
ID: 11930688
Yes,

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

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 42

Accepted Solution

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

frodoman
0
 
LVL 100

Expert Comment

by:mlmcc
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)

mlmcc
0
 

Author Comment

by:rbeemsterboer
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!!



0
 

Author Comment

by:rbeemsterboer
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.
Rick
0
 
LVL 42

Expert Comment

by:frodoman
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...

frodoman
0
 

Author Comment

by:rbeemsterboer
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 :-) )

Regards,
Rick
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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 …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

22 Experts available now in Live!

Get 1:1 Help Now