|
Good Day,
I am using Crystal Reports XI (11.0.0.1282) against a DB2 database.
I have a ticketing system that I need to pull data from. I have been asked to pull the number of closed tickets by customer. I must then determine if the issue in the ticket was fixed in a given amount of time.
The first table probsummarym1 contains the following data I need to pull:
Ticket Number
Severity
Customer
Opened Date
The requestor of the report wants me to determine if the issue was resolved within the time limits of our contracts.
The basis of the resolved time is supposed to be Opened Date to First Resolved Time.
The second table activitym1 contains the following data:
Ticket Number
Update Type
DateStamp
The Resolved time would come from the activitym1 database.
A ticket will have multiple activities.
A ticket may have multiple resolved times
A ticket may not have a resolved time at all. In that case, use the Closed Time.
Here is how a ticket could look:
Ticket # Severity Customer Opened Date
12345 1 ABC 01/01/2013 08:00:00
Activity for ticket 12345
Ticket # UpdateType DateStamp
12345 Opened 01/01/2013 08:00:00
12345 Updated 01/01/2013 08:05:55
12345 Updated 01/01/2013 08:10:00
12345 Resolved 01/01/2013 09:00:00
12345 Resolved 01/01/2013 09:15:00
12345 Closed 01/01/2013 10:00:00
In this ticket's case, I would want to find the time beween the Opened Date of 01/01/2013 08:00:00 and the first instance of Resolved (01/01/2013 09:00:00)
================================================================
Ticket # Severity Customer Opened Date
123456 2 ABC 02/01/2013 08:00:00
Activity for ticket 12345
Ticket # UpdateType DateStamp
123456 Opened 02/01/2013 08:00:00
123456 Updated 02/01/2013 08:05:55
123456 Updated 02/01/2013 08:10:00
123456 Closed 02/01/2013 10:00:00
123456 Closed 02/01/2013 10:05:00
In this ticket's case, I would want to find the time beween the Opened
Date of 02/01/2013 08:00:00 and the first instance of Closed
(02/01/2013 10:00:00)
===================================================================
Ticket # Severity Customer Opened Date
99999 2 ABC 02/01/2013 08:00:00
Activity for ticket 12345
Ticket # UpdateType DateStamp
99999 Opened 02/01/2013 08:00:00
99999 Updated 02/01/2013 08:05:55
99999 Updated 02/01/2013 08:10:00
99999 Closed 02/01/2013 10:00:00
In this ticket's case, I would want to find the time beween the Opened
Date of 02/01/2013 08:00:00 and the Closed UpdateType
(02/01/2013 10:00:00)
==================================================================
I need help finding the minimum Resolved time (in case of ticket 12345. I need help finding the minimum Closed time (in the case of ticket 123456.) I need help finding the minimum Closed time (in the case of ticket 99999.
Any help you can give would be very much appreciated!
I hope I have posted enough information.
Thanks!
Jennifer
|
|
Hi
Please follow the sequence of below steps :
--Create Group on Ticket# (group1)
--Create Group on UpdateType (group2)
--Insert a summary on DateStamp and use (Minimum) as summary type.
Now Create manual running totals for Closed / Resolved / Updated
I am giving you example for Closed, you can create for rest..
@Init_closed
WhilePrintingRecords;
Stringvar Open:=' '; // place this on group1 header
@Acc_closed
WhilePrintingRecords;
Stringvar open;
If GroupName ({Sheet1_.Undate Type}) = "Opened" Then
open:=open+Totext(Minimum ({Sheet1_.DateStamp}, {Sheet1_.Undate Type})) // Place this on your group2 header
@disp_closed
WhilePrintingRecords;
Stringvar Open; // Place this on your group1 footer.
Create using same logic for rest.. and in Group gooter 1 you can create one more formula to find the difference.
|
IP Logged
