Skip to content

PivotGrid

Simple we can say that pivot table is a program tool that allows you to reorganize and summarize
selected columns and rows of data in a spreadsheet or database table to obtain a desired report.
A pivot table doesn't actually change the spreadsheet or database itself. In database lingo, to
pivot is to turn the data to view it from different perspectives.

More deeper information can be found in Wikipedia

What is jqPivotGrid

jqPivotGrid is a component that you can use with jqGrid to create a pivot grid. Let’s say that we have the following table with some sales data and following fields:

product name, category name, country, price and quantity

If you want to edit, search delete this data, you could easily use jqGrid features.

Screenshot

What would you do if you had to answer the following questions?

  1. What are the order amounts of each category?
  2. What are the order amounts of each in a specific country?
  3. What are the order amounts and quantity of each category and product in specific country and their totals?

Using jqPivotGrid these requirements can be easy solved and this is how they would be done:

Screenshot

Screenshot

Screenshot

Using jqPivotGrid

jqPivotGrid inherits the jqGrid, so you can use most of the features you are already familiar with: column re-sizing, formatter, cell renders, row/cell events etc. The jqPivotGrid can be used when you want to get summation and pivot features that aren’t provided in the jqGrid.

To break down the data-set into categories and country, you have to configure the x and y Dimensions:

xDimension : [{
    dataName: 'CategoryName'
}],
yDimension : [ {
    dataName: 'Country'
}]

Multiple levels are supported, so you can specify them in the y and x Dimensions. Grouping the rows and/or columns is done automatically when two or more levels are set in xDimension and/or yDimension.

Now that you broke down the data-set on the y and x Dimension, it’s time to aggregate the cell values. Several kinds of aggregations are available including: sum, min, max, count etc. Future release will provide your own aggregation function.

aggregates : [{
    member : 'Price',
    aggregator : 'sum',
    width:50,
    label:'Sum'
},{
    member : 'Quantity',
    aggregator : 'count',
    width:50,
    label: 'Count'
}]

As you have probably noticed in the above example, you could aggregate multiple data fields, say "Price" and "Quantity". You can easily achieve this by configuring all required aggregations.

Screenshot

Showing the grand totals for rows and/or columns is pretty easy to configure ("rowTotals: true" or "colTotals: true"). The group totals are also available via rowTotals or colTotals configs.

Currently the pivot grid uses JSON data only as data source.

Configuration

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.

It is not needed to call first jqGrid to build the table. It is needed to call jqPivot method. This method internally reconstruct the data depending on xDimension, yDimension and aggregates and create automatically jqGrid with predefined colModel and grouping options.

When created the jqPivot method automatically set the datatype to local and all future calculations are done at client side.

This method can be combined with Exporting.

Bellow is description of all pivot parameters

data

Commonly the pivot grid uses currently only JSON data as data source. The data parameter can be:

  • array
  • string

When the data is array jqPivot expect the data in a name:value pair like in the following example structure :

