Trying to create an aging report

Conan_Lloyd · ‎03-23-2012

Bear with me, I'm pretty new to SN and very new to scripting. I'm pretty good at reverse engineering things but not as great at creating from scratch. After searching the forums I noticed that there didn't seem to be a way to get an aging report for incidents. (How many open are 0-2 days old, 3-7 days old, etc.)

Below is what I have tried and it's not quite working yet, anyone wanna point out where I screwed up?

I created a new field called Aging Category (u_aging_category) on the incident table.

I wrote the following script as a scheduled job:

//************************ Set Aging Category **************************************

var optime = current.opened_at.getGlideObject().getNumericValue();
var nowtime = gs.nowDateTime().getGlideObject().getNumericValue();
var totalTime = nowtime - optime ;
var calculation = totalTime/1000/60/60/24;

if (calculation <= 2){
current.u_aging_category = '0-2 Days';
}
if (calculation <= 7 && calculation > 2){
current.u_aging_category = '3-7 Days';
}
if (calculation <= 14 && calculation > 7){
current.u_aging_category = '8-14 Days';
}
if (calculation <= 21 && calculation > 14){
current.u_aging_category = '15-21 Days';
}
if (calculation <= 28 && calculation > 21){
current.u_aging_category = '22-28 Days';
}
if (calculation > 28){
current.u_aging_category = '> 28 Days';
}

In theory this should update the Aging Category field when I run the job and then I can report on it using a Pivot table. Unfortunately, the field is not updating. Any quick thoughts?

Jim Coyne · ‎03-24-2012

I believe the problem is you are not actually querying any records, nor updating them. This is what I have done.

I have a Scheduled Job...

...that calls a function in a Script Include:


function u_updateAgingCategoryField() {
  var elapsedTime = 0;
  var aging = '';
  var currentTimeNow = gs.nowDateTime();
  var gr = new GlideRecord('incident');
  gr.addEncodedQuery('u_aging_category!=>28^ORu_aging_category=');
  gr.query();
  while(gr.next()) {
    elapsedTime = (gs.dateDiff(gr.opened_at, currentTimeNow, true)) /60/60/24;

    //check to see when the item was created
    if (elapsedTime <= 2) aging = '0_2';
    if (elapsedTime > 2)  aging = '3_7';
    if (elapsedTime > 7)  aging = '8_14';
    if (elapsedTime > 14) aging = '15_21';
    if (elapsedTime > 21) aging = '22_28';
    if (elapsedTime > 28) aging = '>28';

    gr.setWorkflow(false);    //skip any Business Rules
    gr.autoSysFields(false);  //do not update system fields
    gr.u_aging_category = aging;
    gr.update();
  }
}

Notice in the function that I am excluding incidents >28 days because we do not want to keep updating them with ">28 Days" all the time.

Now for the trick so that the Pivot Table orders the columns properly - on the Aging Category dictionary record, set the Default value to be "0_2" and the Choice field to "Dropdown without -- None --" and create some Choice entries:

Otherwise the report will sort the columns alphabetically. And so you should end up with the following report:

It is setup in https://demo23.service-now.com/ at the moment. I've also attached an update set that will re-create it for you if you do not get a change to look at demo23 today.

View solution in original post

Inactive_Use494 · ‎11-16-2017

Hi Jim,

Thanks for the helpful article and it just goes to show that years later this is still relevant and useful!

I've implemented this successfully in my DEV environment in the sc_req_item table and I'm updating my reports with the new Aging Category. My one question/concern is that when the script is updating the aging value for each request item, does it take into account the holidays set within the system? I believe we have these holidays/non-working days configured in our System Scheduler > Schedules. SLA's are updated according to these schedules, I'm hoping the Aging Category can be too?

SNOW46 · ‎02-26-2019

Hi Jim,

Have you declared the Script in the Scheduled Job itself as below?

Please let me know if I am going correct? I have configured the Script and clicked on Execute Now.

And in the report also I am getting the records as below.

Please let me know if the Configuration is correct? And do let me know instead of keeping the field Aging category on Incident form can't we hide it and why the default value needs to be set as 0-2?

Thanks..

Jim Coyne · ‎02-26-2019

That looks correct, as long as the values you are setting match the choice list entry values (if any).

The field definitely can be removed from the form itself. The default value just sets the original value of the field so any reports run that day will see those new Incidents as part of that category.

Lavanya34 · ‎08-31-2020

Hi Jim,

Thanks for the solution. We have this requirement too but while taking the report, it shows some of the incidents under an 'empty' column. Can you please provide your input on why we getting an 'empty' column here?

Thanks & Regards,

Lavanya Nagendran

Jim Coyne · ‎08-31-2020

So you are reporting on the "On hold reason" on the Incident table, but you are looking at ALL Incidents, regardless of the value of the State field. Your first condition should be "State = On Hold".