Grouping


Guriddo jqGrid support two type of grouping:

  • Header grouping
  • Data grouping.

These two types of grouping can be used independent and together and are used with success in Pivot Grid.

The code related to both types of grouping is in file: grid.grouping.js

Header grouping

Header grouping is a way to add additional columns above the header grid rows, so that they are grouped in a way you need them. Typical implementation can look like the picture below.

Screenshot

We support two types of grouping - with colSpan enabled and colSpan disabled.
If the colSpan is disabled (the default) the headers that do not have grouping have a additional cell above it.
If the colSpan is enabled there is no additional cell above the non-grouped column and it is considered as one column.

Limitations

Currently header grouping has the following limitations:

  • Sortable columns is not compatible with grouping when used together. Only one of these feature can be used, but not booth together
  • Column Chooser is not compatible with the header grouping

Set group headers

Grouping of the header should be used after the grid is created. For this purpose a method setGroupHeaders is used.

Typical implementation can look like this:

$("#jqGrid").jqGrid({
    url: 'data.json',
    mtype: "GET",
    datatype: "json",
    page: 1,
    colModel: [
        {   
          label : "Order ID",
          name: 'OrderID',
          key: true,
        }, {
          label: "Order Date",
          name: 'OrderDate',
          width: 150,
          },                    
        }, {
          label: "Customer ID",
          name: 'CustomerID',
        },{
          label: "Freight",
          name: 'Freight'
        },{
          label : "Ship Name",
          name: 'ShipName',
        },{
          label : "Ship City",
          name: 'ShipCity',
        }
        },{
          label : "Ship Country",
          name: 'ShipCountry',
      }
    ],
    //loadonce: true,
    viewrecords: true,
    width: 780,
    height: 250,
    rowNum: 10,
    pager: "#jqGridPager"
});
$("#jqGrid").jqGrid('setGroupHeaders', {
  useColSpanStyle:false,
  groupHeaders : [
    { "startColumnName":'OrderID', "numberOfColumns":2, "titleText":'Order Info' },
    { "startColumnName":'ShipName', "numberOfColumns":3, "titleText":'Shipping Details' }
  ]
});

The method setGroupHeaders creates the group of headers in the grid, after the creation of the grid.

Note

The group headers options are stored as grid parameter. This grid parameters is called groupHeader and can be requested with getGridParam method after it is run and there is no call of destroyGroupHeader method (see below).

Calling convetions:

$("#grid_id").jqGrid('setGroupHeaders', object options )

Where the default properties of options are :

options = {
  useColSpanStyle :  false,
  groupHeaders : []
}

and have the following meaning:

  • useColSpanStyle boolean - Determine if the non grouping header cell should be have cell above it - value of false, or the column should be treated as one combining boot - true
  • groupHeaders - array- set of object(s) which describes the header grouping texts and rules. Bellow is the list of the options of every element of this array
    • startColumnName - string - The name from colModel from which the grouping header begin, including the same field
    • numberOfColumns - integer - The number of columns which are included for this group. Note that the number start from the startColumnName. If the column is hidden it is skipped and as result the group does not contain the field, but the method count it.
    • titleText - string - The text for this group. The text can contain html tags

Note

The method can be called several times one after other and in this case the last called setGroupHeader will be put at first place (below the main grid header) the before last above it and etc. In all cases the names from colModel should be used in startColumnName property.

Using the previous example if we change the useColSpanStyle to true we will obtain the following picture:

$("#jqGrid").jqGrid('setGroupHeaders', {
 useColSpanStyle:true,
 groupHeaders : [
   { "startColumnName":'OrderID', "numberOfColumns":2, "titleText":'Order Info' },
   { "startColumnName":'ShipName', "numberOfColumns":3, "titleText":'Shipping Details' }
 ]
});

Screenshot

Destroy headers

To destroy group header call the destroyGroupHeader method. This method returns back the grid headers to its initial state.

Calling conventions

...
jQuery("#grid_id").jqGrid('destroyGroupHeader', boolean keepOptions);
...

where
- boolean keepOptions if set to false the group header(s) is removed, but the groupHeader option in grid is not empty and can be used later.

Data grouping

Grouping is a way to group data by diffrent criteria. jqGrid support multiple level of grouping.

The simple way to group in jqGrid is to enable grouping with the grid option grouping:true and define a field name on which grouping occurs. The name should correspond to the name in colModel The definition is done with array groupField which is a part of another grid option groupingView.

