Summing Time in Group

[littleress]

I am creating a report that takes data from a MySQL view. The report itself accurately shows the times for a call.
Such as 26:11, 1:02:15, 00:30

Is there a way to be able to "sum" up times within Crystal Reports? I want to be able to group on user and drill down to the actual calls. And then you can pull on date range and get Total # of calls and total amount of time on the phone.

is this possible in Crystal? I seem to be striking out.

Thanks! Amanda

 

[Madawc]

Does it show the start time and end time? If so, you can use DateDiff, which includes time options. (Look it up using HELP.)

If it shows time plus duration, you can extract the time using SPLIT on the comma, then separate minutes and second using the colon. Multiply minutes by 60 and sum the result.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[littleress]

I'm using CR 2008

I already have duration. Like I said originally, I just want to sum them, it shows the actual duration in the details. But when I choose the field, it doesn't allow for a Sum on this field so I didn't know if there was a different way to sum time durations within CR or if I have to via the db view script.

Thanks

 

[lbass]

You have to summarize the durations as numbers, e.g., seconds, and then convert back to a string for display purposes if you wish. So if your durations are also currently appearing as strings, you should show us samples of the durations, indicating what each component means, e.g., dd hh:mm:ss, etc.

-LB

 

[littleress]

I have it in 2 fields. One is a time duration and Crystal know how to format the detail as a time just fine.
So when Crystal allows me to format, it comes up:

0:00.02
0:01.46
0:00.25

I also created a field that is a number field summing up the
seconds and so it comes up

2
106
25

But I still can't figure out how to get summaries of the times.

I have 3 levels.

Top Group is sum of all days selected, 2nd group is sum of days within that summary, bottom level is details of every call within the day.

So exactly what am I supposed to convert and where?

 

[lbass]

If you already have the duration in numbers, then just right click on the number formula you used to convert the string to seconds and insert a sum at whatever group you like.

-LB

 

[littleress]

All of this data is coming from a MySQL View. I haven't converted anything in Crystal.

I'm using CR 2008 Designer -so I'm not sure if that makes a difference. I haven't created any code in CR.

I have 2 views. I have a sum view by date and a details view.

So I need the top sum though to be dynamic since I don't know what date range is pulled and that's where I don't know how to do that.

The seconds comes over as a number from the MySQL View - so am I supposed to convert that to a string?

 

[lbass]

I'm not following. Why don't you show some sample data at the group and detail level. It sounds like maybe you have seconds (number) in the detail level and that for some reason you also have a precalculated summary in string form at the some group level. If you need to summarize the numbers, you can insert sums on the seconds at each group level. If you want them to appear as a string, you would convert by setting the particular group sum equal to "dur" using the guidelines in faq767-3543, as in:

numbervar dur := sum({table.seconds},{table.group#1field}); //etc.

-LB

 

[littleress]

If I never leave CR 2008 designer - how do I do that? That's what I'm not getting. Do I do the above in the SQL expression?

All we do is call the Crystal Report from cold fusion which just will send through the report id and the 2 dates (start/end), nothing more. The Report calls a view from MySQL that just has the hard data. Everything else I'm building within Crystal Designer.

So where do I code in all of your := and get the remainders. That's what I keep coming back to ask. Where do I put in all of this that you keep referring to??? Is each a formula that is seperate, or each an expression? Or how do I do that?

Thanks.

 

[lbass]

I don't know what you are referring to by "Crystal Reports 2008 Designer". If you are working with CR 2008, you would just go into the field explorer->formulas->new and enter the conversion formula from the faq there. Substitute your field names in the following part of the formula:

numbervar dur := sum({table.seconds},{table.group#1field}); //etc.

...where {table.seconds} is your seconds field, and {table.group#1field} is the field you are grouping on. You would create a separate formula for each group, substitituting the new group field in each formula.

-LB