[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

Crystal Reports Totalling time and deducting time used.

Hey

My report required totalling time values past 24 hours and the source field is varchar sql 2000 8 characters and allows nulls.

I will have values listed 3 or 4 records of 99:00:00.

I need to total these records and then deduct a time value that I have pulled from a subreport.

There will also be a record that will have a null in this field in addition to the ones I wish to sum.

Total time - Time Used.

287:00:00
or 287:30:00

deduct 125:30:00

ALSO if you can answer separately how can crystal actually store timevalues larger than 23:59:59.

My current deduction value is only 15 hours for this test. But will immediately need to store larger.

Many thanks in advance

R
0
Romolo
Asked:
Romolo
  • 2
  • 2
2 Solutions
 
peter57rCommented:
You cannot display times >=24 hrs from a date/time field.  Hours over 24 are converted to days.
So you have to convert the total into a text field which can display the number of hours.

However, before that you have to convert each time value to seconds and sum the number of seconds.
You then convert the total  to the format you want to see.

So you need a formula field to get the seconds in each record:. This can go into the detail section if you want to seeit.
//MySecs

if isnull({table.timefield}) then
0
else
hour({table.timefield})*60*60
+Minute({table.timefield})*60
+second({table.timefield})

You can then create a formula field to get the total and convert it back again...This goes in the report footer
//CvrtTotal
numbervar tot;
numbervar tothrs;
numbervar totmins;
numbervar totsecs;

tot:=Sum ({@mysecs});

TotHrs := tot\ 3600;
Tot:= tot mod 3600;
TotMins := Tot\ 60;
TotSecs := Tot mod 60;
ToText(TotHrs,0) & ':' & ToText(TotMins,0) & ':' & ToText(TotSecs,0)

0
 
mlmccCommented:
Agree.  Time fields are just that, clock time and cannot get larger than 23:59:59.

The above should work for you.  If seconds will always be 0 you could work with minutes.

Also if the hours field could get larger than 1000 you might want to use this minor change to wliminate the ,

ToText(TotHrs,0,'') & ':' & ToText(TotMins,0) & ':' & ToText(TotSecs,0)

mlmcc
0
 
RomoloIT Professional ConsultantAuthor Commented:
Sp you are suggesting to convert both into seconds and number values
perform the deducton and then calculate hours / mins / seconds as a text field.
Thanks
 
R
0
 
peter57rCommented:
Yes.
0
 
RomoloIT Professional ConsultantAuthor Commented:
This worked really well.
Please accept my apologies. I had not seen it not accepted.
Sorry for the late close
 
Romolo
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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