Sum Time in a Cross-Tab

[mulligan2003]

I am trying to create a cross-tab report but the summarized field is TIME IN SECONDS. I need to return in HH:MM but when I try any formula's they will not let me SUM them as I used to totext command in the formula.

Does anyone know of a way to sum time in seconds and then convert to HH:MM in a cross-tab report? Any help would be great!!

 

[Reebo99]

To SUM the results you would have to convert them to a numeric field, possibly CDbl ? With regard to showing seconds as HH:MM, in the past I have used :

dateadd("s",{secondstotal},Datetimevalue(0000,00,00,00,00,00))

This only works if your result is guaranteed not to exceed 24 hours. If you cannot guarantee this for the future then (sorry all you crystal syntax people)in Basic Syntax:

Dim Hours as number
Dim Minutes as number
Dim Seconds as number

Hours = truncate({@SecondTotal}/3600)
Minutes = truncate({@SecondTotal}/60) - (hours*60)
Seconds = {@SecondTotal} - (hours*3600) - (minutes*60)

formula = cstr(hours,0,"&quot&":"&cstr(minutes,0,"&quot&":"&cstr(seconds,0,"&quot

If anyone out there knows a better way, then please let me know, because I use this calc alot. Also, if someone could tell me in Crystal Syntax, then this would be appreciated also. Hope this helps.

Reebo
Scotland (snowed in)

 

[kenhamady]

You can't sum text, and can't change the display of totals in a cross-tab by writing formulas.

If you can use a fixed number of columns you can use a manual cross-tab, which will allow you to take your sum of seconds, and then write a conversion formula for each one.

see faq149-243. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com