How to pivot columns in free jqgrid 4.15.4

Question

I need one help in free jqgrid. Here I want to show short summary of a project with some formatting. Currently, I've used setGroupHeaders to show multiple column under one group. But then this doesn't look quite user-friendly, so I started looking to alter that a bit.

About the first image: how it is looking currently.

About the second image: How I want the result grid should look?

Update: I am not clear if I should consider the second(result) grid as pivot grid or tree-grid.

Update 2: I've created a fiddle for the same, but it seems that there is something that I'm missing here. The code sample can be found below:

var data = [{
    "id": 1,            
    "deadline":"Deadline",
    "AgreedD":"Agredd Deadline",
    "labelD":"Date",
    "Agreed":"2018-02-11",


},{
 "id": 2,
  "deadline":"Deadline",
"EstimatedD":"Estimated Deadline",
"labelD":"Date",
"Estimated":"2018-02-15"
}
, {
    "id": 2,
   "deadline":"DaysLeft",
    "UAD":"25",
    "UED":"33"
},
 {
    "id": 3,
   "deadline":"Participants",
    "RequiredP":"120",
    "WRec":"88"
},
 {
    "id": 4,
   "deadline":"Utterences",
    "RequiredU":"6000",
    "RecordedU":"4800"
},{
    "id": 5,
   "deadline":"Throughput",
    "RequiredT":"400",
    "ActualT":"12"
},

];
/* convert the salesdate in  */
$("#list483").jqGrid("jqPivot",
data,
{
    frozenStaticCols: true,
    skipSortByX: true,
    useColSpanStyle: true,
    //defaultFormatting: false,
    **xDimension**: [
            {dataName:"deadline"},
        {dataName: "AgreedD" },
        {dataName: "EstimatedD" },
        {dataName: "WRec", sortorder: "desc" },
        {dataName: "UED", sortorder: "desc" },
        {dataName: "Estimated", sortorder: "desc" },

    ],
    **yDimension**: [

         { dataName: "Agreed", width: 100, label: "Agreed" },
         { dataName: "Estimated", width: 100, label: "Estimated" },
         {dataName:"RequiredP", width: 100, label: "RequiredP"},
         {dataName:"UAD", width: 100, label: "UAD"},
         {dataName:"RequiredU", width: 100, label: "RequiredU"},           
         { dataName: "salesYear", sorttype: "integer" },
         { dataName: "salesMonth", sorttype: "integer" }
],
    **aggregates**: [{
        //member: "Agreed",

        //template: "number",
        //template: "integer", //myIntTemplate,

    },
    {
        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: ["x2"],
        groupColumnShow: [false],
        groupText: ["<span class='group-text'>{0}</span>"]
    },
    //width: 450,
    pager: true,
    rowNum: 20,
    //caption: "<b>statistics</b>",
    rowList: [5, 10, 20, 100, "10000:All"]
});    
//var p = $("#list483").jqGrid("getGridParam");
//console.log(JSON.stringify(p.pivotOptions.xIndex));
//console.log(JSON.stringify(p.pivotOptions.yIndex));

So, It would be very helpful if someone can guide me to some examples.

Answer 1

What you are describing cannot easely be achieved using the jqPivot feature, as the data structure you are showing doesn't seem to follow a specific rule (some records contain value for one cell, while other records have values for two cells).

It seems that you only need to display one record with its values split on two lines, without much need of the features ofered by jqGrid, in which case I would recommend not using a grid plugin, but simply creating the DOM manually.

---

However, if you want to use jqgrid, my recommandation is to process the data before passing it to jqgrid and transform it into a structure more suitable for the widget.

You can achieve the desired UI with a grid configuration such as this:

var dataConverted = convertData(data); //Convert your custom data to the structure of colModel

$('#grid').jqGrid({
    //...
    datatype: "local",
    data: dataConverted,
    colModel: [
        {name: 'deadlineType', label: 'Deadline'},
        {name: 'deadlineValue', label: ''},
        {name: 'daysLeftType', label: 'Days left'},
        {name: 'daysLeftValue', label: ''},
        {name: 'participantsType', label: 'Participants'},
        {name: 'participantsValue', label: 'Total'},
        {name: 'qaPassed', label: 'QA passed'}
    ]
});

* minor tweaks needed for colspan, align, date formatters, etc

If the columns are dynamic, you may also generate the colModel while processing your data.

Demo: https://jsfiddle.net/zohalexix/wy43L0tj/2/