x
Solved

# Crystal Reports Totalling time and deducting time used.

Posted on 2009-03-31
Medium Priority
625 Views
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.

R
0
Question by:Romolo
• 2
• 2

LVL 77

Accepted Solution

peter57r earned 1400 total points
ID: 24028877
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

LVL 101

Assisted Solution

mlmcc earned 600 total points
ID: 24029142
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

LVL 7

Author Comment

ID: 24029209
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

LVL 77

Expert Comment

ID: 24029254
Yes.
0

LVL 7

Author Comment

ID: 24961887
This worked really well.
Sorry for the late close

Romolo
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

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…
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 …
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …
###### Suggested Courses
Course of the Month9 days, 13 hours left to enroll