Hi,

I am struggeling with getting a report that shows a trend (line chart). For
exemple I would like a report that shows:
Number of open cases in several queues over time (last month for exemple).

This way you can see whether cases are increasing or decreasing, if some
queues work more efficiently than others etc. Is this possible, I don't
manage to get this right.

/Regards,
Lotten

The proper method to do this is to basically have a batch job write the
number of open activities to a tabel each night and run the report on this.
However if you want to do this directly on the you would need to do the
following
This works on the basic assumption that a case is open between the create on
date and the modified on date if the current status is closed. Note that this
does not take into account situtations such as reopend cases, for this you
must use the first method.
First you must create a date ruler (this script may help you
http://www.patrickverbeeten.com/page...tem=59&p=true),
the join this with the cases table and using an agregate create the counts
this would look something like:

Select r.date, sum(case when r.date between c.createdon and c.modifiedon
then 1 else 0 end)
from ruler r, cases c
where c.statecode = <CLOSED>

Note that this query isn't terribly efficient so you may want to cache the
report.

--
Patrick Verbeeten (MCPD)
Lead Developer
Aviva IT

Extended Entity and Plug-in browser:
http://www.patrickverbeeten.com/maps/CrmTool.aspx



"Lotten Holm" wrote: