Solved

Crystal Reports Totalling time and deducting time used.

Posted on 2009-03-31
5
608 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
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 video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

919 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

13 Experts available now in Live!

Get 1:1 Help Now