Sum group date/time values 1

[gennaroalpha7]

Hello -

Pete, has helped me derive this formula and it's working great. But, I would like to sum each groups date/time totals. My formula for summing date/time is:

@ResolutionTime

WhilePrintingRecords;
Local NumberVar seconds := 0;

If      IsDate({CallLog.ClosedDate})    and
        IsDate({CallLog.RecvdDate})      and
        IsTime({CallLog.ClosedTime})    and
        IsTime({CallLog.RecvdTime})
Then    seconds := DateDiff("s", DateTime(Date(Trim({CallLog.RecvdDate})), Time({CallLog.RecvdTime})), DateTime(Date(Trim({CallLog.ClosedDate})), Time({CallLog.ClosedTime})))
Else    seconds := 0;

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#.##') + ' minute(s)'

I have created, or trying to, another formula for summing each groups date/time totals called @SumDateTime:

Sum[highlight #FCE94F]({@@ResolutionTime},[/highlight] {Asgnmnt.GroupName})

When I check for errors it gives me the following...'A number field or currency amount field is required here' which I have highlighted.

Please help with this error. Do I need to convert the field or is the formula not considered a field?

Thanks for your help.

Alpha7

 

[pmax9999]

You can't use a Summary calculation because of the use of variables. Any Summary will need to be done manually with variables.

Amend the Formula as follows:

[Code {@ResolutionTime}]
WhilePrintingRecords;
Local NumberVar seconds := 0;
Global NumberVar Tseconds;

If IsDate({CallLog.ClosedDate}) and
IsDate({CallLog.RecvdDate}) and
IsTime({CallLog.ClosedTime}) and
IsTime({CallLog.RecvdTime})
Then seconds := DateDiff("s", DateTime(Date(Trim({CallLog.RecvdDate})), Time({CallLog.RecvdTime})), DateTime(Date(Trim({CallLog.ClosedDate})), Time({CallLog.ClosedTime})))
Else seconds := 0;

Tseconds := Tseconds + seconds;

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#.##') + ' minute(s)'
[/Code]

Then add the following formula to the Group Footer:

[Code {@EmpTotal}]
WhilePrintingRecords;
Global NumberVar Tseconds;

ToText(Truncate(Tseconds/3600), '#') + ' hour(s), ' + ToText(Remainder(Tseconds, 3600)/60, '#.##') + ' minute(s)'
[/Code]

Finally, add the following formula to the Group Header:

[Code {Reset}]
WhilePrintingRecords;
Global NumberVar Tseconds := 0;
[/Code]

Hope this helps

Pete

 

[gennaroalpha7]

Hi Pete -

I completed the steps that you had outlined:

1. Amended the first formula...@ResolutionTime
2. Created the @GroupTotal (@EmpTotal) formula and placed it into Group Footer #1
3. Created the @Reset (Reset) formula and placed it into the Group Header #1

When I run the report it tallys each groups total hours, which is great. Thank you.

But, the @Reset (Reset) formula, which I placed into the Group Header #1 is showing a, at the beginning of a new group, 0.00 when I run the report. Is this correct, because I know it supposed to reset to zero. Is this a correct assumption?

Thanks.

Alpha7

 

[gennaroalpha7]

Hi -

To eliminate or hide duplicate values I used...{CallLog.CallID} = Next( {CallLog.CallID} )

This seemed to work...was this correct?

Thanks.

Alpha7

 

[gennaroalpha7]

Hi -

In reference to my 2 prior post...I have hidden or eliminated duplicates but the @GroupTotal is counting the duplicates.

How can I stop the @GroupTotal formula from adding the duplicates into the total for each group. Should a If Else statement be used?

Thanks.

Alpha7

 

[pmax9999]

The {@Reset} formula will show on the report as a zero. The formula can be suppressed so it does not show.

As for the other post, what formula are you talking about?

Pete

 

[gennaroalpha7]

My post of...19 Sep 13 14:46


I completed the steps that you had outlined in your post of 18 Sep 13 18:14 :

1. Amended the first formula...@ResolutionTime
2. Created the @GroupTotal (@EmpTotal) formula and placed it into Group Footer #1
3. Created the @Reset (Reset) formula and placed it into the Group Header #1

When I run the report it tallys each groups total hours, which is great. Thank you.

I managed to remove the duplicate values with ...{CallLog.CallID} = Next( {CallLog.CallID} )

But, the @GroupTotal formula is adding the duplicate values. How can I stop the @GroupTotal formula from adding the duplicates into the total for each group. Should another If Else statement be used in the @ResolutionTime formula?

Thanks Pete.










I have eliminated the duplicate values, but it's adding those duplicate values in each groups total. I believe the formulas to your earlier post of 18 Sep 13 18:14 will show th formulas....

How can I stop the @GroupTotal formula from adding the duplicates into the total for each group. Should a If Else statement be used?

 

[pmax9999]

Without a better understanding of the report structure it is difficult to be sure of the best approach, but you could try:
[ol 1]
[li]Insert a Group on{CallLog.CallID}[/li]
[li]Suppress its GH section[/li]
[li]Suppress the Details section[/li]
[li]Move existing data (including the {@ResolutionTime} formula) from the Details section to the GF[/li]
[/ol]

Alternatively, the following amendment to the {@ResolutionTime} formula may work:

[Code {@ResolutionTime}]
WhilePrintingRecords;
Local NumberVar seconds := 0;
Global NumberVar Tseconds;

If IsDate({CallLog.ClosedDate}) and
IsDate({CallLog.RecvdDate}) and
IsTime({CallLog.ClosedTime}) and
IsTime({CallLog.RecvdTime}) and
Not(OnLastRecord) and
{CallLog.CallID} <> Next({CallLog.CallID})
Then seconds := DateDiff("s", DateTime(Date(Trim({CallLog.RecvdDate})), Time({CallLog.RecvdTime})), DateTime(Date(Trim({CallLog.ClosedDate})), Time({CallLog.ClosedTime})))
Else seconds := 0;

Tseconds := Tseconds + seconds;

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#.##') + ' minute(s)'
[/Code]

If these alternatives don't achieve what you need, please explain report structure (grouping etc) and the fields on the report, plus provide some sample data.

Cheers
Pete

 

[gennaroalpha7]

Hey Pete -

If you don't mind, I am going to attach this CR 2008 report tomorrow please have a quick look at it for some feedback for me.

Thanks.

Have a good one.

Alpha7

 

[gennaroalpha7]

Hi -

Here's the linked CR8 report.

Thanks.

Alpha7

https://www.dropbox.com/s/tmqp759oq...s Report - Resolution Time 9.17.2013 v0.2.rpt

 

[pmax9999]

I've had a quick look at the report.

I see you implemented my alternative approach rather than the suggested approach. The first suggestion is the better one and does work, so I would have gone that way.

Not sure if you picked up on it but with the latest version of the formula it was returning 0 minutes for the last record. To fix that, amend that formula as follows:

WhilePrintingRecords;
Local NumberVar seconds := 0;
Global NumberVar Tseconds;

If	IsDate({CallLog.ClosedDate})	and
	IsDate({CallLog.RecvdDate})	and
	IsTime({CallLog.ClosedTime})	and
	IsTime({CallLog.RecvdTime})	and
	(
		OnLastRecord or
		{CallLog.CallID} <> Next({CallLog.CallID})
	)
Then    seconds := DateDiff("s", DateTime(Date(Trim({CallLog.RecvdDate})), Time({CallLog.RecvdTime})), DateTime(Date(Trim({CallLog.ClosedDate})), Time({CallLog.ClosedTime})))
Else    seconds := 0;

Tseconds := Tseconds + seconds;

ToText(Truncate(seconds/3600), '#') + ' hour(s), ' + ToText(Remainder(seconds, 3600)/60, '#.##') + ' minute(s)'

The only other aspect I would check is the join between the "CallLog" table and "Asgnmnt" table on DTLastMod. I am not familiar with the application that uses this database so it may be that both tables always get updated at the same time, but I would try dropping that link (leave it joined on the CallID) to see if you get any additional records. I suspect that your Crystal Reports is set up to use "Smart Linking" which I always turn off by default as I find it wastes more time than it saves.

Anyway, hope this helps.

Cheers
Pete

 

[gennaroalpha7]

Thanks Pete -

Have a good one.

Alpha7