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.

It's absolutely unclear how which input data you have and how you nee to organize pivot table. It seems to me that the input data miss some important part of information (`xDimension` fields). In any way the second table, which you included in your question seems to me isn't a pivot table. Sorry, but you described your problem very unclear. — Oleg, Nov 03 '18 at 14:02
Ah, sorry for bad description @Oleg, but I've updated the question again. I hope this is more readable than the previous one. Regarding xDimension fields, they are not very clear at the point of writing the code. so, it will be better if you can look into both images and suggest/hint me what to do. — Chandan, Nov 05 '18 at 06:33
Sorry, but your question is still absolutely unclear. Both images have no relation to pivot table. All pivot tables have some fixed first columns (`xDimension`). It's typically 1-2 columns. The elements of your input data have different properties. It's absolutely unclear which field you need to aggregate, and which use as `yDimension`. The `yDimension` properties should typically exist in every element of input data. The more elements you have in `yDimension` the more levels of headers you get in the header of resulting pivot tables. 7 levels have really no sense. — Oleg, Nov 05 '18 at 16:53
For example, the demo https://jsfiddle.net/tnr2dgkv/ (from [the answer](https://stackoverflow.com/a/41324926/315935)) have 2 levels of column headers: `salesYear` and `salesMonth`. Adding the 3-d `salesdate` increase the header level once more https://jsfiddle.net/OlegKi/tnr2dgkv/5/. All the fields `salesYear`, `salesMonth` and `salesdate` exist (or be filled) in input data and all are levels of the same thing: the date. Your `yDimension` seems be absolutely wrong for pivot. — Oleg, Nov 05 '18 at 17:03
Thanks @Oleg for your valuable comments. I got it. But then can you tell me how to show the jqrid like second image? — Chandan, Nov 05 '18 at 20:25
I repeat that I have still no idea in which form you get the input data. Should jqGrid make some **pre-processing of input data** (aggregation, grouping) which is the main work of jqPivot, or the data could be displayed directly as grid or probably just as pure HTML ``? The second picture is definitively not a jqGrid. — Oleg, Nov 05 '18 at 20:30
Hi @Oleg, I'm getting data as in json in Ajax success from controller. No, jgris doesn't do anything. And Yes, its absolutely not handle by jqPivot. So leave this option. Moreover, yes your second observation is right, data needs to displayed directly as a grid or just a pure html table. — Chandan, Nov 07 '18 at 06:41

Alexandru Severin · Answer 1 · 2018-11-07T14:07:13.403

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/

How to pivot columns in free jqgrid 4.15.4

1 Answers1