Crystal Reports, Sum of DateTime field, and group by User

0
votes

I want to calculate sum of field (DateTime) in Crystal Reports , and for each group.

When i right click in field then Insert->Smmary ,in this window i can select group and it is ok. Also I can select Summary (Minimum, Maximum,Count...) BUT no SUM , why?

1
crystal-reportssum
And what is your expected result of adding say July, 7th 2010 and August, 30th 2009? – Arvo
Thanks for reply , oh sorry i forget to say , field have this complexion- xx:хх (hours-minutes) , and for example 22:48 + 9:03=31:51 – Armen Khachatryan
Field type is string, may need to write formula, that converts it to numbers and calculate sum ? problem is i do not know CR formulas :( – Armen Khachatryan
Your claims are contradicting - in posting you talk about DateTime, in comments about String. Well, CR behavior points to DateTime anyway :) You can start with formula like Mark Bannister wrote; maybe avoiding seconds part. Well, to get output in form HH:MM, you of course need another formula to format output. – Arvo

1 Answers

2
votes

The reason (as Arvo implies) is that there is no sense in which datetimes can be added to each other.

You can create a crystal formula to derive the number of seconds since midnight in a datetime field - this would look like:

Hour ({MyTable.MyDate}) * 3600 + 
Minute ({MyTable.MyDate}) * 60 + 
Second ({MyTable.MyDate})

(Crystal's DateTime functions can be found in the Function Tree in the Formula Editor dialog, under the Date and Time option.)

The formula field could then be SUMmed like any other numeric field - the result would be expressed in seconds.

EDIT:

Assuming there are no padding characters in l1, a formula item with the expression:

ToNumber (Left (DefaultReportPlugin_DetailedDailyReport_ReportData.I1, 2)) * 60 +
ToNumber (Right (DefaultReportPlugin_DetailedDailyReport_ReportData.I1, 2))

would convert the string to a number expressed in minutes. (The Left, Right and ToNumber fields can be found under the Strings option in the Function Tree in the Formula Editor dialog.)

A sum of the formula (expressed in minutes) can be displayed by inserting the formula item into the report, right-clicking on it and selecting the Insert > Summary... option from the dropdown menu.