How can I "Group by" the output of a script in a report?

Marcel H_
Tera Guru

‎09-23-2020 03:32 PM

I've been trying to find an effective way to create reports grouped by the values that are present in a reference list field since you can't group by lists normally due to the potential for inconsistent output of the comma separated values where they may appear in any order.

So far I've been able to create a script that when run as a background script does the following:

  • Perform a GlideRecord query against the table where the records with the list field are located
  • Output the value of the referenced records in the list to a string value
  • Split the string value by (',')

Output at this stage:

find_real_file.png

  • Take the split values and run a forEach function against them to separate the values onto individual lines
  • Use trim() to remove leading spaces from the lines where found (seems to occur if getting the display value, but not the sys_id)
  • Output the list with each value on a single line

Now what I'd like to be able to do is run a report where I could show the records on the table based on specific conditions (like created on today) and then somehow use the output of the script to group the location values for use in a bar chart or pie graph. Below is the script that I have working in a background script:

var gr = new GlideRecord('sn_imt_monitoring_request_for_entry');
gr.addEncodedQuery('access_granted=true');
gr.query();
while (gr.next()) {
    var locs = gr.u_location_list.getValue().toString();
    locs = locs.split(',');
    locs.forEach(function (locs, index, Arr) {
        gs.print(locs.trim());
    });
}

Final script output:

find_real_file.png

Any ideas on the best way to use this output in a report? I have another report that looks for duplicate company records that uses a Script Include to return the values, but it's not trying to group by a list field, so it's a bit different. Any ideas on a better way to accomplish this?

Preview file
104 KB
Preview file
52 KB
0 Helpfuls
  • 6,080 Views
2 REPLIES 2

sachin_namjoshi
Kilo Patron
Kilo Patron

‎09-23-2020 03:35 PM

 

You need to use GlideAgreegate 

https://developer.servicenow.com/blog.do?p=/post/glideaggregate/

 

Regards,

Sachin

0 Helpfuls

Marcel H_
Tera Guru
In response to sachin_namjoshi

‎09-24-2020 06:02 PM

I created an GlideAggregate script and when run as a background script I get the expected return of all applicable sys_ids (about 50), but when called in a report via a Script Include I'm only getting a few values returned (about 10).

Script Include:

var EntryLocations = Class.create();
EntryLocations.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    entryLocations: function () {
        var locList = [];
        var val = 'u_location_list';
        var ga = new GlideAggregate('sn_imt_monitoring_request_for_entry');
        ga.addEncodedQuery('access_granted=true');
        ga.addAggregate('COUNT', val);
        ga.addHaving('COUNT', '>=', 1);
        ga.query();
        while (ga.next()) {
            locList.push(ga[val].getValue().toString());
            var locs = locList.toString();
            locs = locs.split(',');
            locs.forEach(function (locs, index, arr) {
                locs.trim();
            });
        }
        return locs;
    },

    type: 'EntryLocations'
});

Background Script:

    var locList = [];
    var val = 'u_location_list';
    var ga = new GlideAggregate('sn_imt_monitoring_request_for_entry');
    ga.addEncodedQuery('access_granted=true');
    ga.addAggregate('COUNT', val);
    ga.addHaving('COUNT', '>=', 1);
    ga.query();

    while (ga.next()) {
        locList.push(ga[val].getValue().toString());
        var locs = locList.toString();
        locs = locs.split(',');
        locs.forEach(function (locs, index, arr) {
            locs.trim();
gs.print(locs);	
        });	
    }

 

I also noticed that where the gs.print and/or return are placed in the script it will affect the results, but the return in the script include can't be placed where the gs.print is in the background script or it won't return any results.

I'm sure I'm missing something simple here, but just can't seem to get the output I need.

0 Helpfuls