Creating running total 1

[breckie55]

I am new to using Crystal Reports and want to thank you in advance for any help you can give me.

I'm working on a report that is to calculate the amount of time a ticket is open from its first escalation to close date. This summary need to be broken down by representative and needs to produce the average age per rep. A ticket can be escalated multiple times, so I just want to capture the first escalation.

My details include ticket number, escalation date, close date. I then grouped by ticket number and wrote a formula to calculate the maximum age per ticket number (earliest escalation through close). These are grouped by representative and now I need a way to add all the maximum ages per ticket for each representative and then find the average. I think this should be easy and I've tried a bunch of things, but keep getting an error that "a summary/running total could not be created".

I keep wanting to create a variable that will hold the maximum per ticket and then create a sum of those variables per rep, but I can't do it.

Sample:

Group1: Rep1
Group2: Ticket 1234
Details:
Escalation Date CloseDate Time Difference
12/10/04 12/20/04 10 days
12/12/04 12/20/04 8 days

Group2: Ticket 4567
Details:
Escalation Date Close Date Time Difference
12/13/04 12/25/04 12 days
12/15/04 12/25/04 10 days
12/20/04 12/25/04 5 days

Desired Results:
Ticket 1234 was open for 10 days from first escalation to close.
Ticket 4567 was open for 12 days from first escalation to close.
10 + 12 = 22/2 tickets = 11 days
Rep1 has an average of 11 open days from first escalation to close.

 

[lbass]

Create three formulas:

//{@reset} to be placed in the Rep group header:
whileprintingrecords;
numbervar diff := 0;
numbervar counter := 0;

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar diff;
numbervar counter;

if onfirstrecord or
{table.ticket} <> previous({table.ticket}) then
(
diff := diff + datediff("d", minimum({table.escalationdate},{table.ticket}),{table.closedate});
counter := counter + 1
);

//{@displayave} to be placed in the Rep group footer:
whileprintingrecords;
numbervar diff;
numbervar counter;

diff/counter

-LB

 

[Crystalyzer]

I set up your data as a test, created a formula to calculate the "Time Difference" called @Time Difference and sorted the records in ascending order of Escalation Date. Then I created a running total to Average @Time Difference Evaluate on Change of Group #2 Ticket and Reset on change of Group #1 Rep and got the answer 11.

Be very careful of your sorting however. When I changed the sort on Escalation date to Descending I got a VERY different result I think because it only evaluates the average calculation when the ticket number changes therefore ignoring all the other data items.



Thanks and best regards,
-Lloyd

 

[breckie55]

Thank you both. I tried both ways and pretty much got the same result. The problem now has to do with managing the structure of the database. I want to be able to capture a single ticket a couple places. If Rep1 has a ticket escalated to him on Jan. 10 and he escalates it to Rep2 on Jan. 12, I'd like the record to show up in the aging results for each rep with the date it was escalated to them. Right now, it is only showing up for the first escalation. I'm guessing that I need to control the whileprintingrecords or whilereadingrecords processes, but I don't know how.

 

[lbass]

If {table.ticket} is your inner group, then records with different escalation dates for different reps should appear under both reps--unless you have used a SQL expression or something to return the earliest escalation date per ticket regardless of rep--but your earlier issue indicates that you haven't. I'm confused.

-LB

 

[breckie55]

Thanks so much. I started over and it worked fine. Not sure what the problem was, but it's good now.