It is important to note that if you want the grouping to be correct, then the data should come from server to the grid sorted by that field. When we are in local mode (the data is an array) the data is grouped (sorted) automatically so there is no need to define any additional sort column.

In order to inform the server that we want to have a grouped data, jqGrid add to the sidx parameter the groupField name on which we group and then the sortname parameter. This is done only if we have enabled the grouping and the data is remote.

jQuery("#grid_id").jqGrid({
  ...
  sortname : 'invdate',
  sortorder : 'desc',
  grouping:true,
  groupingView : {
    groupField : ['name'],
    groupOrder: ['asc']
  },
  caption: "Grouping"
  ...
});

then the sidx and sord parameters will become:

sidx = name asc, invdate
sord = desc

As can be seen the grouped field(s) is set in the beginning of the string and the sortname parameter as last.

We should get this information from the request, perform the appropriate sorting server side and return the requested data to the grid. The grid then get the data and do the grouping adding the appropriate (defined) headers and footers.

Limitations

When the grouping is enabled, the following options will be set explicit into the code:

  • scroll = false;
  • rownumbers = false;
  • treeGrid = false;
  • gridview = true (afterInsertRow does not fire too);

Please refer in grid options for detailed information on these options.

Options

All options in grouping are set as grid options and can be changed dynamically using the setGridParam method. Two options are related to grouping

  • grouping
  • groupingView

The first option grouping is Boolean and enables or disables the grouping feature into the grid. The default values of this option is false. To enable grouping set it to true - i.e grouping : true

The groupingView option is actually a object and consist a lot of other options. Below is a example on how this should be used:

jQuery("#grid").jqGrid({
  ...
  groupingView : {
     groupField : ['name', 'invdate'],
     groupOrder : ['asc', 'desc']
  }
  ...
});

Below is the list of the options that are part of groupingView option

Note

Any options below, which is array, should have the same length as the groupField array, when defined

Property Type Description Default
groupField array Defines the name from colModel on which we group. The first value is the first level, the second values is the second level and etc. empty
groupOrder array Defines the initial sort order of the group level. Can be asc for ascending or desc for descending order. If the grouping is enabled the default value is asc. empty
groupText array Defines the grouping header text for the group level that will be displayed in the grid. By default if defined the value if {0} which means that the group value name will be displayed. It is possible to specify another value {1} which mean that the total cont of this group will be displayed too. It is possible to set here any valid html content. empty
groupColumnShow array Show/Hide the column on which we group. The value here should be a boolean true/false for the group level. If the grouping is enabled we set this value to true. empty
groupSummary array Enable or disable the summary (footer) row of the current group level. If grouping is set the default value for the group is false. empty
groupSummaryPos array Set the position of the summary row at current group level. Possible values - header or footer. If set to header the summary values are placed at the same row where the group values is. If footer is set additional row at end of the group level is build-ed and the summary values are placed here footer
hideFirstGroupCol boolean If set to true the values at first column are replaced with empty ones so that we have a pretty view. This usually is set ih the first column is a group column false
showSummaryOnHide boolean Show or hide the summary (footer) row when we collapse the group. false
groupCollapse boolean Defines if the initially the grid should show or hide the detailed rows of the group. false
plusicon string Set the icon from jQuery UI Theme Roller or Bootstrap that will be used if the grouped row is collapsed. The default is get from styleUI object property grouping for the CSS framework used
minusicon string Set the icon from jQuery UI Theme Roller or Bootstrap that will be used if the grouped row is expanded. The default is get from styleUI object property grouping for the CSS framework used
isInTheSameGroup array The elements of the array correspond to the number of the groups. Every element of this array is a function which should return true or false. In case if it return false the element will be added in the new group. Parameters passed to this function are : previous value, current value, group index, group object null
formatDisplayField array The elements of the array correspond to the number of the groups. Every element of this array is a function which should return a value, which will be display the grouped value. Parameters passed to this function are: current value, source value, colModel option, group index and group object. null

The last two functions are very useful to implement custom grouping and display of the grouped values.

Example:

By default jqGrid groups the values case-sensitive. To group the values case-insensitive we can use the isInTheSameGroup and formatDisplayField properties to solve the problem. Bellow is how this can be done

