jqGrid Free : Help on design of handling multiple

Question

As a side project, I'm writing a custom tailored resource management web app which will allow project managers within a company to input resources time allocation as a monthly breakdown. Currently it is working like a charm showing only one single year snapshot but as we go towards the end of the year, we need to able to foresee the upcoming months of next year. The column chooser comes handy to hide the months which have passed but as we will be going towards August or September month this year, we would like to be able to manage new or existing resources beginning of next coming year.

Here is what is currently displayed: Thus, I'm stuck on how to design this correctly in a most effective way with jqGrid Free.

Initially, I could send back json data for 2 or more years in one shot and making the grid wider to show snapshots of the whole 2 years. That will probably work if I am able to preset column chooser not to show the whole monthly breakdown data of next year and user will be able to toggle new months of next year when the time comes. Still, I will need to do some trade-offs to display only the whole 2 years set only at the end of each year.

Other than that, I could stick only to one single year and add some previous year / next year buttons so user can go back and forth. But doing that, the server needs to send back and forth data to the client and slows down the whole process.

The amount data is reduced to the amount of projects and its allocated resources for the whole year breakdown in 12 months.

So as many projects with its fixed amount of resources are contributed to one or several projects. My first target is an average of 70 resources for 12 projects per year for one department.

Other department may have more or less, but the focus is for now only for one department.

As I have discussed in the other thread of implementing yearly using subgrids, this way I can repeat the same view of the current grid for different year as a subgrid. I'm still unsure whether or not it is good way to do it.

The way it is currently implemented is that JSON data are pushed to and pulled from server are done via rest services which perform CRUD operations in MySQL database. So, query a full grid with some grouping do take some time to render back to the client. Thus, I need to take into account performance issues.

Is pivot table something I need? Is there any example on this? Oleg mentionned in my other thread of rotateColumnHeaders, but I do not have clue on how to do this? But if this will solve what I'm asking, I definitely go for it.

Any suggestion how I should tackle this?

Any help is greatly appreciated!

Thanks!

More update: Here is the multiple footer rows that I got it working:

Answer 1

The main problem is the content/format of input data, which you have. For example, if the input data are already prepared for displaying in jqGrid like

var data = [
        {code:"A", name:"Project A",
            jan2017:1, feb2017:0, mar2017:0, apr2017:0,
            may2017:0, jun2017:0, jul2017:0, aug2017:0,
            sep2017:0, oct2017:0, nov2017:0, dec2017:1},
        {code:"A", name:"Project A",
            jan2017:1, feb2017:1, mar2017:0, apr2017:0,
            may2017:1, jun2017:0, jul2017:0, aug2017:0,
            sep2017:0, oct2017:1, nov2017:0, dec2017:0}
    ];

then you can create the colModel directly. It could be something like

colModel: [
    { name: "code", label: "Code", width: 50, align: "center" },
    { name: "name", label: "Name", width: 70 },
    { name: "jan2017", label: "Jan", template: intTemplate },
    { name: "feb2017", label: "Feb", template: intTemplate },
    { name: "mar2017", label: "Mar", template: intTemplate },
    { name: "apr2017", label: "Apr", template: intTemplate },
    { name: "may2017", label: "May", template: intTemplate },
    { name: "jun2017", label: "Jun", template: intTemplate },
    { name: "jul2017", label: "Jul", template: intTemplate },
    { name: "aug2017", label: "Aug", template: intTemplate },
    { name: "sep2017", label: "Sep", template: intTemplate },
    { name: "oct2017", label: "Oct", template: intTemplate },
    { name: "nov2017", label: "Nov", template: intTemplate },
    { name: "dec2017", label: "Dec", template: intTemplate },
    { name: "jan2018", label: "Jan", template: intTemplate },
    { name: "feb2018", label: "Feb", template: intTemplate },
    { name: "mar2018", label: "Mar", template: intTemplate },
    { name: "apr2018", label: "Apr", template: intTemplate },
    { name: "may2018", label: "May", template: intTemplate },
    { name: "jun2018", label: "Jun", template: intTemplate },
    { name: "jul2018", label: "Jul", template: intTemplate },
    { name: "aug2018", label: "Aug", template: intTemplate },
    { name: "sep2018", label: "Sep", template: intTemplate },
    { name: "oct2018", label: "Oct", template: intTemplate },
    { name: "nov2018", label: "Nov", template: intTemplate },
    { name: "dec2018", label: "Dec", template: intTemplate }
]

where the column template is, for example, the following

var intTemplate = {
        width: 20, template: "integer",
        align: "center", editable: true
    };

The demo https://jsfiddle.net/d8auuc5r/31/ demonstrates the approach.

