Aggregating on function fields

Ace009
Tera Contributor

yesterday

Requirement: The duration field for Incidents currently is populated once a ticket is closed/set to inactive. When it's still open, the duration field is still empty.  We would like to report on tickets, aggregating SUM based on how long tickets remain open (ie Hardware - 100 hours vs Software 80 hours). 

 

I tried doing a function field similar to this unsolved question:

How to show actual duration for incidents that are... - ServiceNow Community

 

Although the counter doesn't stop and it looks like we can't use a script include but only specific Function field functions. 

 

When I try to add my custom function field as an aggregate SUM on a BAR graph report, it has no data. If I do it in a List report, the report shows the function fields populated correctly.

 

So I have two problems/questions:

 

  • Anyone have a solution to having a function field with an up to date duration value?
  • How do we aggregate on function fields? There is a way to add a Function Field via the report itself (although in my instance, I cant because Im not allowed to use now() in the report), that field is not even available as an option in the aggregate drop down.

 

0 Helpfuls
  • 253 Views
1 REPLY 1

lauri457
Giga Sage

yesterday

I assume calculated and function fields are not triggered properly when you are not loading a list etc so they will not work as expected when you are loading something like a reporting visualization. 

 

As you are working with the deprecated sla duration fields, I would suggest looking into using SLAs for your reporting needs. This is basically what they are for and can offer more granular information in e.g. business/actual elapsed time.

 

However if you can't convince business otherwise, you can use a duration field and set a duration field as below. SLAs are calculated by the BR Calc SLAs on Display on the task table if you need inspiration. But I definitely recommend SLAs for this type of reporting and would question business on this particular requirement vs some more typical report like Aged tickets by buckets.

var start = new GlideDateTime(current.getValue("opened_at")),
	end;
if (JSUtil.notNil(current.getValue("closed_at")))
	end = new GlideDateTime(current.getValue("closed_at"));
else
	end = new GlideDateTime();
current.setValue("u_duration", GlideDateTime.subtract(start, end).getDurationValue());

 

0 Helpfuls