Sort by an average?

[glarkin]

Crystal Reports 10
Sybase database (Symposium call center server 4.2)


I have a report that simply shows employee names (group 1) and the average amount of time they spend on a phone call (talk time / calls answered). Talk time in the database is measured in seconds and I use a forumula that converts it into standard minutes & seconds format (00:00:00) as a string. I need to be able to sort these groups by their average talk time in decending order, but I can't select the average talk time formula as an item to sort by in the Group Sort Expert. Any help is greatly appreciated.

Thanks,
Greg

 

[lbass]

If you right click on {table.talktime}->insert summary ->average at the group level, you should be able to do a group sort. Then use your formula for display purposes only.

If this doesn't work, then you need to provide your specific formula for getting the average, including sharing the contents of any nested formulas.

-LB

 

[glarkin]

Thanks for the reply. Unfortunately, I wasn't able to get that to work. The average returned did not appear to be the same as if I calculate talktime / calls answered.

Here's the formula I use for calculating and formatting the average talk time:

StringVar Hours1;
StringVar Minutes1;
StringVar Seconds1;
NumberVar avglength;
NumberVar totalcalls;
NumberVar totaltalktime;

totalcalls := sum({mAgentPerformanceStat.CallsAnswered},{@Agent_Name});
totaltalktime := sum({mAgentPerformanceStat.TalkTime},{@Agent_Name});

If totalcalls = 0 then
avglength := totaltalktime
Else
avglength := totaltalktime / totalcalls;

avglength := Round(avglength);


If avglength< 0 Then
"Cannot have a time less than zero"
Else
(
(Hours1:=ToText(Truncate(avglength/3600),0);
Minutes1:=ToText(Truncate(Remainder(avglength,3600)/60),0);
Seconds1:=ToText(Remainder(Remainder(avglength,3600),60),0));

//Display the time formated.
(if length(Hours1) < 2 then '0') + Hours1 + ":" +
["0",""][length(Minutes1)] + Minutes1 + ":" +
["0",""][length(Seconds1)] + Seconds1
)


Thanks again for your input.

Greg

 

[lbass]

Whats the formula for {@AgentName}? Is this just to change the display? Let's assume you have a field {table.agent} that you group on. Go to database->database expert->your datasource->add command and enter:

select mAgentPerformanceStat.`agent`,
sum(mAgentPerformanceStat.`CallsAnswered`) as nocalls,
sum(mAgentPerformanceStat.`TalkTime`) as talktime
from `mAgentPerformanceStat`mAgentPerformanceStat
Group by mAgentPerformanceStat.`agent`

Link this to the main report table on the agent field.

Then in the main report, create a formula {@ave}:

if {command.nocalls} > 0 then
{command.talktime}/{command.nocalls}

Place {@ave} in the detail section and insert a maximum on it. Now go to topN sort and use maximum of {@ave}.

-LB

 

[glarkin]

You're correct, {@AgentName} just combines their last name & first name plus their login ID into a single field.

I'll give your suggestion a try. Thanks again,

Greg

 

[glarkin]

I copied the command into the Add Command dialog, changing mAgentPerformanceStat.`agent` to mAgentPerformanceStat.`agentlogin' and I get an error from Sybase about failing to open a rowset. I haven't used the Add Command feature before so I'll have to read up on it.

Thanks again,

Greg

 

[lbass]

Try looking at the "Show SQL Query" under the database tab in another report to see what the punctuation/syntax is like for your database. Then adjust my suggestion accordingly. I'm not familiar with Sybase to know what to recommend.

-LB