Another common case: you have input data, which correspond an item, which describes the resource of a project in a month. Something like

{code:"A", name:"Project A", year: 2017, month: 2, value: 3}

or like

{code:"A", name:"Project A", year: 2017, month: 2, week: 1, value: 1},
{code:"A", name:"Project A", year: 2017, month: 2, week: 2, value: 2}

The week or an additional fields could specify every resource more exactly. In the way one will have more as one item for a {code, name, year, month} combination. jqPivot will first of all sort the input data by properties, which you specifies, then group there and finally the value of some properties (value property in above example) will be aggregated with respect of some aggregation function, for example sum function.

jqPivot allows generates new data and the corresponding colModel. Then it calls setGroupHeaders to create grouping headers and optionally (if frozenStaticCols: true is used) it calls setFrozenColumns to make first columns frozen. The demo https://jsfiddle.net/d8auuc5r/76/ shows both approaches. It uses the following code:

$("#grid2").jqGrid("jqPivot", input, {
    xDimension: [
        { dataName: "name", width: 70, label: "Name" },
        { dataName: "code", width: 50, align: "center",
            skipGrouping: true, label: "Code" }
    ],
    yDimension: [
        { dataName: "year" },
        { dataName: "month", sorttype: "integer",
            label: function (options) { return monthNames[options.yData - 1]; }
        }
    ],
    aggregates: [
        {
            member: "value",
            aggregator: "sum",
            template: "integer",
            align: "center",
            width: 20,
            editable: true
        }
    ],
    frozenStaticCols: true,
    useColSpanStyle: true
},
{
    cmTemplate: { autoResizable: true },
    autoResizing: { compact: true },
    pager: true,
    width: 360,
    iconSet: "fontAwesome",
    caption: "Pivot test",
    shrinkToFit: false,
    viewrecords: false,
    inlineEditing: { keys: true },
    navOptions: { add: false, edit: false, del: false, search: false },
    inlineNavOptions: { add: true, edit: true },
    onInitGrid: function () {
        var $self = $(this),
            p = $self.jqGrid("getGridParam"),
            toRotate = [], i;
        for (i = 2; i < p.colModel.length; i++) {
            toRotate.push(p.colModel[i].name);
        }

        $self.jqGrid("rotateColumnHeaders", toRotate);
    }
}).jqGrid("navGrid")
.jqGrid("inlineNav");

where

var input = [
        {code:"A", name:"Project A", year: 2017, month: 1, value: 1},
        {code:"A", name:"Project A", year: 2017, month: 2, value: 2},
        {code:"A", name:"Project A", year: 2017, month: 2, value: 0},
        {code:"A", name:"Project A", year: 2017, month: 3, value: 1},
        {code:"A", name:"Project A", year: 2017, month: 4, value: 0},
        {code:"A", name:"Project A", year: 2017, month: 5, value: 1},
        {code:"A", name:"Project A", year: 2017, month: 6, value: 0},
        {code:"A", name:"Project A", year: 2017, month: 7, value: 0},
        {code:"A", name:"Project A", year: 2017, month: 8, value: 0},
        {code:"A", name:"Project A", year: 2017, month: 9, value: 0},
        {code:"A", name:"Project A", year: 2017, month: 10, value: 1},
        {code:"A", name:"Project A", year: 2017, month: 11, value: 0},
        {code:"A", name:"Project A", year: 2017, month: 12, value: 1},
        {code:"A", name:"Project A", year: 2018, month: 1, value: 1},
        {code:"A", name:"Project A", year: 2018, month: 2, value: 0},
        {code:"A", name:"Project A", year: 2018, month: 3, value: 1},
        {code:"A", name:"Project A", year: 2018, month: 4, value: 0},
        {code:"A", name:"Project A", year: 2018, month: 5, value: 1},
        {code:"A", name:"Project A", year: 2018, month: 6, value: 0},
        {code:"A", name:"Project A", year: 2018, month: 7, value: 0},
        {code:"A", name:"Project A", year: 2018, month: 8, value: 0},
        {code:"A", name:"Project A", year: 2018, month: 9, value: 0},
        {code:"A", name:"Project A", year: 2018, month: 10, value: 1},
        {code:"A", name:"Project A", year: 2018, month: 11, value: 0},
        {code:"A", name:"Project A", year: 2018, month: 12, value: 1},
    ],
    monthNames = [
        "Jan", "Feb", "Mar", "Apr", "May", "Jun", 
        "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
    ];

One will have some problems with editing such grid, because the columns will be generated dynamically. I just want to explain main ideas of jqPivot and not explain here all features of jqPivot and free jqGrid in general.