One way to do this is to use an Escalation which runs every night during off-hours. The setup is the following:
1. Create an Integer field with Administrator only permissions - I usually call it "Days Open" 2. Setup an Escalation which performs Days Open = Days Open +1 every night on each record 3. When you report, used the clause 'Status' < "Resolved" 4. Use the Days Open count for further statistics after the Case is Resolved. The result is a count of number of days the case was open at a specific time (using Create-date and Modified-Date in the query). Another way to do this is to create an Audit Trail form which holds status transactions. A query against this will give you what was open during a specific past time. The form will get very large, so you will have to come up with a good archive scenario. A good rule of thumb is on average, each Case will have seven (7) modifications. Therefore your Audit form will increase by 7 times for each Case. From an Archive standpoint, calculate how often you report. If it is 30 days, hold 30 days before you archive. If its yearly, hold a year and so on! Over 30 days may require creativity on the backend by holding archives in .arx files and reloading as you need them (something which can be automated). You can also use Status-History for calculating open times. Just some thoughts, Gordon M. Frank Lockheed Martin ________________________________ From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Julie L Kanakanui JLKANAKA Sent: Monday, November 24, 2008 9:08 AM To: arslist@ARSLIST.ORG Subject: Reporting question - count of tickets "open" at a particular point in time ** Hello, Wondering if anyone might have suggestion for best way to obtain snapshot stats of # of Cases open at a particular time each week (say, midnight on Wednesdays). I can run a Query with a search like: 'Created' <= "10/9/2008" AND ('Resolved' > "10/9/2008" OR 'Status*' < "Resolved") which selects Cases that were created up to and including 12:00am at midnight and that were either resolved after that point or are still not resolved at time of search. But obviously this would need to be run once each week... Trying to figure out if there's another way to do this where a date range could be entered and this stat collected for each week included. This is not the first time I've been asked about getting snapshop-in-time stats for open tickets and I'm hoping others may suggestions. We are currently on Remedy 6.x, but moving to 7.x, so solutions/suggestions in either would be welcome. Thanks, Julie [EMAIL PROTECTED] __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"