I'm trying to create a summarized report that shows the average time all cases spend in each status. Once I have that with the status as rows, I'd like a separate report with statuses as columns and case owners as rows.
.. just chiming into this thread here. I have to do this for a client and it was "non trivial".
I had to make a child table for the Status change records and each one had start and stop date time fields. To change status, the user would click a button on ghe parent help ticket which would update the most recent status record to "stop it" with the date/time of now(), and then add a new child status record and "start it" it now().
Then the actual status for the main help ticket record was a reverse lookup from the most recent child record.
Then I could make summary fields on the help ticket for the total time spent in each stataus.