Have you ever run into a situation where you had to change the sharing option for multiple reports – from role-based sharing to group based sharing or vice-versa? If yes – you are not alone. The first method that comes to mind is to edit the report and change the Visible To option from the Sharing settings.
However, this method seems tedious when the situation demands that this change be done for multiple reports. 5 or 10 would have been fine but what if the number of reports to affect is in 100s? This is where today’s scenario comes in.
Let us understand which tables are involved when a report is shared to a role, group or a user.
sys_report – This table stores the report configurations which includes the table on which the report is built, the filter configurations, the report styles and the report type. It also stores the roles with which a report is shared in the “Roles” field.
sys_report_users_groups – This table stores the group and the user with which a report is shared.
From this we understand which tables stores the sharing settings for a report. So, you might think that adding a role to the “Roles” field of the sys_report table will ensure that the report is shared with the role. However, as it seems, this will not suffice. There is a field called “User” on the sys_report table which toggles/controls this visibility. This field determines whether the report is to be shared with a role, a group or a user. It carries 3 values – each of which point to the type of sharing of the report.
GLOBAL – This indicates that the report is shared with everyone. Roles to whom the report is to be shared can be specified in the “Roles” field in the sys_report record.
Group – This indicates that the report is shared with groups or users.
Sys Id of User – This indicates that the report is shared with the user which is done when the type of sharing is “Me”.
Let us now take a situation to understand this better. We have almost 70 reports on the change_request table. They are all currently shared with a certain role (this can be inferred from the User field being marked as GLOBAL). Our task is to share all these reports with a group called “Change Managers”.
We will now run a fix script which will share all these reports with our required group. It will look something like this.
//// THIS FUNCTION WILL CHECK IF THE REPORT IS ALREADY SHARED TO THE GROUP ////
function checkGroupSharing(reportSysId,groupSysId){
var checksharing = new GlideRecord('sys_report_users_groups');
checksharing.addQuery('report_id',reportSysId);
checksharing.addQuery('group_id',groupSysId);
checksharing.query();
return checksharing.hasNext();
}
//// THIS FUNCTION WILL SHARE THE REPORT WITH THE GROUP ////
function shareReportwithGroup(reportSysId,groupSysId){
var sharegroup = new GlideRecord('sys_report_users_groups');
sharegroup.initialize();
sharegroup.setValue('report_id',reportSysId);
sharegroup.setValue('group_id',groupSysId);
sharegroup.insert();
}
var checkreport = new GlideRecord('sys_report');
var groupsysid = "a11f12d8c34fa210e8cab342b401310f"; /// SYS ID OF THE GROUP - CHANGE MANAGERS ///
checkreport.addQuery('table','change_request');
checkreport.query();
while(checkreport.next()){
if(!checkGroupSharing(checkreport.getUniqueValue(),groupsysid)){
shareReportwithGroup(checkreport.getUniqueValue(),groupsysid);
}
checkreport.setValue('user',"group");
}
Running this fix script will change the sharing of the report from role-based to group-based.
Note: It is never a good idea to hard-code sys ids. I have done it here for demonstration purposes.
As can be seen, all the reports have been shared with the appropriate group.
