Summary with Excel formulas

Number of rows can be frozen at the bottom of grid depending upon the number of rows in summaryData option.
In this example, Excel formulas are used in the summaryData to calculate aggregates of the rows.

Summary is refreshed whenever:

Rank
Company
Revenues
Profits
Expenditure = Revenues - Profits
 
 
 
 
 
 
1
Exxon Mobil
$10.00
$1.00
$9.00
2
Wal-Mart Stores
$11.00
$2.00
$9.00
3
Royal Dutch Shell
$12.00
$3.00
$9.00
4
BP
$13.00
$4.00
$9.00
5
General Motors
$14.00
$5.00
$9.00
6
Chevron
$15.00
$6.00
$9.00
7
DaimlerChrysler
$16.00
$7.00
$9.00
8
Toyota Motor
$17.00
$8.00
$9.00
9
Ford Motor
$18.00
$9.00
$9.00
10
ConocoPhillips
$19.00
$10.00
$9.00
11
General Electric
$20.00
$11.00
$9.00
12
Total
$21.00
$12.00
$9.00
13
ING Group
$22.00
$13.00
$9.00
14
Citigroup
$23.00
$14.00
$9.00
15
AXA
$24.00
$15.00
$9.00
16
Allianz
$25.00
$16.00
$9.00
17
Volkswagen
$26.00
$17.00
$9.00
18
Fortis
$27.00
$18.00
$9.00
19
Crédit Agricole
$28.00
$19.00
$9.00
20
American Intl. Group
$29.00
$20.00
$9.00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Total
$390.00
$210.00
$180.00
Average
$19.50
$10.50
$9.00
Std deviation
$5.92
$5.92
$0.00
Loading...

92
 
1
​
2
$(function () {
3
    var json_data = [
4
        {"rank": 1, "company": "Exxon Mobil","revenues": 10, "profits": 1 },
5
        {"rank": 2,"company": "Wal-Mart Stores","revenues": 11,"profits": 2  },
6
        {"rank": 3,"company": "Royal Dutch Shell","revenues": 12,"profits": 3 },
7
        {"rank": 4,"company": "BP","revenues": 13, "profits": 4 },
8
        {"rank": 5, "company": "General Motors", "revenues": 14, "profits": 5},
9
        {"rank": 6, "company": "Chevron", "revenues": 15, "profits": 6},
10
        {"rank": 7, "company": "DaimlerChrysler", "revenues": 16, "profits": 7},
11
        {"rank": 8, "company": "Toyota Motor", "revenues": 17, "profits": 8 },
12
        { "rank": 9, "company": "Ford Motor", "revenues": 18, "profits": 9 },
13
        { "rank": 10, "company": "ConocoPhillips", "revenues": 19, "profits": 10},
14
        { "rank": 11, "company": "General Electric", "revenues": 20, "profits": 11 },
15
        { "rank": 12, "company": "Total", "revenues": 21, "profits": 12 },
16
        { "rank": 13, "company": "ING Group", "revenues": 22, "profits": 13},
17
        { "rank": 14, "company": "Citigroup", "revenues": 23, "profits": 14 },
18
        { "rank": 15, "company": "AXA", "revenues": 24, "profits": 15 },
19
        { "rank": 16, "company": "Allianz", "revenues": 25, "profits": 16 },
20
        { "rank": 17, "company": "Volkswagen", "revenues": 26, "profits": 17 },
21
        { "rank": 18, "company": "Fortis", "revenues": 27, "profits": 18 },
22
        { "rank": 19, "company": "Crédit Agricole","revenues": 28,"profits": 19},
23
        { "rank": 20, "company": "American Intl. Group", "revenues": 29, "profits": 20 }
24
    ];
25
​
26
    var obj = { 
27
        width: "flex",        
28
        showTitle: false,
29
        height: 400,        
30
        flex: {one: true},        
31
        menuIcon: true,
32
        change: function () {
33
            this.refreshSummary();
34
        },
35
        toolbar: {
36
            items: [{
37
                type: 'select',
38
                label: 'Format: ',                
39
                cls: "export_format",
40
                options: [{ xlsx: 'Excel', csv: 'Csv', htm: 'Html', json: 'Json'}]
41
            },
42
            {
43
                type: 'button',
44
                label: "Export",
45
                icon: 'ui-icon-arrowthickstop-1-s',
46
                listener: function () {
47
                    var format = $(".export_format").val(),
48
                        data = this.exportData({
49
                            //url: "/pro/demos/exportData",
50
                            format: format,
51
                            render: true
52
                        });
53
                    pq.saveAs(data, "pqgrid." + format);
54
                }
55
            }]
56
        },
57
        colModel: [
58
            { title: "Rank", dataType: "integer", dataIndx: "rank",  exportRender: false,
59
                render: function(ui){
60
                    if( ui.rowData.summaryRow ){
61
                        return "<b>"+ui.cellData+"</b>";
62
                    }
63
                }
64
            },
65
            { title: "Company", dataIndx: "company" },
66
            { title: "Revenues", dataType: "float", dataIndx: "revenues", format: '$##,###.00' },
67
            { title: "Profits", dataType: "float", dataIndx: "profits", format: '$##,###.00' },
68
            {//formula field.
69
                title: "Expenditure = Revenues - Profits",
70
                dataType: "float", 
71
                dataIndx: 'diff',
72
                format: '$##,###.00',
73
                editable: false
74
            }
75
        ],
76
        summaryData: [
77
            { rank:'Total', summaryRow: true, pq_fn:{revenues:'sum(C:C)', profits:'sum(D:D)', diff:'sum(E:E)' }},
78
            { rank:'Average', summaryRow: true, pq_fn:{revenues:'average(C:C)', profits:'average(D:D)', diff:'average(E:E)' }},            
79
            { rank:'Std deviation', summaryRow: true, pq_fn:{revenues:'stdev(C:C)', profits:'stdev(D:D)', diff:'stdev(E:E)' }}                
80
        ],            
81
        formulas: [
82
            ["diff", function(rd){
83
                return rd.revenues - rd.profits;
84
            }]
85
        ],
86
        dataModel: { data: json_data }
87
    };
88
    
89
    pq.grid("#grid_summary", obj);
90
​
91
});
92
​
ParamQuery Pro Eval