Reporting on Records that match the Locations Covered

[davidglover]

Hi folks!

We have a business need to display records based on physical locations (cmn_locations) in addition to the Assigned To/Group fields.   For example, a Facility Engineer will need to see Work Orders assigned to themselves along with all other Work Orders related to the location(s) they cover.

I am trying to find a way to leverage the User Location Coverage (sys_user_covers_location) table to return the locations and then filter a report that way.   Is anyone aware of a way of leveraging a JavaScript query in the report builder for locations?   What I was thinking is I could use JavaScript to return the location sys_ids, similar to a breadcrumb query, ex:

location=1b6b156013335e005c45b53a6144b080^ORlocation=d647f9aa13b0a24024d67e276144b04c^ORlocation=1154e9e013335e005c45b53a6144b064

Or is there a way to leverage the UI to select the Locations Covered table (screenshot below)?

Thanks in advance!

David

[srinivasthelu]

You are again right David. for some reason it wasn't available for me as well. but, I have just got it there by running a background script.

var gr= new GlideRecord('sys_report')

gr.get('6ba85161df23210068c37a0d3df263e2');

gr.filter='templateISNOTEMPTY^NQlocationINjavascript:test()^EQ^GROUPBYtemplate^TRENDBYactivity_due,year';

gr.update()

There might be a better way, but above worked for me.

[davidglover]

Thank you sir!     That is exactly what I needed (curious to know why the 'IN' operator is not there OOB).

David

[davidglover]

In case any one else comes across this, I thought I would share a quick update.   Rather than running a Fix Script on each report, create a Dynamic Filter so you can call the script easily from the Query Builder.   Example below: