Apologies if this has been posted, but I've searched and was unable to find an answer.

I am working on a report that pulls data from BMC Remedy on Helpdesk tickets. I need to be able to subtract holidays, weekends, outside of business hours, times the ticket was in a "pending" status, and times that it was assigned to groups outside of our control. So far, I'm pretty sure I've got most of it. What I don't have, and can't seem to figure out, is how to remove time that tickets spent assigned to other groups. I am linked to the audit log system table and searching. I've gotten it to where it will reliably pick out the time when a ticket was re-assigned, but the report checks each audit log entry separately and I don't know how to consolidate them into a total time that a ticket spent with outside groups.

If I can figure this out, it will save our team a full 3 days worth of manually checking audit logs for tickets that are flagged as having missed our sla. Right now, most of them actually meet the sla (because of times spent with other groups), and we are spending days going through audit logs to figure this out. Any help would be so appreciated!!

Sorry, maybe I didn't explain quite as clearly as I thought. I'll try again.

My data is from an ODBC connection to BMC Remedy. I have a main report linked to HPD_Help_Desk that I'm using to pull individual ticket details like incident number/submit & resolve date/priority/etc. I'm using a subreport linked to HPD_HelpDesk_AuditLogSystem & a variety of formulas to find times when a ticket was placed into pending status, and when it was taken back out. Those all seem to work fine, and in collaboration with other formulas that exclude holidays, weekends, and time outside of business hours. I don't need help with the design, I need to figure out how to add together times that are on separate rows.

When I run the report, the audit log subreport shows each instance of an audit log as a separate record. So my formulas that define when a ticket was assigned to us vs another group are not calculating as planned. I used the same format for my pending formulas, they display the time a ticket was placed in pending, and when it was moved from. Then I use another formula to get the time elapsed while in pending. Those are added together and subtracted from the total work hours which excludes holidays/weekends/outside of business hours. Those are working fine.

My formulas are:

First instance of assigned to our groups:
shared datetimevar display_FSAssgnd1;

display_FSAssgnd1;

if {HPD_HelpDesk_AuditLogSystem.Log} like ["*Assigned group*Field*", "*Assigned group*JSP Service*"]
then {HPD_HelpDesk_AuditLogSystem.Audit Date}

First instance not assigned to our groups:
shared datetimevar display_xFSAssgnd1;

display_xFSAssgnd1;
if (({HPD_HelpDesk_AuditLogSystem.Fields Changed} like "*Assigned Group*")
and not ({HPD_HelpDesk_AuditLogSystem.Log} like ["*Assigned Group: Field*", "*Assigned Group: JSP Service Desk*"]))
then {HPD_HelpDesk_AuditLogSystem.Audit Date}

Datediff between the two:
whileprintingrecords;
numbervar dur := datediff("s",{@FSAssgnd1},{@xFSAssgnd1}); //gets the seconds between 2 dates
numbervar days;
numbervar hrs;
numbervar min;
numbervar sec;
stringvar ddhhmmss;
days:= truncate(truncate(truncate(dur/60)/60)/24);  //calculates number of days
hrs:= remainder(truncate(truncate(dur/60)/60),24);  //calculates number of hours
min:= remainder(truncate(dur/60),60);               //calculates number of minutes
sec:= remainder(dur,60);                            //calculates number of seconds

ddhhmmss:= totext(days,0,"")+":"+ totext(hrs,"00")+":"+ totext(min,"00")+":"+ totext(sec,"00");
ddhhmmss

The corresponding times for "FSAssgnd1" and "xFSAssgnd1" are displaying, but I am unable to get my datediff formula to calculate because each record only displays one time. I don't know how to consolidate them for calculations.

Sorry this is long & boring, I'm pretty much completely self-taught in Crystal, leaning heavily on knowing how to effectively search Google. Former manager "taught" me Crystal by attending a week-long training herself and giving me second-hand knowledge when she had the time. I'm now the defacto expert on the team and this report has me feeling like I'm in too deep. I don't know what I can and can't do within Crystal, I don't know all the functions so I don't know which one I'm needing help with. I'm learning as I go, and unfortunately the report requests have suddenly gotten much more in depth.