Business Duration to show as HH:MM:SS and not Days

[billi_lumley]

For reporting purposes, looking to pull the business duration for SLA-related reports and have the duration show as HH:MM:SS and NOT as DD:HH:MM:SS. Reason being is that SN rolls up 24 hours into 1 day whether the calculation is using a schedule or not and is causing some confusion.

For example a business elapsed (business duration) is calculating using the schedule 7-6 M-F and the business elapsed time is 1 day 8 hours 5 minutes (32 hours total). The incident was opened lated on Wednesday at 4:50pm, closed the next Monday at 3:56pm.

Wednesday — 1hr 10min left
Thursday — 11 hours
Friday — 11 hours
Saturday — Not counted
Sunday — Not counted
Monday — 8hrs 56min

Total is just over 32 hours (32hr05min).

Therefore I want the business duration to show as 32:05:00. Possible???

[Mike Allen]

The incident rule (marked_resolved) calculates the business duration:

if (dataChange || current.business_duration.nil())

          current.business_duration = gs.calDateDiff(opened, resolved, false);

If you take the script part and manipulate it to fit in something like this:

Calculate Duration Given a Schedule - ServiceNow Wiki

You could probably get it to do display as 7 days, etc., etc.

[Mike Allen]

Looks like this is working:

setResolutionFields();





function setResolutionFields() {

   if (current.resolved_by.nil())

   current.resolved_by = gs.getUserID();

   if (current.resolved_at.nil())

   current.resolved_at = gs.nowDateTime();



   // Update the fields that indicate the time and duration of this incident from open to resolve.

   // Keep track of duration as a glide_duration value (dd hh:mm:ss) and as a pure number of seconds.

   // Both calendar time and business time are maintained.



   var dataChange = current.opened_at.changes() || current.resolved_at.changes();

   var opened = current.opened_at.getDisplayValue();

   var resolved = current.resolved_at.getDisplayValue();



   if (dataChange || current.business_duration.nil()){

   var dur = calcDurationSchedule(current.opened_at, current.resolved_at);

   current.business_duration = dur;

   }



   if (dataChange || current.business_stc.nil())

   current.business_stc = gs.calDateDiff(opened, resolved, true);



   if (dataChange || current.calendar_duration.nil())

   current.calendar_duration = gs.dateDiff(opened, resolved, false);



   if (dataChange || current.calendar_stc.nil())

   current.calendar_stc = gs.dateDiff(opened, resolved, true);

}





function calcDurationSchedule(start, end) {

   // Get the user

   var usr = new GlideRecord('sys_user');

   usr.get(gs.getUserID());

   // Create schedule - pass in the sys_id of your standard work day schedule and pass in the users timezone

   var sched = new GlideSchedule('08fcd0830a0a0b2600079f56b1adb9ae',usr.time_zone);

   // Get duration based on schedule/timezone

   return (sched.duration(start.getGlideObject(), end.getGlideObject()));

}

[montanadawgz]

Even though we set the max_unit=hours, for Line type reports using the Business Elapsed Time, this works.

This problem is that anytime the report type is changed to Bar, and an Aggregation = Average, the Aggregate field is Business elapsed time, the OOB default format changes back to DD/HH/MM/SS time format. 

I am reading that we can create a new Duration field in the Task_SLA table, call it Business Elapsed Hours (BEH), however it contains no records. One has to code either a Business Rule, or scheduled job that will fire at certain times of the day to copy the value from Business Elapsed Time (BET) to Business Elapsed Hours (BEH), but should only happen if BEH is null. 

BET can also be used to track Incidents responding, and Incidents Resolved. This is done using SLA Definitions. 

1. So what mechanisms should we use to trigger the Business Elapsed Hour field to get filled?

2. Can I format the Business Elapsed Hour field to used both HH:MM:SS?

thanks