Search code examples
jqueryjqgridjqpivot

jqPivot- how do I just show the data rows and not summary headers?

I am trying to build a jqPivot (sample jsFiddle here: http://jsfiddle.net/reckert/vqetvqoc/1/) I need a single row for with each of the X Dimensions in it, and then the values - what I am getting is

Image of JS Fiddle results

How can I remove the upper collapsing rows so I can get something more like:

enter image description here

I have tried both the trirand jqGrid and free.jqGrid and get similar results. Here is the code I am using: 

var mydata = [{
  "System": "Central Product Library (CPL)",
  "RFSTitle": "CPL - service support",
  "WorkRequest": "HBCBS00896187",
  "RFS": "40000",
  "PCR": "1",
  "EstimateType": "IE",
  "PHASE": "New",
  "Estimate": 10000.0,
  "BilledTo": null,
  "Lock": false,
  "CategoryID": 2,
  "Category": "BlueList",
  "Rate": 59.1600,
  "MonthEndDate": "2016-01-22T00:00:00",
  "MonthHours": 3750.000000000000,
  "MonthDollars": 221850.000000
}, {
  "System": "Central Product Library (CPL)",
  "RFSTitle": "CPL - service support",
  "WorkRequest": "HBCBS00896187",
  "RFS": "40000",
  "PCR": "1",
  "EstimateType": "IE",
  "PHASE": "New",
  "Estimate": 10000.0,
  "BilledTo": null,
  "Lock": false,
  "CategoryID": 2,
  "Category": "BlueList",
  "Rate": 59.1600,
  "MonthEndDate": "2016-02-26T00:00:00",
  "MonthHours": 6250.000000000000,
  "MonthDollars": 369750.000000
}, {
  "System": "Central Product Library (CPL)",
  "RFSTitle": "CPL - service support",
  "WorkRequest": "HBCBS00896187",
  "RFS": "40000",
  "PCR": "2",
  "EstimateType": "IE",
  "PHASE": "1",
  "Estimate": 2222.0,
  "BilledTo": null,
  "Lock": false,
  "CategoryID": 2,
  "Category": "BlueList",
  "Rate": 59.1600,
  "MonthEndDate": "2016-01-22T00:00:00",
  "MonthHours": 740.670000000000,
  "MonthDollars": 43817.850000
}, {
  "System": "Central Product Library (CPL)",
  "RFSTitle": "CPL - service support",
  "WorkRequest": "HBCBS00896187",
  "RFS": "40000",
  "PCR": "2",
  "EstimateType": "IE",
  "PHASE": "1",
  "Estimate": 2222.0,
  "BilledTo": null,
  "Lock": false,
  "CategoryID": 2,
  "Category": "BlueList",
  "Rate": 59.1600,
  "MonthEndDate": "2016-02-26T00:00:00",
  "MonthHours": 1234.450000000000,
  "MonthDollars": 73029.750000
}, {
  "System": "Central Product Library (CPL)",
  "RFSTitle": "CPL - service support",
  "WorkRequest": "HBCBS00896187",
  "RFS": "40000",
  "PCR": "2",
  "EstimateType": "IE",
  "PHASE": "1",
  "Estimate": 2222.0,
  "BilledTo": null,
  "Lock": false,
  "CategoryID": 2,
  "Category": "BlueList",
  "Rate": 59.1600,
  "MonthEndDate": "2016-03-25T00:00:00",
  "MonthHours": 246.890000000000,
  "MonthDollars": 14605.950000
}];


var grid = $("#grid");

grid.jqGrid('jqPivot',
  mydata, {
    xDimension: [

      {
        groupfield: false,
        groupSummary: false,
        width: 160,
        dataName: 'RFS',
        label: 'RFS'
      }, {
        isGroupField: false,
        groupSummary: false,
        width: 160,
        dataName: 'WorkRequest',
        label: 'WorkRequest'
      },

      {
        groupfield: false,
        groupSummary: false,
        width: 160,
        dataName: 'RFSTitle',
        label: 'Title'
      }, {
        groupfield: false,
        groupSummary: false,
        width: 160,
        dataName: 'Category',
        label: 'Category'
      }, {
        groupfield: false,
        groupSummary: false,
        width: 160,
        dataName: 'Phase',
        label: 'Phase'
      }, {
        groupfield: false,
        groupSummary: false,
        width: 160,
        dataName: 'Rate',
        label: 'Rate'
      },
    ],
    yDimension: [{
        dataName: 'MonthEndDate'
      }
      //,            { dataName: 'PCR' }
    ],
    aggregates: [{
      aggregator: 'sum',
      width: 160,
      member: 'MonthHours',
      label: "Hours"
    }, {
      aggregator: 'sum',
      width: 160,
      member: 'MonthDollars',
      label: "Dollars"
    }],
    groupSummary: false,
    colTotals: true
  },
  // grid options
  {
    groupingView: {
      hideFirstGroupCol: false,
      groupSummary: [false, false, false, false, false, false],
      groupSummaryPos: ["trailer", "trailer", "trailer", "trailer", "trailer", "trailer"],
    },
    pager: true,
    pager: "#MonthlyBillPivotPager",
    iconSet: "fontAwesome",
    cmTemplate: {
      autoResizable: true
    },
    shrinkToFit: false,
    autoresizeOnLoad: true,
    autoResizing: {
      compact: true
    },
    caption: 'RFS Billing',
    hideFirstGroupCol: false
  }
);
Solution

  • You code have small errors in the names of values of some properties or options. You use for example groupSummaryPos which contains non-existing values: "trailer", but the only allows values are: "header" or "footer". I removed just all unneeded properties. I'd recommend you to read the wiki article where I described the options of jqPivot used in free jqGrid.

    The main problem, which you have is the problem with grouping of the data. The reason of the problem is the usage of wrong name of PHASE property. The source data contains the property, but you used dataName: 'Phase' (wrong case!!!):

    {
    groupfield: false,
    groupSummary: false,
    width: 160,
    dataName: 'Phase',
    label: 'Phase'
}

    To fix the problem you should change the item of xDimension to

    {
    width: 80,
    dataName: 'PHASE',
    label: 'Phase'
}

    Additionally I removed autoresizeOnLoad: true, because it don't use the texts from the column headers of the top levels.

    You can see the results of grouping on the demo: https://jsfiddle.net/OlegKi/tcxv2o22/4/

    enter image description here