Recipient Specific Filter on Scheduled Reports

As many of you are aware, the scheduled report functionality allows you to automatically send PDFs or excels of existing reports on a scheduled basis to certain recipients. As useful as this functionality is, it does come with some caveats – the reports need to be run as a certain individual and this means that any reports you have included with the filter of “user is gs.getUserID()” will simply not work as desired. There is a method to resolve this and it involves utilizing a scheduled job to invoke the scheduled report.

Carry out the following tasks:

1) Create your scheduled report, but make it inactive. You can keep the “Run as” as “System Administrator” if you’d like.

2) Create a scheduled job that will run through the intended recipients – for example, if you are looking to send this to a particular group, query the sys_user_grmember table and find your recipients. For each one, dynamically populate the “Run as” as the intended recipient, as well as the “Recipients” list. When you have finished this, you can run the scheduled report as an object by invoking it as follows:

SncTriggerSynchronizer.executeNow(yourScheduledReportObject)

Please note that you will need to invoke a wait at the end of each cycle, otherwise the job will attempt to run on top of itself and only send the report to the last recipient in the list – This is very important as the script will not work without this wait! The way we accomplished this was using the gs.sleep command to incur a wait.

In the below example, you will observe that for our uses we looped through the “Project Managers” group to send a personalized version of the same exported report to each recipient one at a time. Of course you could do the same thing with any other group of your choosing:

var projManagersArray = [];
var proj = new GlideRecord('pm_project');
proj.addActiveQuery();
proj.addNotNullQuery('project_manager');
proj.query();

while (proj.next()) {
    projManagersArray.push(proj.project_manager.toString());
}

//Make sure each project manager is only sent one report
var arrayUtil = new ArrayUtil();
projManagersArray = arrayUtil.unique(projManagersArray);

//Loop through project managers to see who is in Time groups. If so, generate and send report.
for (var i = 0; i < projManagersArray.length; i++) {
    var projManager = projManagersArray[i];

    var groupGR = new GlideRecord('sys_user_grmember');
    groupGR.addEncodedQuery('group=f7c85ca61b8777003a3a433fbd4bcbc6^ORgroup=3bc85ca61b8777003a3a433fbd4bcbc4^ORgroup=3fc85ca61b8777003a3a433fbd4bcbc1^user=' + projManager);
    groupGR.query();

    while (groupGR.next()) {

        var rec = new GlideRecord('sysauto_report');
        rec.get('b4c237871b0bbb003a3a433fbd4bcbbb');
        rec.run_as = projManager;
        rec.user_list = projManager;
        rec.update();


        SncTriggerSynchronizer.executeNow(rec);

        gs.sleep(10000);
    }

}

Just ensure that when maintaining this script, you remember that it is not entirely managed within the scope of the Scheduled Report. You may want to put a note on the scheduled report (or a link) to the scheduled job in question to keep them paired!