Summary operation on a formule

[elsenorjose]

I have a formula which allows me to convert total call time by customer care agent from seconds to hours/minutes/seconds.

numberVar dur := {V_AGENT_CALLS_DAY.ACDTIME};
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


In CR, I am grouping by agent and by day so I get their total ACDTIME by day.

Now, I want to create a summary of their time by date range; i.e.; when a user requests a week of data, I want the agents' daily totals and their weekly total. I have tried creating this formula:

numberVar dur := sum({V_AGENT_CALLS_DAY.ACDTIME});
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

but the result is the sum of ACDTIME across ALL agents for the time period. Has anyone encountered this before and come up with a solution? Thanks in advance. I've used Tek-Tips many times before for KILLER solutions to GNARLY probs so I know I'll find success again!

 

[synapsevampire]

Try inserting a group into the report by the date field, and alter the formula:

numberVar dur := sum({V_AGENT_CALLS_DAY.ACDTIME},{table.date});

Place the formula in the Group Header or Group Footer.

-k

 

[elsenorjose]

So, {table.date} would be the field I am using as the date parameter in my report?

 

[synapsevampire]

No, that would be your table date, you don't group on parameters, you group on data.

-k

 

[elsenorjose]

Right, misworded the question but that's what I meant. Will try it right now. Thank you.

 

[elsenorjose]

I'm getting inconsistent results. In some records, it is correctly summing the time while in others it's not. Could the original formula be incorrect? Example:

Agent Smith
Date Calls ACD Time

10/1/2004 19 1:26:45

10/4/2004 25 1:52:51

10/5/2004 22 1:34:59

10/6/2004 14 1:51:26

10/7/2004 24 1:05:13

10/8/2004 18 1:46:51

10/11/2004 27 0:49:20

10/12/2004 23 2:25:15

10/13/2004 16 1:59:20

10/14/2004 24 2:22:24

10/15/2004 23 2:43:30

235 13:37:30<== Correct

Agent Jones
Date Calls ACD Time

10/1/2004 21 0:22:29

10/3/2004 53 0:45:44

10/4/2004 45 0:53:30

10/5/2004 29 0:31:47

10/6/2004 24 0:29:00

10/7/2004 24 0:17:39

10/8/2004 34 0:42:51

10/11/2004 35 1:01:14

10/12/2004 30 0:35:15

10/13/2004 29 0:38:37

10/14/2004 32 0:33:25

10/15/2004 31 0:44:38

387 0:44:38 <==Not correct

Any thoughts?

 

[dgillz]

Do you have a formula in the group header that resets the variables? Do you have "Repeat Group Header on Every Page" turned on?

If the answer to both of those questions is yes, then you need to modify your group header formula with a line of code:

Not InRepeatedGroupHeader

This will keep the variables from resetting except at the very first part of the group.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[elsenorjose]

No and no. The group header for group 1 (the agent name) only contains the group name and text labels for the data. I have both 'Keep group together' and 'Repeat group header on every page' unchecked.

 

[dgillz]

And what section of the report is your total for the agent in? The group footer?

Is the only incorrect agent total the last one, or was your data just one example?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[elsenorjose]

Yes, the report footer. I have some other totals, namely the number of calls, that are calculating correctly across all agents. If I do not use the conversion formula and just display the total in seconds, everything is fine, it's just when converting it to hhmmss format that things go awry. The samples I provided were from throughout the report. There is no consistency to the inconsistency! I am trying to see if there is anything in common with the agents that do not total correctly to see if there is some underlying problem I'm not taking into consideration but so far no luck.

 

[elsenorjose]

Well, don't know why this worked but when I changed the formula to:


numberVar dur := sum({V_AGENT_CALLS_DAY.ACDTIME},{V_AGENT_CALLS_DAY.FULLNAME});

where V_AGENT_CALLS_DAY.FULLNAME is the agent's name, it worked. Validated it with our switch's CMS interface and it's a match.

Thanks again everyone for the help.

 

[dgillz]

SUM() requires a number datatype. What is the datatype of your field?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[elsenorjose]

Well, no sooner do I declare victory than I realize I was way off. My solution seemed to work ok when I selected a week or two of data but when I opened it up to two months, I got the identical totals for each month, obviously not correct

dgillz, my data types are NUMBER for ACDTIME and DATE for the date field. I am running these reports on an Oracle 9i DB.