How to categorize rows in the free grid jqgrid?

Question

Hi i have the following table to generate a json dataset

it has the following data i have script teh table

USE [GridSamples]
GO
/****** Object:  Table [dbo].[SalesStats]    Script Date: 12/13/2016 07:34:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SalesStats](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [makes] [nchar](10) NOT NULL,
    [models] [nchar](10) NOT NULL,
    [fuelusagecity] [nchar](10) NOT NULL,
    [fuelusagehwy] [nchar](10) NOT NULL,
    [salesaboveavg] [bit] NOT NULL,
    [totalnumofsales] [money] NOT NULL,
    [highsalestext] [varchar](50) NULL,
    [saledate] [date] NOT NULL,
 CONSTRAINT [PK_SalesStats] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[SalesStats] ON 

GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (1, N'toyota    ', N'corolla   ', N'17        ', N'12        ', 0, 120000.0000, NULL, CAST(0x9A330B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (2, N'toyota    ', N'corolla   ', N'10        ', N'14        ', 0, 100000.0000, N'HIGH', CAST(0xA8330B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (3, N'toyota    ', N'belta     ', N'15        ', N'10        ', 1, 200000.0000, NULL, CAST(0xC2330B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (4, N'toyota    ', N'camry     ', N'13        ', N'10        ', 0, 300000.0000, N'HIGH', CAST(0x29340B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (5, N'nissan    ', N'skyline   ', N'14        ', N'9         ', 1, 500000.0000, N'HIGH', CAST(0x48330B00 AS Date))
GO
INSERT [dbo].[SalesStats] ([id], [makes], [models], [fuelusagecity], [fuelusagehwy], [salesaboveavg], [totalnumofsales], [highsalestext], [saledate]) VALUES (6, N'nissan    ', N'zx300     ', N'10        ', N'8         ', 0, 400000.0000, NULL, CAST(0x2B350B00 AS Date))
GO
SET IDENTITY_INSERT [dbo].[SalesStats] OFF
GO

In my middle tier ASP MVC the controller converts this to the the following json (UPADATED TO HAVE A STRING SalesDate)

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

i have used the following code to generate the grid using JqGrid free-grid. for easiness i have added the above json array to the data array

<!DOCTYPE html>
<html>
<head lang="en">
    <meta charset="UTF-8">
    <title></title>

    <link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/south-street/jquery-ui.css" />
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/free-jqgrid/4.13.5/css/ui.jqgrid.min.css" />
</head>
<body>

<table id="list483"></table>
<div id=""></div>

<!--<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.12.3/jquery.min.js"></script>-->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<!--<script src="https://cdn.jsdelivr.net/free-jqgrid/4.13.5/js/i18n/grid.locale-de.min.js"></script>-->
<script src="https://cdn.jsdelivr.net/free-jqgrid/4.13.5/js/jquery.jqgrid.min.js"></script>
<script>

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


    $("#list483").jqGrid("jqPivot",
            data,
            {
                frozenStaticCols: true,
                xDimension: [
                    {/*x0*/ dataName: "make", width: 200, label: "Make" },
                    {/*x1*/ dataName: "model", skipGrouping: true },
                    {/*x2*/ dataName: "fuelusagecity", hidden: true, skipGrouping: true },
                    {/*x3*/ dataName: "fuelusagehwy", width: 80, align: "center",
                        label: "fuel consumption", skipGrouping: true,
                        formatter: function (cellvalue, options, rowObject) {
                            return rowObject.x2 === null || rowObject.x3 === null ?
                                    "-" :
                            String(rowObject.x2) + "-" + String(cellvalue);
                        }
                    },
                    {/*x4*/ dataName: "salesaboveavg", hidden: true, width: 50, align: "center", skipGrouping: true },
                    {/*x5*/ dataName: "highsalestext", hidden: true, skipGrouping: true }
                ],
                yDimension: [
                    {/*y0*/    dataName: "salesdate",
                    sorttype: "date",
                        formatter: function (cellvalue, options, rowObject) {
                        //var x = rawObject.y0;
                        console.log(rowObject);
//                            return rowObject;
                    }
                }],
                aggregates: [{
                    member: "totalnumberofsales",
                    aggregator: "max"
                }]
            },
