Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Crystal Reports Totalling time and deducting time used.

Posted on 2009-03-31
5
Medium Priority
?
615 Views
Last Modified: 2012-06-27
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
Comment
Question by:Romolo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 77

Accepted Solution

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

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

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

by:peter57r
ID: 24029254
Yes.
0
 
LVL 7

Author Comment

by:Romolo
ID: 24961887
This worked really well.
Please accept my apologies. I had not seen it not accepted.
Sorry for the late close
 
Romolo
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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