GlideAggregate - limiting number of records

[MG Casey]

The GlideRecord when making UI Pages makes it very easy to set the number records display by SetLimit().

However, when grouping data with GlideAggregate, setLimit does not seem to come into play. I know I can do addHaving, but I'd much prefer not having to have an arbitrary number that I would need to keep updating as records grows.

What I'm trying to do: Get a list of the top 3 subcategories of incidents for the previous day

[MG Casey]

Anyone have any insight on this?

[James_Neale]

For an aggregate to work it has to go through all the records anyway hence setLimit has no bearing on the results. It sounds like you want to limit the result set to 3 which you can just do programmatically. One way would be to make the while loop max out at 3 with an iterator and another is to put the aggregate results in an array and slice it before working with the data.

If you are concerned about performance then just ensure the ordering of your query is efficient and you have DB indexes set up properly. Also, preventing unnecessary users from accessing it will help. Unless you have tens of thousands of tickets a day you will probably find it will be fine. You could also think about caching the data if that fits your model.

Finally, any reason for not doing this in a daily scheduled report?

Hope that helps.

James

[MG Casey]

No concerns on the performance.

We're actually doing this because ServiceNow reporting doesn't allow for sorting of group-by lists.

Is there any documentation on how to input an iterator in? Is it within the main GlideAggregate top section?

  <g2:evaluate var="jvar_kb">

      var kb = new GlideAggregate('u_incident_rtsd');

      kb.addQuery('opened_at','>','javascript:gs.daysAgoStart(1)');

      kb.addQuery('opened_at','${AMP}lt;','javascript:gs.daysAgoEnd(1)');

      kb.addAggregate('count');

      kb.orderByAggregate('count');

      kb.groupBy('u_subcategory');

      kb.query();

  </g2:evaluate>

[James_Neale]

Right, ok, no problem. Iterators and loops are just a standard programming concept so nothing on the wiki that I know of other than dotted around in example scripts, etc. GlideRecord and GlideAggregate are iterators as they have the next() function. There are numerous sources of help on this, e.g.

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/The_Iterator_protocol

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/for

This example uses a jelly forEach loop to iterate over the GlideAggregate 3 times only:

<g2:evaluate>

   var kb = new GlideAggregate('u_incident_rtsd');

   kb.addQuery('opened_at','>','javascript:gs.daysAgoStart(1)');

   kb.addQuery('opened_at','${AMP}lt;','javascript:gs.daysAgoEnd(1)');

   kb.addAggregate('count');

   kb.orderByAggregate('count');

   kb.groupBy('u_subcategory');

   kb.query();

</g2:evaluate>



<j2:forEach begin="0" end="2">

   <j2:if test="$[kb.next()]">

       $[kb.u_subcategory.getDisplayValue()] => $[kb.getAggregate('COUNT')]<br />

   </j2:if>

</j2:forEach>