$("#grid_id").jqGrid({
  colModel :[
    {
      name :"name", index:"name"...
    },{
      ...
    }
    ...
  ],
  grouping : true,
  groupingView: {
    groupField: ['name'],
    formatDisplayField: [
        //display the filed in lowecase
        function (dspVal, value, cmopt, level, grpopt) {
            return String(dspVal).toLowerCase();
        }
    ],
    groupColumnShow: [true],
    isInTheSameGroup: [
        function (x, y, index, grpopt) {
            // compare the values
            return String(x).toLowerCase() === String(y).toLowerCase();
        }
    ]
  }
  ...
});

The callbacks isInTheSameGroup[0] and formatDisplayField[0] will be used by grouping by groupField[0]. Because jqGrid support multilevel grouping the isInTheSameGroup and formatDisplayField properties are arrays of callback functions instead of just callback function.

The function isInTheSameGroup[level] (where level correspond to the level of groupField array) will be used consequently to compare the value of grouping column from of previous row with the corresponding value of the current row. The function isInTheSameGroup[level] will be called with the values. If the callback returns true then the row will be grouped with the previous one.

The callback formatDisplayField[level] allows to customize the information displayed in the grouping header. In the example above it is converted to lowercase.

colModel options

Additionally if the group summary footer row is enabled we use a option in column model to set the type of the summary field. We add the following options in colModel and its names are: summaryType, summaryTpl, summaryRound and summaryRoundType.

summaryType

This option can be a string with certain values or a user defined function. Bellow is a example of using this option:

jQuery("#grid").jqGrid({
  ...
  colModel : [
     {..},
     {name:'amount', formatter:'number', summaryType:'sum'},
     ...
  ],
  ...
});

The option determines what type of calculation we should do with the current group value applied to column. Currently we support the following build in functions:

  • sum - apply the sum function to the current group value and return the result
  • count - apply the count function to the current group value and return the result
  • avg - apply the average function to the current group value and return the result
  • min - apply the min function to the current group value and return the result
  • max - apply the max function to the current group value and return the result
  • function - use custom function for calculations.

The option can be defined as function. If defined we pass three parameters to it - the current value, the name and the record object. The function should return value. Note that this value will be used again for the group value until it changes.

Bellow is a example on using this function - simulating the sum function.

function mysum(val, name, record)
{
  return parseFloat(val||0) + parseFloat((record[name]||0));
}

jQuery("#grid").jqGrid({
  ...
  colModel : [
     {..},
     {name:'amount', formatter:'number', summaryType:mysum},
     ...
  ],
  ...
});

summaryTpl

This option acts as template which can be used in the summary footer row. By default its value is defined as {0} - which means that this will print the calculated (summary) value. A second parameter is possible to be added {1} which mean the count of records to the current group The parameter can contain any valid HTML code.

summaryRound

{..., summaryRound: number_of_digits, ... }

By default this parameter is not defined in colModel when grouping is on. The number_of_digits by default is not defined. This options determines the length of the remaining part after the decimal point.

summaryRoundType

{ summaryRoundType: 'round' | 'fixed' }

This parameter works only if the summaryRound is defined. and sets the rounding method selector.

Parameter round means 'just round it and do not keep trailing zeroes'. fixed means 'round it and keep the given fixed number of digits even zeroes'.

The default option is set to round

Group methods

Bellow are the methods that can be used with the grouping.


groupingToggle(string groupid)

Toggles the group identified by groupid. The groupid is a combination of grid id plus 'ghead_' plus the current count number in the grid view. By example if the grid id is named mygrid the the second grouping value in grid will become mygridghead_1

paremeters

  • string groupid - the id of the group to toggle

return
none


groupingGroupBy(mixed name, [object options])

Perform a grouping by given name. The previous grouping is destroyed and new one is applied. A set of additional options can be set with the option parameter. The groupingView object is extended with the options parameter.

parameters

  • mixed name - can be name from col model or array with a names from colModel. Replaces the groupField array.
  • object options - a set of allowed options for groupView

return
grid object


groupingRemove( boolean current)

Remove the current grouping and set the grouping parameter to false. If the parameter current is set to true all the headers and footers are removed without triggering the grid. If the parameter is false (default) the grid is triggered.
and grouping parameter is set to false. Note that the groupingView parameter is not destroyed.

parameters

  • boolean current - set to true removed the headers and footers without triggering the grid.

return
grid object