Summary total field could not be created headache 1

[zootweller]

Hi

I'm suffering from the 'summary total field could not be created' message and need help (again).
The story:

An old helpdesk report I have summarises the number of tickets raised in a month and the percent that were inside the service level.
In a normal world (!) the resolution time of a ticket would be calculated by the open and close dates, but it turns out that users are re-opening tickets for root-cause analysis, so throwing my figures out. Way out.

Howard Hammerman and LBass helpded me find the 1st close date from the seperate audit-trail table (thanks guys)(see

http://www.tek-tips.com/viewthread.cfm?SQID=884988&SPID=767&newpid=767&page=1)

but now I just cannot seem to summarise the tickets that are within the service level.

I was hoping that if I placed the following in the ticket group header I could summarise it:

If {@TimeToResolve} < 8.00 Then 1 Else 0

I have also tried creating conditional running totals, distinct counting the ticket number if within the expected time to resolve.

I am using CR7.5 btw, still waiting for the business to upgrade to v10.

Any help appreciated, once again..

 

[lbass]

It's difficult to refer back to old threads in trying to figure out a solution, and even in the old thread, you have not shown the content of {@TimeToResolve}. I'm guessing this is some sort of date difference, but please post the contents of all formulas used within this formula and the formula itself, and then we can go from there. It would also help to know something about the structure of your report--groups, etc.--and whether you have duplicate data. Sample data would be helpful.

-LB

 

[zootweller]

There are 2 tables involved, linked by problem_id:

PROBLEM contains problem_id, opendatetime, closedatetime, SLALevel, description etc.

AUDIT_TRAIL lists the life cycle of the call, problem_id, when the problem was opened, transferred or closed, at what datetime, by who etc. Each action has an associated event_type field and when a call is closed the event_type = 'Quick Solution'.

GH1: Grouped by {PROBLEM.problem_id}, also displays {PROBLEM.opendatetime}, {@FirstCloseDate}, {@HoursToClose}

//{@FirstCloseDate}
If {@FirstQuickSolution} = DateTime(9999,09,09,0,0,0) Then {PROBLEM.closedatetime}
Else Minimum({@FirstQuickSolution}, {PROBLEM_VIEW.PROBLEM_ID})
(this only works in the group header, not the footer).

//{@HoursToClose}
({@TrueCloseDate} - {@OpenDateTime})*24
(calculates the decimal time a call was open before being closed the 1st time)


Detail: {AUDIT_TRAIL.event_type}, {AUDIT_TRAIL.event_datetime}, {@FirstQuickSolution}

//{@FirstQuickSolution}
If IsNull({AUDIT_TRAIL.event_type}) or
{AUDIT_TRAIL.event_type} <> 'Quick Solution' Then
DateTime(9999,09,09,0,0,0)
Else {AUDIT_TRAIL.event_datetime}


I created {@SLA_Achieved} in GH1 thinking it was a simple case of summarising it to count the number of Problem_ID's within the SLA, so the percentage could be found:

//{@SLA_Achieved}
If {@HoursToClose} <= 8.00 Then 1 Else 0

This gives the 'summary total field could not be created' message.
The same when I try to incorporate a similiar formula into a running total.


Any suggestions would be most appreciated!

 

[lbass]

//{@HoursToClose}
({@TrueCloseDate} - {@OpenDateTime})*24
(calculates the decimal time a call was open before being closed the 1st time)

Just to clarify, is {@TrueCloseDate} another name for {@FirstCloseDate}? And is {@OpenDateTime} really {PROBLEM.OpenDateTime}?

-LB

 

[lbass]

You shouldn't have made the change you made to my formula in your last thread. {@FirstCloseDate} should be:

If {@FirstQuickSolution} = DateTime(9999,09,09,0,0,0) Then {Orders.Ship Date}
Else {@FirstQuickSolution}

Then you can right click on this formula and insert a minimum and display it in the group header OR footer and it will be correct in either place.

Then {@HoursToClose} should be (if I am correct in my guesses in my last post):

(minimum({@FirstCloseDate},{PROBLEM_VIEW.PROBLEM_ID})-{PROBLEM.OpenDateTime}) * 24

This assumes there is only one {PROBLEM.OpenDateTime} per ProblemID. Then create another formula {@SLA Achieved} to count the groups which meet your achievement criterion:

whileprintingrecords;
numbervar cntSLA;

if {@HoursToClose} < 8 then
cntSLA := cntSLA + 1 else
cntSLA := cntSLA;

Then create another formula {@percentSLAAchieved} to display the percent in the report footer:

whileprintingrecords;
numbervar cntSLA;

cntSLA % distinctcount({PROBLEM_VIEW.PROBLEM_ID})

Right click on the last formula and select the % icon in the toolbar to add the percent sign.

-LB

 

[zootweller]

Sorry about the confusion, but you were spot on.
{@OpenDateTime} is actualy {PROBLEM.OpenDate} (datetime) and {PROBLEM.OpenTime} (text) joined-up and {@TrueCloseDate} is the same as {@FirstCloseDate}, I just wasn't paying attention.

Anyway, you have helped me out once again LB, and I salute you!