How to hide/show columns on a pivot grid?

Question

The following code generates a pivot grid with two buttons on the tool bar

var data = [{
        "id": 1,
        "make": "toyota",
        "model": "corolla",
        "fuelusagecity": "17",
        "fuelusagehwy": "12",
        "fuelmeasure":'Litre',
        "salesaboveavg": false,
        "totalnumberofsales": 120000.0000,
        "highsalestext": null,
        "salesdate": "2010-12-01"
    }, {
        "id": 2,
        "make": "toyota",
        "model": "corolla",
        "fuelusagecity": "10",
        "fuelusagehwy": "14",
        "salesaboveavg": false,
        "fuelmeasure":'Litre',
        "totalnumberofsales": 100000.0000,
        "highsalestext": "HIGH",
        "salesdate": "2010-12-15"
    }, {
        "id": 3,
        "make": "toyota",
        "model": "belta",
        "fuelusagecity": "15",
        "fuelusagehwy": "10",
        "salesaboveavg": true,
        "fuelmeasure":'Litre',
        "totalnumberofsales": 200000.0000,
        "highsalestext": null,
        "salesdate": "2011-01-10"
    }, {
        "id": 4,
        "make": "toyota",
        "model": "camry",
        "fuelusagecity": "13",
        "fuelusagehwy": "10",
        "fuelmeasure":'Litre',
        "salesaboveavg": false,
        "totalnumberofsales": 300000.0000,
        "highsalestext": "HIGH",
        "salesdate": "2011-04-23"
    }, {
        "id": 5,
        "make": "nissan",
        "model": "skyline",
        "fuelusagecity": "14",
        "fuelusagehwy": "9",
        "fuelmeasure":'Litre',
        "salesaboveavg": true,
        "totalnumberofsales": 500000.0000,
        "highsalestext": "HIGH",
        "salesdate": "2010-09-10"
    }, {
        "id": 6,
        "make": "nissan",
        "model": "zx300",
        "fuelusagecity": "10",
        "fuelusagehwy": "8",
        "fuelmeasure":'Litre',
        "salesaboveavg": false,
        "totalnumberofsales": 400000.0000,
        "highsalestext": null,
        "salesdate": "2012-01-06"
    }];

    /* convert the salesdate in  */
    var i, item, dateParts;
    for (i = 0; i < data.length; i++) {
        item = data[i];
        if (typeof item.salesdate === "string") {
            dateParts = item.salesdate.split("-");
            item.salesYear = dateParts[0];
            item.salesMonth = dateParts[1];
            item.salesDay = dateParts[2];
            item.salesDateFormatted = dateParts[0];
        }
    }

    var myIntTemplate = {
        formatter: "currency",
        align: "right", sorttype: "number",
        searchoptions: { sopt: ["eq", "ne", "lt", "le", "gt", "ge"] },
        formatoptions: { defaultValue: ""}};

    $("#list483").jqGrid("jqPivot",
            data,
            {
                frozenStaticCols: true,
                skipSortByX: true,
                useColSpanStyle: true,
                //defaultFormatting: false,
                xDimension: [
                    { dataName: "make", width: 100, label: "Make" },
                    { dataName: "model", width: 100, label: "Model", align: "center", skipGrouping:true },
                    { dataName: "fuelmeasure", width: 103, label: "Units" },
                    ],
                yDimension: [
                    { dataName: "salesdate", sortorder: "desc"}//,
                    //{ dataName: "salesYear", sorttype: "integer" },
                    //{ dataName: "salesMonth", sorttype: "integer" }
                ],
                aggregates: [{
                    member: "totalnumberofsales",
                    template: myIntTemplate,
                    formatter:function(cellvalue, options, rowObject){
                       if(cellvalue=== undefined){
                           return '';
                       }
                        else{

                           var x = options.rowData.pivotInfos[options.colModel.name].rows[0].highsalestext;

                           if(x==="HIGH")
                           {
                               return x;
                           }
                           else
                           {
                               return cellvalue;
                           }
                       }
                    },
                    cellattr: function (rowId, cellValue, rawObject, cm, rdata) {
                        if (rawObject != null) {
                            var items = rawObject.pivotInfos[cm.name];
                            if (items != null && items.rows != null && items.rows.length > 0) {
                                var isHigh = true, i;
                                for (i = 0; i < items.rows.length; i++) {
                                    if (items.rows[i].highsalestext !== "HIGH") {
                                        isHigh = false;
                                        break;
                                    }
                                }
                                if (isHigh) {
                                    return "class='high-marker'";
                                }
                            }
                        }
                    },
                    aggregator: "max"
                }/*,
                 {
                 member: "totalnumberofsales",
                 aggregator: "count",
                 //template: "integer",
                 label: "{0}"
                 }*/]
            },
            // grid options
            {
                iconSet: "fontAwesome",
                cmTemplate: { autoResizable: true, width: 75 },
                shrinkToFit: false,
                useUnformattedDataForCellAttr: false,
                autoResizing: { compact: true },
                groupingView: {
                    groupField: ["x0"],
                    groupColumnShow: [false],
                    groupText: ["<span class='group-text'>{0}</span>"]
                },
                //width: 450,
                toolbar: [true, "top"],
                pager: true,
                rowNum: 20,
                caption: "<b>Car sales statistics</b>",
                rowList: [5, 10, 20, 100, "10000:All"]
            }
    );    

    $('<div className="clsRangeDiv"><input type="button" id="btnHide" value="Hide" /><input type="button" id="btnShow" value="Show" /></div>').appendTo("#t_list483");

    $('#btnHide').click(function(){
            var datestoHide = ['2011-04-23','2010-12-15'];
        jQuery("#list483").jqGrid('hideCol',datestoHide);
    })

    $('btnShow').click(function(){
        var datestoShow = ['2011-04-23','2010-12-15'];
        jQuery("#list483").jqGrid('showCol',datestoShow);
    })

