Calculating ticket age/ calculating in proper order

[krpurcel]

I need to develop a new report that calculates ticket age for tickets opened with our helpdesk. Currently we calculate the age of the ticket by simply calculating the time between the OPEN activity and the CLOSE activity for the ticket.
We need a new report because we have multiple resolver groups, and the tickets sometimes get passed back and forth between groups until the correct resolver group is identified. So even though a ticket may have been open for a week, the proper resolver group may have only had the ticket for 1 day. This is further complicated by the fact the tickets may be reassigned to the same group multiple times during their life.
To solve this I am trying to build a report that calculates total time each resolver group held the ticket during the ticket’s life. There is a reassignment activity that I can use for the calculations. I need help figuring out how to properly organize the data and the calculation order.
When I pull the reassignment activities from the database I have the following:
NUMBERPRGN TYPE CST_DateStamp FromGroup ToGroup
IM1533774 Reassignment 5/19/2008 3:13:30 PM HD LEVEL 2 CRN TRIAGE
IM1533774 Reassignment 5/19/2008 5:14:24 PM CRN TRIAGE CRN MFI
IM1533774 Reassignment 5/20/2008 5:30:36 AM CRN MFI CRN SIEBEL

Where NUMBERPRGN is the ticket number, and the CST_DateStamp represents the time the ticket was reassigned from the FROMGROUP to the TOGROUP.
In order to calculate total time for each resolver group I need to calculate the time the group held the ticket before reassigning to the next group. The DATESTAMP of the reassignment is the ending time for the current FROMGROUP and the beginning time for the new TOGROUP.
I can subtract the first reassignment time from the ticket’s open time to get the length of time the initial resolver group had the ticket, but don’t know how to calculate the time from that first reassignment to the next and so on. Somehow the DATESTAMP of the first reassignment must be recognized as the start time for the second resolver group and the next reassignment DATESTAMP would be the closing time for the second resolver group and the starting time for the third resolver group.
I don’t understand how to make that happen, and make sure that the calculations are being done in the proper order – i.e. all reassignment times and groups have been pulled from the DB before the time calculations begin.
Any and all help is appreciated! If you need more info than this, please let me know.

 

[lbass]

Try something like this:

if onfirstrecord or
{table.NUMBERPRGN} <> previous({table.NUMBERPRGN}) then
datediff("s",{table.opendatetime},{table.CST_DateStamp}) else
datediff("s",previous({table.CST_DateStamp}),{table.CST_DateStamp})

This would return the number of seconds at each level.

-LB

 

[krpurcel]

LBass,

As always, your advice is excellent. Thanks for the help with this.

 

[krpurcel]

LBass,

I need some more help please. I am having trouble grasping the concept of how to do this. I now have two formulas:

TimeDif Formula:
if onfirstrecord or
{ACTIVITYA1.NUMBERPRGN}<> previous({ACTIVITYA1.NUMBERPRGN})then
datediff("s",{@CST_OpenTime},{@CST_DateStamp}) else
datediff("s",previous ({@CST_DateStamp}),{@CST_DateStamp})
(this is the one you suggested and is working fine)

Owner Formula:
StringVar Owner;
(
if onfirstrecord or
{ACTIVITYA1.NUMBERPRGN}<> previous({ACTIVITYA1.NUMBERPRGN})then
Owner :=({@FromGroup}) else
Owner :=previous({@ToGroup})
)

I need to roll up the total time by owner. However I cannot group on the Owner field, I assume because it is a variable?

Can you tell me the right way to go about rolling up variables like this?

Thanks!

 

[lbass]

I'm not sure what you want to see. Can you show a sample with the time calculation and then also show how the same data would be rolled up by owner? Please show how the owner rollup would be displayed.

-LB

 

[krpurcel]

Doh, sorry LBass, here ya go.

TICKET NUMBER ACTIVITY Date/Time Stamp TIME DIF FROM GROUP TO GROUP OWNER
IM1645873 Open 12:00 N/A N/A CRN TRIAGE CRN TRIAGE
IM1645873 Reassign 14:00 2:00 CRN TRIAGE HD SERVICES CRN TRIAGE
IM1645873 Reassign 15:00 1:00 HD SERVICES HD LEVEL 2 HD SERVICES
IM1645873 Reassign 16:00 1:00 HD LEVEL 2 CRN TRIAGE HD LEVEL 2
IM1645873 Reassign 19:00 3:00 CRN TRIAGE N/A CRN TRIAGE

This is what I currently have using the two formulas to estabish which assignment group owned the ticket and the length of time that assignment group held it.

I need to total this as follows:
TICKET NUMBER Owner Total Time Dif
IM1645873 CRN TRIAGE 5:00
IM1645873 HD SERVICES 1:00
IM1645873 HD LEVEL 2 1:00

It would be simple if I could group on owner, but can't. I am not understanding something pretty basic I think.

Thanks

 

[lbass]

This isn't really basic stuff. How many Owners are there? If there are a reasonable number, you could create formulas like this (you should leave the time difference in seconds and convert later, after summing):

//{@reset} to be placed in ticketnumber group header:
whileprintingrecords;
numbervar crntriage;
numbervar hdservices;
numbervar hdlevel2;
if not inrepeatedgroupheader then (
crntriage := 0;
hdservices := 0;
numbervar hdlevel2 := 0
);

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar crntriage;
numbervar hdservices;
numbervar hdlevel2;
if {@owner} = "CRN TRIAGE" then
crntriage := crntriage + {@timediff};
if {@owner} = "HD SERVICES" then
hdservices := hdservices + {@timediff};
if {@owner} = "HD Level 2" then
hdlevel2 := hdlevel2 + {@timediff};

Then in the ticket group footer, add a text box identifying each total, and create a separate formula for each owner, like this to display the result:

whileprintingrecords;
numbervar crntriage;

-LB

 

[krpurcel]

LBass,

Unfortunately there are 28 different assignment groups. Any other thoughts about how to accomplish this? If not I will set up formulas for each group as you suggest.

Thanks for all the hard work you do supporting this forum!

Kevin

 

[lbass]

You would have to use arrays then--which I always have to work hard to figure out. I'll take a crack at it later.

-LB