Working out the initial response time on my helpdesk system

[n2nuk]

Hi all,

I am working on a report that will tell me the time taken to respond to a helpdesk call. I want to produce a pie chart that tells our client if we have met our target of a 2 hour response time. The response time is not necessarily when the call was resolved or closed but an indication that we responded to the client's request within the timeframe.

I have a date logged field, but the response time is derived from the activity table, where it's liklely to contain multiple activities for the call. I just want to check on the first activity as this will be the initial response/activity for that call.

I have created a formula that works out the difference in minutes
@responsetime datediff ("n",{event.datelogged}, {action_date})

I have another formula that returns a true or false if the time returned is under 2 hours:
@init_sla_met if {@responsetime} < 120 then true else false


I want the datediff formula to only check the first activity not any subsequeqent activites. How do I do that?

Also I have been looking at Ken Harmady's page to combine the business hours/working hours formula into my report, how would I do this? also am a bit confused as to what fields to use for the start and end dates. I can use event.datelogged as the start date, but what of the end date?

Many thanks in advance

 

[IanWaterman]

This type of thing is quite difficult in Crystal.

It would make your life a lot easier if you can do something in SQL.

1. Easiest option for you is to create a view on the database which brings back only the first date for each incident. Your reports only then needs to analyse a single record making reporting or Charting simple.
or
2. If you can't create a view then do it all in a command where you bring back the data you need from your log table and link to a subquery which replicates the view discussed above.

Ian

 

[Madawc]

There's another way. Group activities by call, sorted by time. Add a running total count that counts within the group. When it is one, it should be the "first activity".

If your formula only calculated the difference in minutes for the first instance, you could use a Maximum for the group, which would be a summary total (I think). Or you could use a Variable.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP