Reporting on Duration field in Incident Tasks

[Ross Walker]

i have a field on incident task called actual effort - this is a basic duration field where the engineer inputs the amount of time worked on an incident. im not using a time worked as it was requested as a simple input.
i have a business requirement to report on that duration field value per day, currently its for the last 2 entries
example

INC Number   Assigned to   Actual effort day1  Actual Effort day2
INCXXXXXX  User1            3hrs 30mins             5hrs 15mins

my thought is to set up a metric to capture the value at the end of the day but currently im unsure where to start

any help would be gratefully recieved 

[Rajesh Chopade1 · Accepted solution]

Hi @Ross Walker 

You can use a combination of Scheduled Jobs, Metrics, and a custom table to store daily records.

Custom table - create table with fields - INC number, Assigned to, Actual effort, date.

Schedule job - it should run Daily on specific time and in script section write logic to capture actual effort of each incident task.

var gr = new GlideRecord('incident_task'); // Replace with your task table name
gr.addActiveQuery(); // Optional: only include active tasks
gr.query();

while (gr.next()) {
    // Create a new record in Daily Actual Effort table
    var dailyEffort = new GlideRecord('u_daily_actual_effort'); // Replace with your custom table name
    dailyEffort.initialize();
    
    dailyEffort.u_inc_number = gr.sys_id; // Set the incident task reference
    dailyEffort.u_assigned_to = gr.assigned_to; // Set the assigned to field
    dailyEffort.u_actual_effort = gr.u_actual_effort; // Capture the actual effort
    dailyEffort.u_date = new GlideDateTime().getDate(); // Set the current date
    dailyEffort.insert(); // Save the record
}

now create report on your custom table and add columns for INC Number, Assigned To, Actual Effort, and Date.

I hope my answer helps you to resolve your issue, if yes please mark my answer helpful and correct.

thank you

Rajesh

[Rajesh Chopade1 · Accepted solution]

Hi @Ross Walker 

You can use a combination of Scheduled Jobs, Metrics, and a custom table to store daily records.

Custom table - create table with fields - INC number, Assigned to, Actual effort, date.

Schedule job - it should run Daily on specific time and in script section write logic to capture actual effort of each incident task.

var gr = new GlideRecord('incident_task'); // Replace with your task table name
gr.addActiveQuery(); // Optional: only include active tasks
gr.query();

while (gr.next()) {
    // Create a new record in Daily Actual Effort table
    var dailyEffort = new GlideRecord('u_daily_actual_effort'); // Replace with your custom table name
    dailyEffort.initialize();
    
    dailyEffort.u_inc_number = gr.sys_id; // Set the incident task reference
    dailyEffort.u_assigned_to = gr.assigned_to; // Set the assigned to field
    dailyEffort.u_actual_effort = gr.u_actual_effort; // Capture the actual effort
    dailyEffort.u_date = new GlideDateTime().getDate(); // Set the current date
    dailyEffort.insert(); // Save the record
}

now create report on your custom table and add columns for INC Number, Assigned To, Actual Effort, and Date.

I hope my answer helps you to resolve your issue, if yes please mark my answer helpful and correct.

thank you

Rajesh

[Ross Walker]

@Rajesh Chopade1 Thank you - that has given me a great start
one last question - how can i link it back to the task or is that just not an option?

[Rajesh Chopade1 · Accepted solution]

hi @Ross Walker 

To link your daily records back to the task (or incident) while still using a custom table to store daily metrics, you can establish a reference relationship between your custom table and the task table.

Add new field on your custom table and type of field should be 'reference' & table = task / incident_task (as per your requirement change reference table here).

You need to update the schedule job as - add bellow lines before 'dailyEffort.insert()'

// Link back to the task
    dailyEffort.task = gr.sys_id; // Reference to the task or incident
    dailyEffort.insert(); // Save the record

[Ross Walker]

Hi @Rajesh Chopade1 

I have a new requirement added on to this im hoping you can help me with
The business now wants a new field (named Delta) where it calculates the difference in the daily effort between the latest one and one from the day before.
Currently im thinking that this should be done as a seperate scheduled task, but if it could be incorporated in the current task that may make it easier to maintain
Hopefully that makes sense