How can I report maximum aggregates?

[Joris Scharpff]

Hello,

We are trying to generate a report that contains the maximum internet connection speed per location. Although this intuitively feels like a very trivial report, simply running a MAX query/aggregate over the data set, we have so far not found any way to accomplish this within the user interface of the reporting module of ServiceNow (e.g. Reports > Create New). Basically we have a table that contains a single record for every combination of location and internet service provider that contains the available connection speed, and we want to generate a report that shows the bext avaiable speed per location, i.e. the MAX value of records grouped by location, regardless of provider. However, the MAX function is not even an option you can select, whereas Count, Sum, Average and Count Distinct are.

Note that using a script this could be easily realised using the GlideAggregate functionality, however we want to generate reports that can be included on dashboard pages and thus we are looking for a way to do so using the Reporting module. Also, you can show MIN and MAX values using the List Control, but this does not allow reporting or graphing over these values and is therefore also not sufficient for our purpose.

Our questions are therefore:

1) Is there a way to compute MIN/MAX values and include them as columns of a list within the Reporting interface that we missed?

2) If not, is there another way to accomplish this so that the results can be included on homepages and used in other reports?

Thank you in advance!

Joris

[Kalaiarasan Pus]

As you said in the question, you can write a script that can act as a reference qualifier and call that in the report filter.

[gyedwab]

Within the platform, the only way to do it is to write code.

If you want this kind of report live, drillable, and embedded in the SN dashboard, you can use Explore Analytics which supports MIN/MAX, as well as average, medians, and formula based calculation across multiple tables.

How you do it:

To do something like this:

Published to the SN Dashboard here:

[jelmer]

Hi guys,

Kalai his advice was very useful. We made a Script Include function that takes an encoded query (filter) as an argument, and returns an array of sys_id values. This is used in reports and views as a 'reference qualifier'.

function Getmaxdownload( filter ) {
   // returns an array of sys_id values of the result entries that have the maximum value Y per job X
   res = new GlideRecord('myapplication_datatable');
   if (!gs.nil(filter)) {
   // filter is e.g. job_id.company.name=Intel
   res.addEncodedQuery(filter);
   }
   res.orderBy('job_id');
   res.orderByDesc('values');
   res.query();


   var max = [];
   var last_id;


   while (res.next()) {
   // due to sorting, every first time a new job_id is seen, the first row has the highest value per job_id
   // therefore, this record's sys_id is stored in the <max> array
   if (res.job_id.toString() != last_id) {
   max.push(res.sys_id.toString());
   }
   // set last_id
   last_id = res.job_id.toString();
   }


   return max;
}

On a table in the backend of ServiceNow you can specify a filter to make whatever report you want,

      [ sys_id ]     is one of     [ javascript:myapplication.Getmaxvalues('OPTIONAL_QUERY') ]

On the Service Portal a filter is specified on a data table widget that filters on a logged in user's CompanyID

      [ Filter ]     sys_idINjavascript:myapplication.Getmaxvalues('job.company='+gs.getUser().getCompanyID().toString())