Can we write script to change reports filter condition?

AK79
Tera Contributor

Hi All,

I have a requirement, I have created a report on change table and added to a dashboard. I used a filter planned start date is between one week? Currently I am changing it manually every week.

Can anyone suggest can we write any script like scheduled job or anything which automatically add seven days in that??

 

Thanks in Advance

@Ankur Bawiskar @Jaspal Singh @Chuck Tomasi 

1 ACCEPTED SOLUTION

Hi,

update as this and it worked for me

var gr = new GlideRecord('sys_report');
gr.addQuery('sys_id','6a793872073630102011ff208c1ed0d5');
gr.query();
if(gr.next())
{
	var gdt = new GlideDateTime();
	var ed = new GlideDateTime(gdt);
	ed.addDaysUTC(7);

	var query = "end_dateBETWEENjavascript:gs.dateGenerate('" + gdt.getDate() + "','" + "00:00:00" + "')@javascript:gs.dateGenerate('" + ed.getDate() + "','" + "00:00:00" + "')";

	gr.filter = query;
	gr.update();
}

Regards
Ankur

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

View solution in original post

7 REPLIES 7

Ankur Bawiskar
Tera Patron
Tera Patron

Hi,

you can run weekly scheduled job and update the condition

reports are stored in sys_report table

what script did you start and what is not working?

Regards
Ankur

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

Hi Ankur,

Thanks for replying. below script i am trying in scheduled job but seems it is not correct. Please suggest.

 

find_real_file.png

 

and I want to change the below filter date automatically next week when job runs.

find_real_file.png

Hi,

you should get the next week's start and the end and then form the encoded query string and then update the filter when job runs

Regards
Ankur

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

Hi Ankur, 

I have used the below script but the issue is that on the current date and date after 7 days are not populating on the filter.

var gr = new GlideRecord('sys_report');
gr.addQuery('sys_id','519b5ed42f4301106264d2172799b6c0');
gr.query();
if(gr.next())
{
var gdt = new GlideDateTime(gs.nowDateTime());
var ed = new GlideDateTime(gdt);
ed.addDays(7);
gs.print(ed);
gr.filter = gr.filter + 'start_dateBETWEENgdt@ed';
gr.update();
}

Instead of date it is showing variables name, but when i used thease variable in gs.log or gs.print then i am getting right value.

find_real_file.png