[
{"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 case the parameter is a string a ajax request is made. The data that is returned from the server 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.

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 and default values.

Note

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

Property Type Description Default
aggregates array Defines 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 below empty
colTotals boolean If set to true additional footer row is build. All pivot fields are summarized. Note that only summary function is aplied. false
frozenStaticCols boolean If 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
groupSummary boolean With this option we summarizes the groups and subgroups if they are available. If set to false no summations are available. true
groupSummaryPos string 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
rowTotals boolean If set to true additional column is build ed which summarizes all column values of the row. false
rowTotalsText string Set the column label of the column if rowTotals is set to true Total
xDimension array 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 Defines the yDimension used by the grid. The values are build ed dynamically depend on the data. Every element in array is a object. 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
ignoreCase boolean By defalt grouping of data is case-sensitive. If you want the grouping to be non case-sensitive (i.e jqGrid and jqgrid will be grouping in one group when pivot is build-ed) set this option to true false
loadMsg boolean Enables loading message at center of the screen during the build of pivot data (The process can consume in some cases a lot of time). true
onInitPivot function Event which occur when pivot starts building your data. No parameters passed. null
onCompletePivot function Event which occur, when pivot and grid are build. No parameters passed. null

aggregates

This array contain object elements. The element(s) 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 (name property from name:value pair - see above) 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
  • avg - make average
  • function - custom function to define a custom calculations. To this function we pass the following parameters. The function should return value.
    • value - the current value. Initially it is undefined
    • fieldName - the name of the field used for calculations
    • record - the current record as defined in the source data

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 aggregates. The label is taken from the data.

All properties of colModel can be set here.

The below code will use custom function to simulate a summarization.

// function to perform calculation
function mySum( value, fieldName, record) {
  return parseFloat(value || 0) + parseFloat((record[fieldName] || 0));
}
...
// aggregates array
myAggregates : [
    { member : 'PrTotal',
      aggregator : mySum,
      label:'Sum',
      width:50,
      formatter: 'number',
      summaryType : 'sum',
      align:'right'
    },{
      member : 'PrTotal',
      aggregator : 'count',
      width:50,
      label: 'Count',
      summaryType : 'sum',
      formatter:'integer',
      align:'right'
    }
];
...
// calling th epivot
jQuery("#grid").jqGrid('jqPivot',
  'mydataurl.php',
  {
      aggregates : myAggregates,
      ...
  },...);

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',
   isGroupField : true
   label: 'Category',
   width: 70
 } , {
   dataName: 'ProductName',
   isGroupField : false
   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.

isGroupField - by default this option is true and determine if the field should be grouped when set. If this property is used to display certain or important information without to perform any grouping or calculation set it to false.

All properties of colModel can be set here.

If the pivot options frozenStaticCols is set to true the frozen 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 element object 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 corresponded to a dataName and return new value. The parameters passed to this functions are:
- Value - the value from data
- xValues - array - the "static" data from the row defined in xDimention. Actually this is array of the dataName in xDimension

gridoptions

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

ajaxoptions

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 order to read correct this data in pivot we need to define the reader property like this:

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

Example

With this example we will try to explain and build pivot grid from source data to final pivot. Now - lets go first with the data.

Source data

Let’s say that we have a table with sales data with the following fields:

  • category name
  • product name
  • country
  • totalprice
  • quantity

This can be seen on the picture below:

Screenshot

This grid view is achieved with jqGrid with the following code:

jQuery(document).ready(function(){
...
 jQuery("#grid").jqGrid(
 {
   url : "data.json",
   loadonce: true,
   colModel : [
     { name: "CategoryName"},
     { name: "ProductName" },
     { name: "Country"},
     { name: "Price", formatter: 'number', align: "right"},
     { name: "Quantity", formatter:'integer', align:"right"}
   ],
   datatype:"json",
   width: 700,
   rowNum : 10,
   pager: "#pager",
   caption: "Grid"
 });
 ...
});

The data is obtained from Northwind database with the following SQL:

SELECT
   c.CategoryName,
   b.ProductName,
   e.Country,
   SUM( a.Quantity * a.UnitPrice ) AS Price,
   SUM(a.Quantity) AS Quantity
FROM
   order_details a,
   products b,
   categories c,
   orders d,
   customers e
WHERE
   a.ProductID = b.ProductID
   AND b.CategoryID = c.CategoryID
   AND a.OrderID = d.OrderID
   AND d.CustomerID = e.CustomerID
   AND (e.Country = 'UK' OR e.Country = 'USA')
GROUP BY
   a.ProductID,
   e.Country

The JSON response from the server obtained via ajax ( the option url:"data.json") has the following structuure:

{"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"},
...
]}

Let say that your boss want to know the sales for the categories and products for the given countries. Of course this can be achieved with another SQL query, but your provider does not allow you to add new query or to change the existing one. What to do?

You can use the jqPivotGrid to solve this requirement, without to make changes to the server side responses. Let see how to do this, setting the pivot options

Options settings

Looking into the task we can see that the categories and products are fields which will be used in our grid. This data is "static" and we should describe this in jqPivot Grid. This is achieved with xDimension option:

...
xDimension : [
  {
      dataName: 'CategoryName',
      label : 'Category',
      width : 90
  }, {
      dataName: 'ProductName',
      label: 'Product',
      width: 90
  }
]

With this setting we instruct jqPivot Grid that the data will be grouped first by Category and then by Product. These fields should be taken from the JSON response which names are CategoryName and ProductName (see the JSON response above).

Next we should determine which field is the country field. With this setting we will generate dynamic columns (yDimension). Seen into the source data this is the Country field. Now the settings is:

...
yDimension : [
  {
     dataName: 'Country'
  }
]

The missed part is: which fields we should actually summarize? This is achieved with the aggregates option using the fields Price and Quantity.

aggregates : [
{
  member : 'Price',
  aggregator : 'sum',
  width:50,
  label:'Sum'
}, {
  member : 'Quantity',
  aggregator : 'sum',
  width:50,
  label:'Qty'
}]

To summarize the columns and rows we need to set - rowTotal : true and colTotal: true in pivot options.

The final code with jqGrid setting is:

<head>
...
<script type="text/javascript">
jQuery(document).ready(function(){

 jQuery("#grid").jqGrid('jqPivot',
 "data1.json",
 // pivot options
 {
   xDimension : [
                  {dataName: 'CategoryName', label : 'Category', width: 90},
                  {dataName: 'ProductName', label : 'Product', width:90}
               ],
   yDimension : [
                  {dataName: 'Country'}
               ],
   aggregates : [
     {member : 'Price', aggregator : 'sum', width:50, label:'Sum'},
     {member : 'Quantity', aggregator : 'sum', width:50, label:'Qty'}
   ],
   rowTotals: true
   colTotals : true

 },
 // grid options
 {
   width: 700,
   rowNum : 10,
   pager: "#pager",
   caption: "Amounts and quantity by category and product"
 });
});
</script>
 </head>
 <body>
...
    <table id='grid'></table>
    <div id='pager'></div>
...
  </body>
</html>

The final result look like this:

Screenshot