jqPivot in version 4.9

Introduction. The state of the problem in jqGrid 4.7

The code of jqPivot included in free jqGrid 4.8 was almost identical to the code from jqGrid 4.7. It was only some small bug fixes implemented. The bugs reported later after publishing of free jqGrid 4.8 were much more difficult to fix.

The implementation of old jqPivot had many principal problems which could be difficult to solve. The code consists mostly from two parts. In the first part one scans the input data and creates the internal tree structure with some properties holding the input information and the results of calculations of the aggregates which will be the input data of jqGrid. In the second part one generates jqGrid columns (colModel), the grouping header information. The second part are implemented as recursive function. In both parts one uses enumeration by properties of objects. The order of enumeration is very important for some parts, but it could be in general different for different web browsers.

One more important problem of old implementation of jqPivot was the usage of column names based on the properties of input data. The problem is that there are almost no restrictions on the properties of JavaScript and so on the properties of input data. On the other side the column names of colModel will be used to build ids of resulting HTML elements, which produces an important problem. The old code cut only spaces from the input data, but only spaces. It produced another problems. Moreover one used in some other, more complicates cases, the column names which had the space at the end of the name. I don't want to describe all problems in the old code. One of the main problems was the debugging problem of the old code. It was very difficult to debug recursive part of the code to locale the reported bug. Some other simple changes in the code have changed just the order of enumerating properties in some loops and it break the code.

At the end I decided to rewrite the whole code just trying to hold the main compatibility. The main goal was to organize the code in some clear separated parts. In the first part of new code I creates just sorted index of unique X and Y values (see xDimension and yDimension option) of input data. The indexes consists mostly from the unique set of X and Y vectors found in the input data and the references (indexes) of the vectors in the source data. Then the code creates the column (colModel) and column headers. Finally it will be made the calculation of the aggregated values. Such code organizations simplifies debugging and allows to implement new advanced options relatively easy without any side effects.

The options of new implementation of jqPivot in free jqGrid 4.9

The base structure of the options of jqPivot is exactly like before:

$("#grid").jqGrid("jqPivot", data, pivotOptions, gridOptions, ajaxOptions);

The changes exists only in pivotOptions. To describe there I will first remind what jqPivot do. I starts with an typical example of the usage. Let us we have statistic data of selling of different goods/products and we need to analyse the data. We have large set of data: which goods/products, when were sold, to which category the good/product belongs, the price, the number of sold items of goods, the date of selling and many other things. We want to produce the grid with selling statistic grouped by year, mouth and week name. We use for example the texts "first", "second", "third" and "fourth" for the week name inside of the month. The results should looks close to the picture below:

To build such grid we need dynamically build colModel of jqGrid based on the input data. Let us we have the input data as array of items with the following properties for example

{
    "CategoryName": "Baby",
    "ProductName": "Baby Oil",
    "Price": "193.81",
    "Quantity": "1",
    "sellmonth": "7",
    "sellyear": "2011",
    "week": "first"
}

It could be many items of data having CategoryName: "Baby", but we want to group all the data together in one row of the output grid. Such option, which build the rows of resulting grid will be named as xDimension.

xDimension: [
    { dataName: "CategoryName", label: "Category Name" }
]

The items of xDimension defines not only the rows in the resulting grids, but the fist columns too. One have to define at least one item in xDimension, but it's possible to define multiple xDimension. jqGrid will use grouping by the second item of xDimension automatically. One can have additionally add grouping. So that one can collapse the items to see overview and to expand group to see details:

We will use below the following input data

[
    { "CategoryName": "Baby", "Price": "193.81", "sellmonth": "7",  "sellyear": "2011", "week": "first", ... },
    { "CategoryName": "Mom",  "Price": "93.81",  "sellmonth": "12", "sellyear": "2011", "week": "first", ... },
    { "CategoryName": "none", "Price": "93.81",  "sellmonth": "12", "sellyear": "2011", "week": "second", ... },
    { "CategoryName": "none", "Price": "93.81",  "sellmonth": "12", "sellyear": "2011", "week": "third", ... },
    { "CategoryName": "none", "Price": "105.37", "sellmonth": "12", "sellyear": "2011", "week": "third", ... },
    { "CategoryName": "none", "Price": "93.81",  "sellmonth": "12", "sellyear": "2015", "week": "second", ... }
]

and will display the pivot

The input data contains many different unique combinations of the y-vector (year, mouth, week name). All different values of the y-vector defines columns of the grid:

[
    {"sellyear": "2011", "sellmonth": "7",  "week": "first", ...}
    {"sellyear": "2011", "sellmonth": "12", "week": "first", ...}
    {"sellyear": "2011", "sellmonth": "12", "week": "second", ...}
    {"sellyear": "2011", "sellmonth": "12", "week": "third", ...}
    {"sellyear": "2015", "sellmonth": "12", "week": "second", ...}
]

and many different unique "Category Name" values which builds x-vector:

[
    { "CategoryName": "Baby", ... },
    { "CategoryName": "Mom",  ... },
    { "CategoryName": "none", ... },
]