Average number of agents per time per day

[draacor]

Thanks to LBASS i have the following formula for calculating the average number of agents for each time frame.

//{@accum} to be placed in the {@timeint} group footer:
whileprintingrecords;
numbervar int12to7;
numbervar int7to5;
numbervar cnt12to7;
numbervar cnt7to5;
if {@timeint} = "12:00 to 6:59" then (
int12to7 := int12to7 + distinctcount({table.agent},{@timeint});
cnt12to7 := cnt12to7 + 1
);
if {@timeint} = "7:00 to 4:59 PM" then (
int7to5 := int7to5 + distinctcount({table.agent},{@timeint});
cnt7to5 := cnt7to5 + 1
);

Then in the report footer, create formulas like this:

//{@ave12to7}:
whileprintingrecords;
numbervar int12to7;
numbervar cnt12to7;
int12to7/cnt12to7

//{@ave7to5}:
whileprintingrecords;
numbervar int7to5;
numbervar cnt7to5;
int7to5/cnt7to5

What i am looking for now is i have a group where it shows each hour on the left, and across the top is each day of the week. This will show me the total and average number of calls for each day of hte week broken down by hour for a specific date range. I want to add another column that shows the average number of calls per agent based on the data for each day of the week. The above formula gives me the average agents for each time frame but how do i create a formula that shows the average number of agents for each time frame as well as for each day of the week. For example between 12am and 1am on Mondays there is an average of 11 calls, but i want to see the average number of agents during that time frame so that i can take those calls and divide it by the agents to get how many calls per agent.

 

[lbass]

I would not have suggested the above approach had I known your ultimate goal. I think you should start over and use SQL expressions to return the counts of calls and agents per hour per date, as in:

[%cntCalls:]
(
select count(`Call ID`)
from table A
where cdate(A.`datetime`) = cdate(`table`.`datetime`) and
{fn HOUR(A.`datetime`)} = {fn HOUR(`table`.`datetime`)}
)

[%cntAgents:]
(
select count(`Agent ID`)
from table A
where cdate(A.`datetime`) = cdate(`table`.`datetime`) and
{fn HOUR(A.`datetime`)} = {fn HOUR(`table`.`datetime`)}
)

Then you could insert a crosstab that uses a formula:

hour({table.datetime})

...as your row field, and:

dayofweek({table.datetime})

...as your column field. Use group options->customize name->use a formula to change the display:

weekdayname(dayofweek({table.datetime}))

Then add sum and average of {%cntCalls} as your summary field. I'm not sure but I'm guessing you would want to create a formula {@avecallsperAgt}:

{%cntCalls}/{%cntAgents}

...and then insert an average on this as your final summary. This would be an average of the average.

-LB

 

[draacor]

its hard to explain but with this particular report i do not have access to do sql commands for it. Is there a way to do this within crystal reports using formulas?

 

[lbass]

These are not commands, they are SQL expressions. Go to the field explorer->SQL expression->new. The syntax/punctuation would be dependent upon your datasource/connectivity however, so you might need to go to database->Show SQL query and observe how to punctuate the fields.

-LB

 

[draacor]

hmm in Field explorer it is greyed out to add anything new. Is it because i already have a SQL command for this report?

 

[lbass]

Yes. How are you using the command? (I thought you said you didn't have access to commands?) Unless it is for a parameter list of values, you should be using a command as your sole datasource, not linking it to tables--as that will seriously slow the report.

You could build in these expressions into a command, although the syntax would be a bit different.

-LB

 

[draacor]

its really strange, i kind of inherited this report from another program. The command that is listed is nothing more than a stored procedure located on the database. I went into the stored procedure and it is so convoluted and confusing i just left it alone. So all the modifications i have made to it reside on the crystal reports server. Im not too worried about the time it takes to run the report as it is pretty fast as it is and should be too bad if i do most of the formatting and calculations within crystal reports. But unfortunately this also means that i cant add commands to the stored procedure or the sql command in crystal.

 

[lbass]

I think you need to set up your own report then, as I think you really need to start out with already calculated summary fields for the kind of report you want.

-LB