// grid options
            {
                iconSet: "fontAwesome",
                cmTemplate: { autoResizable: true, width: 90 },
                shrinkToFit: false,
                useUnformattedDataForCellAttr: false,
                autoResizing: { compact: true },
                groupingView: {
                    groupField: ["x0"],
                    groupColumnShow: [false],
                    groupText: ['<b>{0}</b>']
                },
                width: 450,
                pager: true,
                rowNum: 20,
                rowList: [5, 10, 20, 100, "10000:All"],
                caption: "<b>Car sales statistics</b>"
            }
    );
</script>

</body>
</html>

the generated grid is shown below image link

i have so many questions relating to this grid. but i will post them one by one as answers progresses. But for now the following questions to start off with:

1. when i turned on teh grouping what happened to teh collapse icon image that should appear behind the group name? SOLVED: i have found the answer to this question, the missing font-awesome library caused the issue

2. How do i convert the json dates in teh dates columns and display them in actual dates?SOLVED:This issue was solved once i made the conversion in the middle tier to return saledate a string value

3. The Grid has two entries on the Toyota Corolla but this should be one entry and in that entry, totalnumofsales values should be placed on the two matching dates. AKA in a single row entry to toyota corolla 120000 in 2010-12-01 cell and 100000 in 2010-12-15 also teh fuel consuption value must be the first row value i.e. 10-14

4. for the cell that don't have values shows 0.00 instead of showing 0.00 i need them to display an empty cell

5. How do i turn off the automatic sorting of the grid becuase when grid is generated the groups names are sorted in the alphabetical order but i dont wont grid to sort them, instead keep their original order that comes from the backend

how do i acheive the above?

Answer 1

I think origin of your question are some common misunderstanding what is pivot table and how one can use it. I try to explain all below in details on an example of your input data.

You use the following input data:

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

Every item of data have many properties. Some properties contains different (or even unique) values (like id). Other properties should be grouped together and calculate some aggregation function on another properties on every item inside of the group. I explain the above statement on an example.

The input data contains sale information of some car models. It could be interesting, for example, to display the number of sales of every car model by date or by year etc.

Pivot tables contains three main parameters xDimension, yDimension and aggregates. For example, look at the following picture below The left part (markt in orange) represents xDimension (make and model). It build mostly the rows of the grid. The right part (marked in blue) represents yDimension (year and month of the input items). The lowest level of the information on the right size contains the result of calculation of aggregate functions (max and count) on some input property (it was totalnumberofsales in the example).

If one defined only one aggregate function inside of aggregates parameter, then the name of aggregate function will be not displayed:

Now I have to display what jqPivot do to create jqGrid. First of all it scan all input data by all X and by Y parameters. If you define for example the following pivot model

xDimension: [
    { dataName: "make", width: 100, label: "Make" },
    { dataName: "model", width: 100, label: "Model", align: "center" }
],
yDimension: [
    { dataName: "salesdate", sortorder: "desc" }
],
aggregates: [
    { member: "totalnumberofsales", aggregator: "max" }
]

then all data will be first scanned for the items having the same ["make", "model"] values in xDimension and by ["salesdate"] in yDimension. The input data has 6 elements with the indexes from 0 till 5. The resulting xIndexes and yIndexes contains unique values by x and y and the indexes of source data (from 0 till 5) to the items, which has the data. One can add the following lines after jqPivot call to see the indexes:

var p = $("#list483").jqGrid("getGridParam");
console.log(JSON.stringify(p.pivotOptions.xIndex));
console.log(JSON.stringify(p.pivotOptions.yIndex));

As the result one will see the demo https://jsfiddle.net/oadzsnov/. I include the resulting xIndex and yIndex below. xIndex is

{
    "items": [
        ["toyota", "corolla"],
        ["toyota", "belta"],
        ["toyota", "camry"],
        ["nissan", "skyline"],
        ["nissan", "zx300"]
    ],
    "indexesOfSourceData": [
        [0, 1],
        [2],
        [3],
        [4],
        [5]
    ],
    "trimByCollect": true,
    "caseSensitive": false,
    "skipSort": true,
    "fieldLength": 2,
    "fieldNames": ["make", "model"],
    "fieldSortDirection": [1, 1],
    "fieldCompare": [null, null]
}

yIndex is

