Methods

jqPivot

jqPivotGrid has the following calling convention

jQuery("#grid").jqGrid('jqPivot', data, pivotoptions, gridoptions, ajaxoptions);

Where

#grid is the id of the table element as used in the jqGrid.

data

- can be a string or array of data to be passed to the pivot. In case the parameter is a string a ajax request is made. The data that should be returned should have a name value pair like this:

{"rows":[
{"CategoryName":"Beverages","ProductName":"Steeleye Stout","Country":"UK","Price":"1008.0000","Quantity":"65"},
{"CategoryName":"Beverages","ProductName":"Laughing Lumberjack Lager","Country":"USA","Price":"140.0000","Quantity":"10"},
{"CategoryName":"Beverages","ProductName":"Lakkalik","Country":"USA","Price":"2160.0000","Quantity":"120"},
...
]}

The “rows” property name can be configured from the ajaxoptions - see below.

In case a array of data is passed the rows property should be omitted and the data should look like this

[
...
{"CategoryName":"Beverages","ProductName":"Steeleye Stout","Country":"UK","Price":"1008.0000","Quantity":"65"},
{"CategoryName":"Beverages","ProductName":"Laughing Lumberjack Lager","Country":"USA","Price":"140.0000","Quantity":"10"},
{"CategoryName":"Beverages","ProductName":"Lakkalik","Country":"USA","Price":"2160.0000","Quantity":"120"},
...
]

The method expect all the needed data to be passed to the pivot. This means that no other special handling on server
should be done. All other next transformations are done at client side and the jqPivotGrid build its new data to be displayed.

pivotoptions

This is a object with following properties:

PropertyTypeDescriptionDefault
aggregatesarray of objectsDefines the aggregates records and builds the pivot. The array should have at minimum one set of object and should always be defined. If no aggregates is set, the pivot will not be build. All properties of colModel can be set. For all available properties see belowempty
colTotalsbooleanIf set to true additional footer row is build. All pivot fields are summarized. Note that only summary function is aplied.false
frozenStaticColsbooleanIf set to true all fields defined in xDimension (see below) are set as frozen so that when scroll horizontally theses fields are always visible.false
groupSummarybooleanWith this option we summarizes the groups and subgroups if they are available. If set to false no summations are vailable.true
groupSummaryPosstring Defines where the summary row should appear - at top of the group or at bottom. Available options are 'header' (top row) or 'footer' (bottom row)header
rowTotalsbooleanIf set to true additional column is build ed which summarizes all column values of the row.false
rowTotalsTextstringSet the column label of the column if rowTotals is set to trueTotal
xDimension array of objects Defines the xDimension used by the grid. These values are the cells from source data that appear as rows in the grid. When more that one object element is set a grouping appear automatically. The hierarchy begin from the first object element. The last element is the last element in grouping. All properties of colModel can be set. See below for the remaining options. empty
yDimension array of objects Defines the yDimension used by the grid. The values are build ed dynamically depend on the data. When more than one object element is set a header grouping appear automatically The hierarchy begin from the first object element. The last element is the last element in header grouping. See below for the remaining options. empty



The pivot can be build only if the xDimension and aggregates are not empty arrays

aggregates

This array contain object elements. The elements build aggregates records and is a collection of items that are gathered together to form a total quantity.Typical it can look like this:

aggregates : [
    { member : 'PrTotal', 
      aggregator : 'sum', 
      label:'Sum', 
      width:50,
      formatter: 'number', 
      summaryType : 'sum', 
      align:'right'
    },{
      member : 'PrTotal', 
      aggregator : 'count', 
      width:50, 
      label: 'Count',
      summaryType : 'sum', 
      formatter:'integer', 
      align:'right'
    }
]

A dimension member is a discrete name or identifier used to identify a data item's position and description within a dimension. The value should be present in source data.

The aggregator defines the action which should be applied. Currently we have

  • sum - Make summarization
  • count - count the number of items in the group
  • min - find the minimal value
  • max - find the maximal value

The label property appear only if the length of the aggregates is greater than one. In this case we create additional group header which summarizes the aggregators. The label a taken from the data.

All properties of colModel can be set here.

xDimension

As described here The dimension is a data set composed of individual, non-overlapping data elements. The primary functions of dimensions are threefold: to provide filtering, grouping and labeling.
Example:

xDimension : [
	{
	        dataName: 'CategoryName', 
		label: 'Category',
		width: 70
	} , {
		dataName: 'ProductName', 
		label: 'Product',
		frozen: false
	}
]

dataName is a cell from data that appear as cell in the row. The value of this should correspond to a name in the data source.
All properties of colModel can be set here.

If the pivot options frozenStaticCols is set to true the forozen property is set automatically. You can overwrite this by set a frozen:false in the object, but be a carefully with the order.

yDimension

The values build dynamically columns depend on the data source.
Example:

yDimension : [
	{
		dataName: 'Country',
		converter: function(Value, xValues) {
			return 'Total Countryies';
		}
	},{
		dataName: 'Country'
	}
]

Possible options are:
dataName is a cell from data which build the column. The value of this should correspond to a name in the data source
converter is function which can convert the source cell data and return new value. The parameters passed to this functions are:

  • Value - the value
  • xValues - the “static” data from the row

gridoptions

These options are options of the grid. You can set any of them.

ajaxoptions

Additionally when the data is obtained via ajax we can set additional ajax options.
Note that currently we support only JSON data, so some ajax options like dataType is not recommended to be changed. Again with this we have a reader option which defines the root data. By example if the data that is returned from the server has the following structure:

{"root":[
{"CategoryName":"Beverages","ProductName":"Steeleye Stout","Country":"UK","Price":"1008.0000","Quantity":"65"},
{"CategoryName":"Beverages","ProductName":"Laughing Lumberjack Lager","Country":"USA","Price":"140.0000","Quantity":"10"},
{"CategoryName":"Beverages","ProductName":"Lakkalik","Country":"USA","Price":"2160.0000","Quantity":"120"},
...
]}

In this case the setting should be like this

jQuery("#grid").jqGrid('jqPivot', 
   "jsondataurl.json", 
   {pivotoptions}, 
   {gridoptions}, 
   { 
     reader:"root"
   }
);

Personal Tools
;