Here is jsfiddle with the complete code.

The Grid toolbar has two buttons, Hide and show. When the user clicks hide, it should hide the date columns '2011-04-23','2010-12-15' and when user clicks show, it should show the dates columns '2011-04-23','2010-12-15' .

i have used following code to achieve hide and show

on the click event of the hide button

 $('#btnHide').click(function(){
            var datestoHide = ['2011-04-23','2010-12-15'];
        jQuery("#list483").jqGrid('hideCol',datestoHide);
    })

on the click event of the show button

 $('btnShow').click(function(){
        var datestoShow = ['2011-04-23','2010-12-15'];
        jQuery("#list483").jqGrid('showCol',datestoShow);
    })

but the code lines jQuery("#list483").jqGrid('hideCol',datestoHide); and jQuery("#list483").jqGrid('showCol',datestoShow); does not seems to work. why is that and how do i hide/show the columns?

Answer 1

The methods hideCol/showCol allows to hide/show columns of the grids by column name, but there are no columns with the names '2011-04-23' and '2010-12-15'. It's the reason why the original code don't work. The first columns have the names x0, x1, ... and then other group of columns with the names y0, y1, y2, ... follow. Thus one can fix the code to

$('#btnHide').click(function () {
    //var datestoHide = ['2011-04-23','2010-12-15'];
    var datestoHide = ['y1','y3'];
    $("#list483").jqGrid('hideCol', datestoHide);
});

$('#btnShow').click(function () {
    //var datestoShow = ['2011-04-23','2010-12-15'];
    var datestoShow = ['y1','y3'];
    $("#list483").jqGrid('showCol', datestoShow);
});

See https://jsfiddle.net/sppc21dm/3/

One can make the code more dynamic by usage pivotOptions option of jqGrid, which will be set by jqPivot. The pivotOptions option contains many helpfulk information inclusive yIndex property, which one can use to find the

function getYColumnName (yValue) {
    var yIndex = $("#list483").jqGrid("getGridParam", "pivotOptions").yIndex,
        length = yIndex.getIndexLength(), i, item;
    for (i = 0; i < length; i++) {
        item = yIndex.getItem(i);
        // item is array with different y-values.
        // We use yDimension with one element "salesdate", thus every item is array
        // with one element item[0], which we can test to find "salesdate"
        if (yValue === item[0]) {
            return "y" + i;
        }
    }
}
var columnsToHideOrShow = [getYColumnName('2011-04-23'),getYColumnName('2010-12-15')];

$('#btnHide').click(function() {
    $("#list483").jqGrid('hideCol', columnsToHideOrShow);
});

$('#btnShow').click(function(){
    $("#list483").jqGrid('showCol', columnsToHideOrShow);
});

See https://jsfiddle.net/sppc21dm/2/