jqGrid - hide specific column or row in pivot

Question

If we want to hide the column (month 7, range: border black)

or hide the column(year 2015, border green)

Is there any solution or jqGrid options that we can use it?

Answer 1

jqPivot method have no special options which allow you to hide some columns directly, but one can use beforeInitGrid callback to make any modifications of colModel before the grid will be created. The only problem is: one have to understand the exact name conversion for the columns used by jqPivot to write correct code of beforeInitGrid callback. So I describe some internals structures of jqPivot first of all and then the code of beforeInitGrid callback will be clear to understand. I explain the problem based on the example. I recommend all to read the wiki article too which provides additional information about jqPivot implemented in free jqGrid 4.9.0.

First of all I have to remind that jqPivot get as input data which will be indexed based on xDimension and yDimension options and then one calculates aggregation function over all items with the same x and y values. The aggregation function will be specified by aggregates parameter. In other words jqPivot is "pre-processor" of input data. It analyses the data and generate new data and colModel which display more compact information about original data.

To implement your requirements one need to understand which column names will be used by jqPivot for the colModel which will be generated. Moreover one need understand how to get the corresponding y values for the column.

For example we have the following input data:

var data = [{
        CategoryName: "Baby", ProductName: "Baby Oil",
        Price: "193.81", Quantity: "1",
        sellmonth: "7",  sellyear: "2011", week: "first"
    }, {
        CategoryName: "Mom",  ProductName: "Shampoo",
        Price: "93.81",  Quantity: "1",
        sellmonth: "12", sellyear: "2011", week: "first"
    }, {
        CategoryName: "none", ProductName: "beauty",
        Price: "93.81",  Quantity: "1",
        sellmonth: "12", sellyear: "2011", week: "second"
    }, {
        CategoryName: "none", ProductName: "beauty",
        Price: "93.81",  Quantity: "1",
        sellmonth: "12", sellyear: "2011", week: "third"
    }, {
        CategoryName: "none", ProductName: "Shampoo",
        Price: "105.37", Quantity: "2",
        sellmonth: "12", sellyear: "2011", week: "third"
    }, {
        CategoryName: "none", ProductName: "beauty",
        Price: "93.81",  Quantity: "1",
        sellmonth: "12", sellyear: "2015", week: "second"
    }];

and we use as jqPivot options

$("#pvtCrewAttendance").jqGrid("jqPivot",
    data,
    {
        footerTotals: true,
        footerAggregator: "sum",
        totals: true,
        totalHeader: "Grand Total",
        totalText: "<span style='font-style: italic'>Grand {0} {1}</span>",
        xDimension: [
            { dataName: "CategoryName", label: "Category Name", sortorder: "desc" },
            { dataName: "ProductName", label: "Product Name", footerText: "Total:" }
        ],
        yDimension: [
            { dataName: "sellyear",  sorttype: "integer", totalHeader: "Total in {0}" },
            { dataName: "sellmonth", sorttype: "integer" }//,
            //{ dataName: "week" }
        ],
        aggregates: [
            { member: "Price",    aggregator: "sum", summaryType: "sum", label: "{1}" },
            { member: "Quantity", aggregator: "sum", summaryType: "sum", label: "{1}" }
        ]
    },
    {/* jqGrid options ...*/});

The resulting pivot grid will be displayed on the demo:

The above options means than qnique values of CategoryName and ProductName properties of input dat build x-values - the first rows of the grid. It's

[["Baby", "Baby Oil"], ["Mom", "Shampoo"], ["none", "beauty"], ["none", "Shampoo"]]

The above array is xIndex. In the same way the unique y-values are

[["2011", "7"], ["2011", "12"], ["2015", "12"]]

The values build the columns of colModel. If one use totalHeader, totalHeader, totalText or totals: true properties in some yDimension then additional columns with total sum over the group will be included. One uses totalHeader for dataName: "sellyear" in the above example. It means that additional two columns with both aggregates (sum by Price and sum by Quantity) will be inserted at the end of columns having sellyear "2011" and "2015".

The first names of columns of the grid will be "x0" and "x1" (corresponds the number of items in xDimension). Then there are columns which names starts with y and the ending a0 and a1 (corresponds the number of items in aggregates). The final two "total" columns have the names "ta0" and "ta1" (corresponds the number of items in aggregates). If aggregates contains only from one element that the suffixes (ending) a0 and a1 will be missing in the column which starts with y or t. The grouping total columns have the names which starts with y have t in the middle and a at the end (like y1t0a0). I includes an example on column names from the example above

I hope that one will see the column names which I wrote in red color. It's the name values for all 14 columns: x0, x1, y0a0, y0a1, y1a0, y1a1, y1t0a0, y1t0a1, y2a0, y2a1, y2t0a0, y2t0a1, ta0, ta1.

Now it's important to mention that jqPivot includes xIndex and yIndex used for building of pivot table inside. To be exactly one can get pivotOptions parameter of jqGrid and examine xIndex.items and yIndex.items properties. One will see the arrays of items which I included above.

Finally one have now enough information to understand the below code used in the demo which hides the columns which you asked:

The demo uses the following beforeInitGrid which hides the required columns:

beforeInitGrid: function () {
    var $self = $(this), p = $self.jqGrid("getGridParam"),
        yItems = p.pivotOptions.yIndex.items, matches, iy, y,
        colModel = p.colModel, i, cm, l = colModel.length, cmName;

    for (i = 0; i < l; i++) {
        cm = colModel[i];
        cmName = cm.name;
        if (cmName.charAt(0) === "y") { // x, y, t
            // aggregation column
            matches = /^([x|y])(\d+)(t(\d+))?(a)?(\d+)/.exec(cmName);
            if (matches !== null && matches.length > 1) {
                // matches[2] - iy - index if y item
                // matches[4] - undefined or total group index
                // matches[6] - ia - aggregation index
                iy = parseInt(matches[2], 10);
                y = yItems[iy];
                if (y != null && (y[0] === "2015" || (y[0] === "2011" && y[1] === "7"))) {
                    cm.hidden = true;
                }
            }
        }
    }
}