Advanced Reporting: How to calculate datediff between sys_created_on and now?

Mark Nguyen · ‎12-05-2022

Hi PA Community,

I am trying to build a report for "Days since last P1 Incident" for my organizaton. I've attempted to use a Function Field within Reporting however datediff operations have to have some limitiations and I can't think of a way to have Performance Analytics do it.

Anyone have any tips or experience building a similar report?

Mike_R · ‎12-05-2022

For a quick solution,I would probably just create a new dynamic content block

instanceName.service-now.com/content_block_programmatic_list.do?sysparm_query=&sysparm_view=

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
	 <g:evaluate>
		 
		var result = 0;
		var inc = new GlideRecord('incident');
		inc.addQuery('priority', '1');
		inc.orderByDesc('opened_at'); 
		inc.setLimit(1);
		inc.query();
		if(inc.next()) {

		  var start = new GlideDateTime(inc.opened_at);
		  var now = new GlideDateTime();
		  var diff = GlideDateTime.subtract(start, now);
		  var days = diff.getRoundedDayPart();

		  result = days;
		}

		 </g:evaluate>
	<p><span style="font-size: 36pt; color: #ff0000;">	${result}</span></p>

	
</j:jelly>

Longer term solution, look into seeing what's possible through PA.

Mike
* My Collection of ServiceNow Stuff *

View solution in original post

Mike_R · ‎12-05-2022

Is this for a dashboard or do you need something in the reporting module?

Mike
* My Collection of ServiceNow Stuff *

Mark Nguyen · ‎12-05-2022

Hi @Mike_R ,

Thank you for your reply. I need to display this report in a dashboard for my organization.

Mike_R · ‎12-05-2022

Do you need the datediff for every Incident or do you just want for the most recent P1 Incident such as

var inc = new GlideRecord('incident');
inc.addQuery('priority', '1');
inc.orderByDesc('opened_at'); 
inc.setLimit(1);
inc.query();
if(inc.next()) {

  var start = new GlideDateTime(inc.opened_at);
  var now = new GlideDateTime();
  var diff = GlideDateTime.subtract(start, now);
  var days = diff.getRoundedDayPart();
 
  gs.log('Number of days is ' + days);
}

*** Script: Number of days is 356

Mike
* My Collection of ServiceNow Stuff *

Mark Nguyen · ‎12-05-2022

Thanks @Mike_R this is helpful. I am looking for a datediff for most recent P1. How do you suggest visualizing this on a dashboard?