Calculate a help desk ticket end to end time (Variable and Count)

[marckssg]

Hi,

I'm running Crystal 2008 against a SQL Server 2005 Remedy database.

My problem is I'm trying calculate the end to end time of a help desk ticket minus the time the ticket had a status of "pend" I am then trying to compare that to Service Level Targets on a month by month basis.

Remedy has an Audit Log table which contains memo fields and time stamps, one tells you what fields have changed and the other tells you what values have been added, so I've been able to work out using variables when a call enters a pend status and when it enters, I add all these together using a variable to give me a Total Time in Pend for a ticket, I then subtract this from the total time from when a ticket was opened to resolved and I have the end to end ticket time displayed in a group footer.

What I'd like to do now is create a manual cross tab by the four severitys of call (Sev 1, Sev 2, Sev 3, Sev 4) down the left handside, with a further group with the targets and have a rolling 6 monhts across the top then the detail would be a count of tickets that have met the target and then a % of those met compared to overall volume of tickets for that severity in the month.

Its an evolving report I'm trying to modify, it was fit for purpose and now with the additional summaries its not as I was going to do a distinct count were say the following conition was met;
if {@Severity} = "Severity 1" and {@End to End} < 120 then {HPD_HELP_DESK.Incident_Number}

But I can't as the @End to End is based on the variable, I've been looking at this to long and suspect I've gone to far down the wrong path to make it work so any help appreciated!

I'll be looking to run it over 6 months with around 50,000 tickets a month, I can't use a subreport as I suspect the next stage will require this report to become a subreport of a monthly management pack that will be automated monthly. I also can't do anything database side we don't have acces.

Cheers

Marc

 

[marckssg]

I should add, but missed from the above, my best stab so far involves a running total via a variable using a count as follows;

whileprintingrecords;
numbervar Sev4TailCount;

if {@End to End} < 120 then
Sev1Count:= Sev1Count + 1;

Then Displayed in the footer as;
whileprintingrecords;
numbervar Sev1Count;

Which does give me what I need, as far as I can see, just not the most elegant of solutions, I've that nagging feeling I've over engineered the whole thing!

Cheers

Marc

 

[Madawc]

Are you aware you can add the same dataset to Crystal two or more times, with the later instances being aliases, a different name and can be processed as if they were two separate datasets.

Not sure if this would fix it, but worth thinking about.

Another possibility (if allowed) is to do multiple selections and some processing in an SQL Command.

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