Solved

Crystal Reports Totalling time and deducting time used.

Posted on 2009-03-31
5
607 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:roycasella
  • 2
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 350 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 100

Assisted Solution

by:mlmcc
mlmcc earned 150 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:roycasella
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:roycasella
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write 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 …
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

707 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

11 Experts available now in Live!

Get 1:1 Help Now