Copy filter conditions string from Reports

[georgechen]

Hi folks,

Anyone could help in the report scripting this time.     I have created a report via View / Run and made it accessible to my team.   To automate a record update process, I need to convert the filter conditions to a query (encoded query ) that is compatible to GlideRecord.

Here is my filters

and here is the script

var grTask = new GlideRecord('task_sla');

var queryStr = 'task.u_resolvedONThis month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()';

queryStr += '^active=false';

queryStr += '^stage!=cancelled';

queryStr += '^task.u_met_sla=false';

queryStr += '^task.sys_class_name=u_finance';

grTask.addEncodedQuery(queryStr);

//grTask.setLimit(1);

grTask.query();

The query is not accurate as this is different from the report above

Any simple way I could copy directly from the report and paste it to the encodedQuery property of GlideRecord.

Any advise would be appreciated.

[guhann · Accepted solution]

If you go to the list view of sys_report table, there must a column named 'Filter' which stores the condition filter of the report as an encoded string format. You can copy the query from this field. If the query is quite lengthy you cannot copy it fully. So just try the below code in background script to fetch the filter query string and you can use it as an EncodedQuery.

var gr = new GlideRecord('sys_report');

gr.get('09549fa3c611227a0131ebb3045da1c3'); // sys_id of the report.

gs.print(gr.filter);

[georgechen]

Thanks Guhan, this script works like a charm.

var gr = new GlideRecord('sys_report');

gr.get('3dc153420d0dd200454f432dba3b1374'); // sys_id of the report.

gs.print(gr.filter);

output

//sys_created_onONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()

//^has_breached=true

//^sys_created_onSAMEAStask.sys_created_on@day

//^stage=breached

//^EQ

//^GROUPBYtask.sys_class_name

[alanwilson]

Another way to get the filter is to schedule the report.  The report name will have an information icon.  You can either actually submit it, or click the icon to get the information window.  The filter is listed.

//cheers

Of course, immediately after posting I realized the information icon on the sys_report list gives the same thing.  //smile

[Tyson3]

Any even easier way to get the filter query from a report would be to open up the report from the administration form view, then right click and do a "Show XML".