How to apply a group by of second level to a list

[loteodor]

Hi,

I would like to make a ' group by ' of a ' group by '.

Example, I have a Budget Table that save cost items. For every cost items I have a Value (€), a Cost center and a Type Of Cost (ex. Software, Hardware, ..). I would like to group by Cost Center and for every Cost Center group by Type of Cost, but the tool doesn't offer this basic functionalities (group by of 2nd level). As a matter of fact the System only support group-by on only one field in the reporting tools.

Can I apply a group-by of 2 nd level on every column of the list? For example with UI Action that call a script (GlideAggregate)? Any suggestion on how to do it?

[ohhgr]

Hi Lorenzo,

You could achieve such further levels of grouping using server side scripting. A code snippet like below would achieve the grouping you want.

var budgetTable = new GlideRecord("u_budget"); //your table name

budgetTable.orderBy("u_cost_center");

budgetTable.orderBy("u_type_of_cost");

budgetTable.query();

while(budgetTable.next()) {

//perform some action

}

However, if displaying such grouping is your priority, then you will have to use some logic as above in an UI Page and display the output in an orderly manner.

Hope that helps.

-Mandar

[adiddigi]

There is a whole GlideAggregate API dedicated to "grouping" stuff.

Here is an example :

//always returns a comma seperated sys_ids

DupeUtil.calcDupes   = function(/*table_name*/tbl,/*column_name*/clm){



       var arr   = [];

       var gr = new GlideAggregate(tbl);

       gr.addAggregate('COUNT',clm);

       gr.groupBy(clm);

       gr.addHaving('COUNT','>',1);

       gr.query();

       while(gr.next()){

               arr.push(gr.getValue(clm));

       }

       return arr;

};

GlideAggregate - ServiceNow Wiki

[DrewW]

As everyone has pointed out you can only groupBy multiple fields in code for the most part.   The only thing that I can suggest is do a group by one of the fields while sorting on the other.   Otherwise you are going to have to use the ODBC connection and an external reporting tool.