Total/Subtotal Time Value 1

[MrTBC]

Crystal Reports v8.5, Progress Database.

I have a time value which is a duration (in seconds) which I am trying to report in the format HH:MM:SS using formulas to format the field {TIMEVALUE}.

I have two levels of grouping - 'Staff Member' and 'Department', with the Detail section suppressed.

My subtotals by the two Groups are correct, but the Grand Total is displaying the wrong value.
All are correct when the actual field {TIMEVALUE} is inserted, but when I insert formulas to format these figures, the Grand Total in the Report Footer is incorrect.

My Formulas are:
Group by Staff Member:
TimeSerial(0,0,Sum({TIMEVALUE},{STAFFKEY}))

Group by Department:
TimeSerial(0,0,Sum({TIMEVALUE},{DEPTKEY}))

Grand Total:
TimeSerial(0,0,Sum({TIMEVALUE}))

These are the kind of values I receive:

Group by Staff Member:
e.g.
FIELD = 20,281 FORMULA= 05:38:01 (both correct)

Group by Department:
e.g.
FIELD = 44,453 FORMULA= 12:20:53 (both correct)

Grand Total:
FIELD = 128,214 FORMULA= 11:36:54 (formula result is wrong -should be about 36 hours)

Thanks very much for any help you can give me guys.

 

[Reebo99]

Have you tried using Synapsevampire's FAQ faq767-3543 ?

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[MrTBC]

Thanks Reebo, but I am trying to Sum Time Values, rather than calculate the difference between them.

 

[Reebo99]

The concept of viewing the result in a HH:MM:SS format is still the same. Try using:

numberVar dur := Sum({TIMEVALUE})
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(dur/60)/60);
min := Remainder(Truncate(dur/60),60);
sec := Remainder(dur,60);

hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");

hhmmss


Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[MrTBC]

Thanks Reebo! That worked like a dream. The Total is now correct while all of the subtotals are still the same.

I don't know what I'd do without this site!

 

[Reebo99]

I'd like to pass on the thanks to SV for the FAQ.....

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[MrTBC]

Good point. I've just given it a top rating.