You're probably not going to like this answer, but...
Edit: Note that I've posted another answer below that uses a report against a custom table.
To my knowledge, there's no way to do this via a report. However, it is possible via creating your own custom widget. Here's the simplest way I know of to do it.
First, you'll need to create your own UI page (on the navigation pane, System UI > UI Pages, and click the New button). Call it render_days_since_p1 (the exact name isn't important, but the render_ part of it is; I'll explain later). Enter the following in the HTML field:
<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<g2:evaluate var="jvar_changes" object="true">
//<![CDATA[
(function daysSinceP1() {
var grIncident = new GlideRecord('incident');
grIncident.addQuery('priority', '1');
grIncident.orderByDesc('opened_at');
grIncident.setLimit(1);
grIncident.query();
if (!grIncident.next()) return 'No P1s';
// I am assuming you want calendar days here, not literally 24-hour periods.
var gdStart = new GlideDateTime(grIncident.getValue('opened_at')).getLocalDate();
var gdEnd = new GlideDateTime().getLocalDate(); // now
return '' + (GlideDate.subtract(gdStart, gdEnd).getNumericValue() / 86400000);
})();
// ]]>
</g2:evaluate>
<h1 style="text-align:center;">$[jvar_changes]</h1>
</j:jelly>
Leave the Category as General, and leave the other fields on the form blank. Here's a screenshot of what your UI Page should look like:
After saving it, you can click the Try It button and make sure the calculation works for you. It should show you the number of days since your last P1.
Now you'll need to encapsulate this into a widget. Pull up your widgets (System UI > Widgets, click New; note that this is NOT the widgets under Performance Analytics, Service Portal, or other headings). Create a new widget called something like "My Widgets" (or whatever name you want to show up as the text in the widget type on your dashbaord, see below...), and enter the following script:
function sections() {
return {
'Days Since P1': {
'type': 'days_since_p1' }
};
}
function render() {
var type = renderer.getPreferences().get("type");
var gf = new GlideForm(renderer.getGC(), "render_" + type, 0);
gf.setDirect(true);
gf.setRenderProperties(renderer.getRenderProperties());
return gf.getRenderedPage();
}
function getEditLink() {
return '';
}
It should look like this when you're done:
After saving this, you should see a "My Widgets" entry available to select as a widget type on your dashboard:
Select "My Widgets" (or whatever you named your widget above), and you should see Days Since P1 as a widget you can add to the dashboard.
Add it to your dashboard, et voila!
A few miscellaneous notes:
- Note how the gf variable in your widget script consists of the string "render_" concatenated with the type of widget. This is why you had to name your UI Page "render_days_since_p1". If you want to name your UI Page something else, feel free to, but make sure that the string that gets passed to GlideForm() matches up with the UI Page name or it will break the widget.
- If you want other types of highly customized content rendered as your custom widget, feel free to add more UI Pages and key/value pairs to the object returned by sections() in the widget. In other words, you don't have to create one widget per UI Page, you can have as many custom UI Pages under your one "My Widgets" widget as you want.
- I heavily empahsize that as far as I know, this is the easiest way to get what you're asking for on a dashboard. Other community members might have better suggestions.
- I won't go into a full discussion of how to write jelly tags or UI Pages, there's plenty enough documentation on that on the ServiceNow web site. But suffice it to say that you can go nuts with this stuff, and you can even write pages to render editing controls so that you can dynamically customize what your UI Page renders without having to edit the back-end code. If you want to expand on this simple example, have at it!
Hope this helps,
--Dennis R
Here's another answer. It's kind of ugly, but it should also get you want you're looking for, and it does have the advantage of being an actual report like others that would appear on your dashboard.
Create a new table called something like Days Since P1 [u_days_since_p1]. Add an integer column to it called Days [u_days]. Just for grins, set the default value to 0. Here's a screenshot:
Now create a new Scheduled Job (System Definition > Scheduled Jobs, click New, then Automatically run a script of your choosing). Name it something like Update P1 Day Count. Copy the following script into the Run the script field:
(function updateP1DayCount() {
var grIncident = new GlideRecord('incident');
grIncident.addQuery('priority', '1');
grIncident.orderByDesc('opened_at');
grIncident.setLimit(1);
grIncident.query();
if (!grIncident.next()) return 'No P1s';
// I am assuming you want calendar days here, not literally 24-hour periods.
var gdStart = new GlideDateTime(grIncident.getValue('opened_at')).getLocalDate();
var gdEnd = new GlideDateTime().getLocalDate(); // now
var days = '' + (GlideDate.subtract(gdStart, gdEnd).getNumericValue() / 86400000);
var grDayCount = new GlideRecord('u_days_since_p1');
grDayCount.query();
if (grDayCount.next()) {
grDayCount.setValue('u_days', days);
grDayCount.update();
// If someone has inserted extra records, delete them so it doesn't
// screw up our count.
while (grDayCount.next()) grDayCount.deleteRecord();
}
else {
grDayCount = new GlideRecord('u_days_since_p1');
grDayCount.newRecord();
grDayCount.setValue('u_days', days);
grDayCount.insert();
}
})();
Set the Run time to Periodically, and have it run every five minutes or so. Your scheduled job should look something like this:
Save that puppy and you should be good to go. Note that there might be a lag up to five minutes when a new P1 incident is created, but hopefully that's acceptable. If it just plain isn't, create a business rule that sets the value to 0 when an incident is created or updated with a priority of P1.
(I admittedly got kind of lazy there. If you really want to spruce it up, have it fire on any priority change, and if the priority changes from P1 to something else, have it recalculate the days per the algorithm above in the scheduled job. Otherwise, if someone changes a ticket from P1 to P3, for example, it will recalculate the time since a P1 within five minutes per the scheduled job.)
At any rate, now create the report. It should use Days Since P1 [u_days_since_p1] as the source table. Set the type to Single Score, and configure it to aggregate as a Sum on the Days column.
Et voila!
Like I said, it ain't pretty, but it might be more suitable to your needs.
Hope this helps,
--Dennis R
