summary problem (newbie)

[spungee]

I got a problem
I need to make a report for a callcenter, and there is a table called ccm.agent_state with the fields: agent_id, old_state, time_in_old_state

I need to count the average time each user has been in each state. The report has to be in a table format like this

old_state=1 old_state=2
---------------------------------------------------------
agent_id: (avg. time in old_state 1)
agent_id:
agent_id:


I've tried making summaries the "automatic" way, but the values aren't right at all. I've tried making a formula too, but I just can't get it right.I got the table set up looking ok, listing the agent_id's, but I think my problem is understanding the way formulas work. I'm pretty new to this reporting stuff too, so some help would be greatly(!) appreciated. =)

I've made 2 groups in the report:
Group 1: agent_id
Group 2: old_state (sorted in specified order, with subgrouping of old_state=1, old_state=2...and so on.

 

[Ngolem]

ok...let us take it one step at a time....

"table called ccm.agent_state with the fields: agent_id, old_state, time_in_old_state"

1. Are there any other tables involved or is the report simply on this table?

2. What is the format of the field, time_in_old_state? Is it a string , number, date, time or datetime?

3. old_state=1, old_state=2....are these codes for state names??? Would you not want actual State names?

4. this format for the layout below:

old_state=1 old_state=2
---------------------------------------------------------
agent_id: (avg. time in old_state 1)
agent_id:
agent_id:


do you want it to look like this??

Average Time
--------------------------------------
Agent State 1 State 2
----------------- -------------------

{table.AgentID} {@AvgTimeState1} {@AvgTimeState2}


If this is the case then we can put the the column details in the Page header....suppress the Agent and State headers, as well as the detail section and the State footer

The results will be displayed in the Agent footer from "3 formula " calculations...storing the results in variables for later display.

Answer these questions and we will go from there
Jim
JimBroadbent@Hotmail.com

 

[spungee]

There are 2 more tables involved
userinfo with "fullname" and "id"
calls which also has "id"
I've created a relation between the "id" fields which is the same as the "agent_id".

old_state is an integer with values from 1 to 7, each representing a state the user has been in. There is no table with a name of state relating to the value in the "old_state" field, we just had to pull that out of the coding..

time_in_old_state is a number field with the amount of seconds in old state (not 100% sure about that one, I'll check it out on monday)

 

[Ngolem]

As far as the states goes you can convert the numbers to State name in an array created in the report header...as long as you are sure these numbers won't change. It will involve some maintenance down the road as new States are added....unless you can do the total 52 states at once.


Average Time
--------------------------------------
Agent State 1 State 2
----------------- -------------------

is this the look you want or is it to be for the entire 7 state list with the possiblities of other states being added. If all states are to be added then you must plan your real estate on the form carefully...7 states are not bad but what if it were 52??. A simple crosstab report might all you need although I am not sure if it will do a crosstab of averages.

Many things to consider before giving you your answer
Jim
JimBroadbent@Hotmail.com

 

[spungee]

old_state is an integer field btw, and yeah. I'd like to do a report with all 7 states as different column "headers" I'll only work here for another 10 days, so someone else has to do the updating if they decide to change the states. I just need to get this finished by the time I got left

the layout you set up above looks pretty right, if you mean the average values for the state would come up where it says "State 1"

Thanx for helping out btw, everytime I posted on this forum, people have been rushing to help =)