Report

gautham11
Tera Contributor

How to create a report of incident created from last month 20th to this month 20th. It should be dynamically updated in dashboard when month changes

1 ACCEPTED SOLUTION

Ankur Bawiskar
Tera Patron
Tera Patron

@gautham11 

1 way is to use script in the report filter condition

1) create client callable classless script include and bring in the incidents created from 20th of last month and 20th of this month, return that array of sysIds

function getIncidents() {
    // Get the current date
    var currentDate = new GlideDateTime();

    // Calculate the 20th of the current month
    var currentMonth20th = new GlideDateTime();
    currentMonth20th.setDayOfMonth(20);

    // Calculate the 20th of the previous month
    var previousMonth20th = new GlideDateTime();
    previousMonth20th.addMonthsUTC(-1);
    previousMonth20th.setDayOfMonth(20);

    gs.info(previousMonth20th);
    gs.info(currentMonth20th);
    var arr = [];
    // Create a GlideRecord query for incidents created between previousMonth20th and currentMonth20th
    var gr = new GlideRecord('incident');
    gr.addQuery('sys_created_on', '>=', previousMonth20th);
    gr.addQuery('sys_created_on', '<', currentMonth20th);
    gr.query();

    // Process the results
    while (gr.next()) {
        arr.push(gr.getUniqueValue());
    }
    return arr.toString();
}

AnkurBawiskar_0-1746524902084.png

 

 

2) then in report filter condition use this

sys id [IS ONE OF] javascript&colon; getIncidents();

Note: I have tested the script, but please check and enhance, it gave me correct date as 20th April and 20th May

AnkurBawiskar_1-1746524946526.png

 

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

View solution in original post

6 REPLIES 6

mohdarbaz
Kilo Guru

Hi @gautham11 ,

-In the Conditions section, set the filter to include incidents created between last month 20th and this month 20th.

-Use the Advanced filter to dynamically set the date range:

[Created on] [between] [javascript:gs.beginningOfLastMonth() + 19 days] [and] [javascript:gs.endOfThisMonth() + 19 days]
 

If my response helped, please mark it correct/helpful and close the thread so that it benefits future readers.

 

Regards,

Mohd Arbaz.

Hi @mohdarbaz ,

 

Can you share the screenshot please. I'm not getting option to use above conditions in filter..

Ankur Bawiskar
Tera Patron
Tera Patron

@gautham11 

1 way is to use script in the report filter condition

1) create client callable classless script include and bring in the incidents created from 20th of last month and 20th of this month, return that array of sysIds

function getIncidents() {
    // Get the current date
    var currentDate = new GlideDateTime();

    // Calculate the 20th of the current month
    var currentMonth20th = new GlideDateTime();
    currentMonth20th.setDayOfMonth(20);

    // Calculate the 20th of the previous month
    var previousMonth20th = new GlideDateTime();
    previousMonth20th.addMonthsUTC(-1);
    previousMonth20th.setDayOfMonth(20);

    gs.info(previousMonth20th);
    gs.info(currentMonth20th);
    var arr = [];
    // Create a GlideRecord query for incidents created between previousMonth20th and currentMonth20th
    var gr = new GlideRecord('incident');
    gr.addQuery('sys_created_on', '>=', previousMonth20th);
    gr.addQuery('sys_created_on', '<', currentMonth20th);
    gr.query();

    // Process the results
    while (gr.next()) {
        arr.push(gr.getUniqueValue());
    }
    return arr.toString();
}

AnkurBawiskar_0-1746524902084.png

 

 

2) then in report filter condition use this

sys id [IS ONE OF] javascript&colon; getIncidents();

Note: I have tested the script, but please check and enhance, it gave me correct date as 20th April and 20th May

AnkurBawiskar_1-1746524946526.png

 

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader

Thank you @Ankur Bawiskar,

 

But in 2nd point, I'm able to display incidents in Background script as per conditions.. But in report it's not working..

I tried like both Sys ID [is one of] javascript&colon; scriptIncludeName.functionName();

Sys ID [is one of] javascript&colon; new scriptIncludeName.functionName();