{
    "items": [
        ["2012-01-06"],
        ["2011-04-23"],
        ["2011-01-10"],
        ["2010-12-15"],
        ["2010-12-01"],
        ["2010-09-10"]
    ],
    "indexesOfSourceData": [
        [5],
        [3],
        [2],
        [1],
        [0],
        [4]
    ],
    "trimByCollect": true,
    "caseSensitive": false,
    "skipSort": false,
    "fieldLength": 1,
    "fieldNames": ["salesdate"],
    "fieldSortDirection": [-1],
    "fieldCompare": [null]
}

The xIndex.items build the rows of the resulting pivot table and yIndex build the columns. You can see that the rows will be

["toyota", "corolla"],
["toyota", "belta"],
["toyota", "camry"],
["nissan", "skyline"],
["nissan", "zx300"]

and the columns:

["2012-01-06"],
["2011-04-23"],
["2011-01-10"],
["2010-12-15"],
["2010-12-01"],
["2010-09-10"]

One can see additionally that 2 elements of source data (see xIndex.indexesOfSourceData, which is [0, 1]) xIndex.items corresponds the same x-vector ["toyota", "corolla"]. The content of the pivot table (see yellow marked data on the first picture) will be the result of executing the specified aggregate function. We used

aggregates: [
    { member: "totalnumberofsales", aggregator: "max" }
]

and the source elements with the index 0 and 1 are

[{
    ...
    "make": "toyota",
    "model": "corolla",
    ...
    "totalnumberofsales": 120000.0000,
    ...
    "salesdate": "2010-12-01"
}, {
    ...
    "make": "toyota",
    "model": "corolla",
    ...
    "totalnumberofsales": 100000.0000,
    ...
    "salesdate": "2010-12-15"
}

The items have different y-vectors ("salesdate": "2010-12-01" for the first item and "salesdate": "2010-12-15" for the second one). Thus the calculation of aggregates will be very simple: 120000 in the column "2010-12-01", 100000 in the column "2010-12-15" and 0 for all other dates, because there are no ["toyota", "corolla"] sales at the day:

If you would assign separate salesYear and salesMonth properties based on the salesdate, then you can use

xDimension: [
    { dataName: "make", width: 100, label: "Make" },
    { dataName: "model", width: 100, label: "Model", align: "center" }
],
yDimension: [
    { dataName: "salesYear", sorttype: "integer" },
    { dataName: "salesMonth", sorttype: "integer" }
],
aggregates: [{
    member: "totalnumberofsales",
    aggregator: "max"
}]

to create the pivot table. Both first source items has the same salesdate ("2010-12-15" and "2010-12-15"). The calculation of max aggregater over the both items get 120000, which one will see in the resulting grid

see https://jsfiddle.net/fa40onkz/

If you could follow me till the place, then you could understand any from effect, which you could see in the resulting pivot table.

For example, it's wrong to use

{/*x2*/ dataName: "fuelusagecity", hidden: true, skipGrouping: true },
{/*x3*/ dataName: "fuelusagehwy", width: 80, align: "center",
    label: "fuel consumption", skipGrouping: true,
    formatter: function (cellvalue, options, rowObject) {
        return rowObject.x2 === null || rowObject.x3 === null ?
                "-" :
        String(rowObject.x2) + "-" + String(cellvalue);
    }
},
{/*x4*/ dataName: "salesaboveavg", hidden: true, width: 50, align: "center", skipGrouping: true },
{/*x5*/ dataName: "highsalestext", hidden: true, skipGrouping: true }

in option of jqPivot, because two ["toyota", "corolla"] source items have different fuelusagecity, fuelusagehwy and highsalestext values. The salesaboveavg value is the same, but I suppose the usage of salesaboveavg is the same error. You should remove the items from xDimension.

To display empty cell in the cells with 0 value you can define column template

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

and to use it in aggregates:

aggregates: [{
    member: "cellvalue",
    template: myIntTemplate,
    aggregator: "max"
}]

You will get the results like

like on the demo https://jsfiddle.net/tnr2dgkv/

Your last question was about sorting. Sorting of source items is extremely important to create correct pivot table. You can use skipSortByX: true or/and skipSortByY: true options of jqPivot to suppress sorting of initial data, but the resulting data will be sorted once more if you use grouping (groupField: ["x0"]). I think that the simplest way (but it's still not so simple in the implementation) to hold the order of resulting items would be by defining custom sorting functions for xDimension items. I recommend you to read the wiki article for more information.