In this article I will describe the implementation I used to convert a list filter into a report, and some secondary use cases, such as generating score reports for system tables. If you would simply like to add the feature to your instance, you can download and install the update set attached to this article.
Intro: Requirements
We want to accomplish the following with this feature:
- Given that a user is able to build a filter on a list, we want that user to be able to transition from the List Filter to the Report Builder UI without losing their progress on the list, "as-is"
- The user should be prompted for a report name, and if none is given, generate a unique name.
- The solution should be extensible for plug-and-play with future use cases where report generation could be useful.
- The solution should prevent users from generating reports on system tables (to begin with, this will be overcome later)
First component: Script Include
First we will create an Ajax Script include which will do server-side creation of the report record.
Input parameters:
- sysparm_query = the filter on the report
- sysparm_table = the table to run the report on
- sysparm_rep_name = the title of the report. This will be auto-generated if none provided
- sysparm_type = the internal type of the report, e.g. 'list' or 'single_score'.
This info is enough to get the ball rolling on report conversion.
What's going on in the script:
First we obtain the parameters for the report creation from the client UI Action (see second component below).
Initially we check for whether the table is blocked from report creation. I have used the list of tables provided in the docs, but you could add additional tables here to further restrict the user's ability to generate reports from filters.
You'll notice that there's a check for report type != single_score, we'll come back to this in section 3 below.
Next up is setting the report values. I've added some checks here for the user providing a report name, if they leave the prompt blank it will generate a name in the format "New Report: User Name Date/Time".
We set the User field and created by user fields to ensure that report permissions are set as normal.
After we have inserted the new report, we return the sys_id to the client. This is used to generate a URL for the client to navigate to after it has been created.
If we blocked creation, we instead return null so that the client knows we did not insert a record and that can be handled.
Script Include details:
var ReportGenerator = Class.create();
ReportGenerator.prototype = Object.extendsObject(AbstractAjaxProcessor, {
generateReport: function() {
var query = this.getParameter("sysparm_query");
var table = this.getParameter("sysparm_table");
var repName = this.getParameter("sysparm_rep_name");
var repType = this.getParameter("sysparm_type");
var blockedReportTable = false;
//Single score reports can bypass the blocked table filter, they generate read-only score reports.
if (!repType || repType != "single_score") {
var gr = new GlideRecord(table);
ReportGenerator.excludedTables.forEach(function(table) {
if (gr.instanceOf(table)) {
blockedReportTable = true;
}
});
if (blockedReportTable)
return null;
}
var date = new GlideDateTime().getDisplayValue();
var report = new GlideRecord('sys_report');
report.newRecord();
if (repName)
report.setValue("title", repName);
else
report.setValue("title", "New Report: " + gs.getUserDisplayName() + " " + date);
if (repType)
report.setValue("type", repType);
else
report.setValue("type", "list");
report.setValue("filter", query);
report.setValue("table", table);
report.setValue("user", gs.getUserID());
report.setValue("created_by_user", gs.getUserID());
report.insert();
return report.getUniqueValue();
},
type: 'ReportGenerator'
});
//https://docs.servicenow.com/bundle/rome-now-intelligence/page/use/reporting/concept/c_ReportOnSystemTables.html
ReportGenerator.excludedTables = [
"sys_email",
"sys_audit",
"syslog",
"syslog_transaction",
"sys_attachment",
"sys_metadata"
];
Second component: UI Action
Important fields:
- Action name = sysverb_open_report_builder - the name itself needs to start with sysverb to prevent shenanigans with g_list and not having a record selected with a checkbox.
- Table = global - ensures it shows up on every list
- List bottom button = true
- onClick =
openReport() - Isolate script = false - Necessary for window.open to redirect the user to a new tab with the report in it.
What's going on in the script:
First we grab a prompt from the user of what they want their report to be called. This utilises an OOB UI page called "glide_prompt" which is marginally better than using native javascript prompt() to obtain free text user input.
We obtain the encoded query which will become our Report Filter by using g_list.getQuery() This will return the filter used in the breadcrumbs of the list.
Next we pack all the info and send it to the GlideAjax to generate the report.
In the callback function, we wait for either null or a URL. If null, the Ajax refused to generate the report. At this stage we assume it's because the user has tried to run the UI Action on a system table. You could add additional checks and conditions here if you wanted further limitations.
Otherwise, we get a URL which is for the newly generated report. window.open() opens up a new tab with that report loaded.
UI Action Condition:
This condition prevents the UI Action from displaying under Related lists on forms.
!RP.isRelatedList()UI Action Script:
function openReport() {
var prompt = new GlideModal("glide_prompt");
prompt.setTitle("Enter report name");
prompt.setPreference("onPromptComplete",createReport);
prompt.render();
function createReport(result){
var repName = result;
var query = g_list.getQuery();
var ga = new GlideAjax("ReportGenerator");
ga.addParam("sysparm_name", "generateReport");
ga.addParam("sysparm_rep_name", repName);
ga.addParam("sysparm_type","list");
ga.addParam("sysparm_query", query);
ga.addParam("sysparm_table", g_list.getTableName());
ga.getXMLAnswer(callback);
}
}
function callback(answer) {
if (answer == null) {
// Ajax will check and block creating a report on system tables
// Docs: https://docs.servicenow.com/bundle/rome-now-intelligence/page/use/reporting/concept/c_ReportOnSystemTables.html
alert("Reports are not supported on system tables");
return false;
} else {
var url = "/sys_report_template.do?jvar_report_id=" + answer + "";
window.open(url, "blank");
}
}
Third Component: Score Reports on System Tables.
It can be very useful to get a simple score report for a table such as syslog where you have a specific filter set up which can alert you to errors or warnings about a particular application or feature. This extra button will allow you to force-generate a Score report for system tables so that you can add this counter to a dashboard for system health monitoring.
This kind of thing can be easily added by creating a variant UI Action like below:
Important fields:
- Action name = sysverb_open_report_score - the name itself needs to start with sysverb to prevent shenanigans with g_list and not having a record selected with a checkbox.
- Table = global - ensures it shows up on every list
- List bottom button = true
- onClick =
openScoreReport()- making it slightly different to prevent the client window from triggering the wrong one. - Isolate script = false - Necessary for window.open to redirect the user to a new tab with the report in it.
Some caveats:
Once the report is created it is read-only, when the Report Builder detects that the report is on an unsupported table you are prevented from editing it. For this reason it's important to set everything, including the report name, upfront, and make sure the filter doesn't need to be changed after the fact.
Restriction to roles:
I have also restricted this to the admin role to limit the UI action's visibility, since non-admins would almost never go to a system table, they wouldn't need the functionality.
function openScoreReport() {
var prompt = new GlideModal("glide_prompt");
prompt.setTitle("Enter report name. WARNING this cannot be changed later for system tables");
prompt.setPreference("onPromptComplete",createReport);
prompt.render();
function createReport(result){
var repName = result;
var query = g_list.getQuery();
var ga = new GlideAjax("ReportGenerator");
ga.addParam("sysparm_name", "generateReport");
ga.addParam("sysparm_rep_name", repName);
ga.addParam("sysparm_type","single_score");
ga.addParam("sysparm_query", query);
ga.addParam("sysparm_table", g_list.getTableName());
ga.getXMLAnswer(callback);
}
}
function callback(answer) {
if (answer == null) {
// Ajax will check and block creating a report on system tables
// Docs: https://docs.servicenow.com/bundle/rome-now-intelligence/page/use/reporting/concept/c_ReportOnSystemTables.html
alert("Reports are not supported on system tables");
return false;
} else {
var url = "/sys_report_template.do?jvar_report_id=" + answer + "";
window.open(url, "blank");
}
}
