Group Aggregates not displayed

[berukenParticipant]

I can’t get the group aggregates to show despite all attempts. My data looks fine, aggregates and the grouping works fine too. I’ve reviewed my settings with the demo http://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/index.htm#demos/jqxgrid/grid-grouping-aggregates.htm and nothing works. Could it just be my data?

                var data = {
                    localdata: table,
                    datatype: "array",
                    datafields:
                    [
                        { name: "DOC_TYPE", type: "string" },
                        { name: "DOC_ID", type: "string" },
                        { name: "ACCT_ID", type: "string" },
                        { name: "COMMIT", type: "number" },
                        { name: "UDOS", type: "number" },
                        { name: "OBLIG", type: "number" },
                        { name: "EXPEND", type: "number" },
                        { name: "TotalExp", type: "number" },
                        { name: "LEDGER_TRANS_NO", type: "number" },
                        { name: "TRANS_EFF_DT", type: "date" }
                    ]
                };

                var dataAdapter = new $.jqx.dataAdapter(data);

                $("#jqxgrid").jqxGrid(
                {
                    width: 1300,
                    theme: 'energyblue',
                    source: dataAdapter,
                    autoheight: true,
                    altrows: true,
                    groupable: true,
                    showgroupaggregates: true,
                	showstatusbar: true,
                	showaggregates: true,
                	statusbarheight: 25,
                	groups: ['DOC_TYPE'],
                    columns: [
                      { text: 'DOC_TYPE', dataField: 'DOC_TYPE', cellsalign: 'center'},
                      { text: 'DOC_ID', dataField: 'DOC_ID', cellsalign: 'center', width: 140},
                      { text: 'ACCT_ID', dataField: 'ACCT_ID', cellsalign: 'center', width: 190},
                      { text: 'COMMIT', dataField: 'COMMIT', cellsalign: 'right',width: 100, cellsformat: 'c2' },
                      { text: 'UDOS', dataField: 'UDOS', cellsalign: 'right', cellsformat: 'c2',width: 100 },
                      { text: 'OBLIG', dataField: 'OBLIG', cellsalign: 'right', cellsformat: 'c2', width: 100  },
                      { text: 'EXPEND', dataField: 'EXPEND', cellsalign: 'right', cellsformat: 'c2', width: 100  },
                      { text: 'Total Expended', dataField: 'TotalExp', cellsalign: 'right', cellsformat: 'c2', aggregates: ['sum']},
                      { text: 'TRANS NO', dataField: 'LEDGER_TRANS_NO', cellsalign: 'right' },
                      { text: 'TRANS_EFF_DT', dataField: 'TRANS_EFF_DT', cellsalign: 'right' }
                    ]
                });

[berukenParticipant]

I found the issue being the pre-group setting… groups: [‘DOC_TYPE’] and removed it. Then manually applied the group by dragging the column up to the grouping header. However the value is the total rows not the sub set of grouped data. It does not recalculate.

[HristoParticipant]

Hello beruken,

Each one group has an own calculation. (when the group was expanded)
I assume that this case has been resolved?

Best Regards,
Hristo Hristov

jQWidgets team
http://www.jqwidgets.com

[berukenParticipant]

Hristo, No this has never been resolved and I don’t understand why the grouping aggregates don’t behave as your demo. There is nothing out of the ordinary and it is set up like your example. When the aggregates do show up they are not by the currently expanded group. They may include numbers from the other groups and the group aggregate row does not display in the proper position. I don’t have an internet site to submit a picture but the rows look like this.
group
data
data
group
data
aggregate (includes sums from both groups)

only when I filter out some of the groups do the numbers display accurately.

[berukenParticipant]

Actually, a better way to state this is it only shows the aggregates for the first group. However, when I filter the numbers update accordingly.

[Peter StoevKeymaster]

You can check whether you use the latest version, too. The grouping with aggregates behavior has changed since this topic is posted.

[berukenParticipant]

I tried the latest and notice the behavior if I have two groups I only get the first groups aggregates. Adding a third group gives me the second groups aggregates but when expanding the individual items in the third group it still does not display totals for those records. Excel filter no longer works either. I see no specific errors in debugger with the exception of unreachable code after return statement as I am using jqx-all.

Latest version Firefox and IE 11

[Peter StoevKeymaster]

Hi beruken,

Unfortunately, We do not see anything wrong with the group aggregates sample we have and the way this feature works. The Excel filter works, too: https://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/excelfiltering.htm?light

Best Regards,
Peter Stoev

jQWidgets Team
https://www.jqwidgets.com/

[berukenParticipant]

I too have no issue with your sample but cannot understand why my data does not aggregate properly. The last group defined, when expanded does not show the Summed data. I stripped it down into a single html file and are providing it for your test. I would be extremely surprised if you can tell me it works as intended. Group by FY and Doc_Type and it only aggregates on FY. Add Acct ID, Doc_Type aggregates but not Acct_ID.

<!DOCTYPE html >

<html >

<head >
<meta http-equiv=”X-UA-Compatible” content=”IE=edge” />
<meta http-equiv=”Content-Type” content=”text/html; charset=utf-8″ />
<title>Untitled 2</title>
<!– the following SP style reference is required. Do not remove –>
<link rel=”stylesheet” href=”/SiteAssets/jqwidgets/jqwidgets/styles/jqx.base.css” type=”text/css” />
<link rel=”stylesheet” href=”/SiteAssets/jqwidgets/jqwidgets/styles/jqx.metrodark.css” type=”text/css” />
<link rel=”stylesheet” href=”/SiteAssets/jqwidgets/jqwidgets/styles/jqx.darkblue.css” type=”text/css” />

<script type=”text/javascript” src=”/SiteAssets/jquery-1.12.4.min.js”></script>

<script type=”text/javascript” src=”/SiteAssets/jqwidgets/jqwidgets/jqx-all.js”></script>

<script type=”text/javascript”>
$(document).ready( function () {
var rows = $(“#GridView1 tbody tr”);

// select columns.

var columns = $(“#GridView1 thead th”);

var data = [];

for (var i = 0; i < rows.length; i++) {

var row = rows[i];

var datarow = {};

for (var j = 0; j < columns.length; j++) {

// get column’s title.

var columnName = $.trim($(columns[j]).text());

// select cell.

var cell = $(row).find(‘td:eq(‘ + j + ‘)’);

datarow[columnName] = $.trim(cell.text());

}

data[data.length] = datarow;

}
var source = {
localdata: data,
datatype: “array”,
datafields:
[ { name: “FY”, type: “string” },
{ name: “DOC_TYPE”, type: “string” },
{ name: “ACCT_ID”, type: “string” },
{ name: “COMMIT”, type: “number” },
{ name: “UDOS”, type: “number” },
{ name: “OBLIG”, type: “number” },
{ name: “EXPEND”, type: “number” } ]
};

var dataAdapter = new $.jqx.dataAdapter(source);

var grid = $(“#jqxgrid”).jqxGrid({
width: ‘99%’,
source: dataAdapter,
theme: ‘metrodark’,
autoheight: true,
filterable: true,
filterMode: ‘excel’,
groupable: true,
groupsexpandedbydefault: false,
showgroupaggregates: true,
columns: [
{ text: ‘FY’, dataField: ‘FY’, width:75 },
{ text: ‘Doc Type’, dataField: ‘DOC_TYPE’, cellsalign: ‘center’, width: 75},
{ text: ‘Acct ID’, dataField: ‘ACCT_ID’, cellsalign: ‘center’, width: 190},
{ text: ‘Commit’, dataField: ‘COMMIT’, cellsalign: ‘right’,width: 100, cellsformat: ‘c2’, aggregates: [‘sum’]},
{ text: ‘Udos’, dataField: ‘UDOS’, cellsalign: ‘right’, cellsformat: ‘c2’,width: 125, aggregates: [‘sum’]},
{ text: ‘Oblig’, dataField: ‘OBLIG’, cellsalign: ‘right’, cellsformat: ‘c2’, width: 100, aggregates: [‘sum’]},
{ text: ‘Expended’, dataField: ‘EXPEND’, cellsalign: ‘right’, cellsformat: ‘c2’, width: 125, aggregates: [‘sum’]}
]
});

});

</script>

</head>

<body>

<div id=”jqxgrid”></div>

<table id=”GridView1″ >
<thead>
<tr>
<th scope=”col”>FY</th>
<th scope=”col”>DOC_TYPE</th>
<th scope=”col”>ACCT_ID</th>
<th scope=”col”>COMMIT</th>
<th scope=”col”>UDOS</th>
<th scope=”col”>OBLIG</th>
<th scope=”col”>EXPEND</th>
</tr>
</thead>
<tbody>
<tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>1279.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>1279.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>-1279.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>996.5600</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>218.2300</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-1279.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>1278.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>-148.6200</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>-1129.3800</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>265.7700</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>1129.3800</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>2001.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>-452.1500</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>-1548.8500</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>191.3100</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>1548.8500</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>1756.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>1228.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-63.5900</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>416.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-1692.4100</td><td>0.0000</td><td>1692.4100</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>1692.4100</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>-1692.4100</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-1228.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>2837.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>PR-Commitment</td><td>1234 1810009904E1</td><td>200000.0000</td><td>0.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>PR-Commitment</td><td>1234 1810009904E1</td><td>425000.0000</td><td>0.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>2965.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>MIPR</td><td>1234 1810009904E1</td><td>0.0000</td><td>425000.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>PR-Commitment</td><td>1234 1810009904E1</td><td>-425000.0000</td><td>0.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-1868.4800</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-968.5200</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>579.3100</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>1868.4800</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>1868.4800</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>-1868.4800</td>
</tr><tr>
<td>2016</td><td>MIPR</td><td>1234 1810009904E1</td><td>0.0000</td><td>200000.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>PR-Commitment</td><td>1234 1810009904E1</td><td>-200000.0000</td><td>0.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>1046.8200</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-1046.8200</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>MIPR</td><td>1234 1810009904E1</td><td>0.0000</td><td>-136624.8500</td><td>0.0000</td><td>136624.8500</td>
</tr><tr>
<td>2016</td><td>PR-Commitment</td><td>1234 1810009904E1</td><td>-200000.0000</td><td>0.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>2965.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904</td><td>0.0000</td><td>-2965.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>874.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>982.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>PR-Commitment</td><td>1234 1810009904E1</td><td>200000.0000</td><td>0.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>3030.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-982.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>MIPR</td><td>1234 1810009904E1</td><td>0.0000</td><td>-57856.7600</td><td>0.0000</td><td>57856.7600</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-874.0000</td><td>0.0000</td><td>874.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-1530.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>500.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-2965.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>5678 1810009904</td><td>0.0000</td><td>1500.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2017</td><td>Travel</td><td>5678 1810009904</td><td>0.0000</td><td>1000.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>MIPR</td><td>1234 1810009904E1</td><td>0.0000</td><td>-5518.3900</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>MIPR</td><td>1234 1810009904E1</td><td>0.0000</td><td>0.0000</td><td>0.0000</td><td>5518.3900</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>-2000.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2017</td><td>Travel</td><td>5678 1810009904</td><td>0.0000</td><td>-1000.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>1234 1810009904E1</td><td>0.0000</td><td>735.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>Travel</td><td>2Z24D 1810009904</td><td>0.0000</td><td>598.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2017</td><td>Travel</td><td>2Z24D 1810009904</td><td>0.0000</td><td>2981.0000</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>MIPR</td><td>1234 1810009904E1</td><td>0.0000</td><td>-112500.0000</td><td>0.0000</td><td>112500.0000</td>
</tr><tr>
<td>2016</td><td>FedMilStrip-IntraGovtTransfer</td><td>1234 1810009904E1</td><td>0.0000</td><td>15602.2600</td><td>0.0000</td><td>0.0000</td>
</tr><tr>
<td>2016</td><td>FedMilStrip-IntraGovtTransfer</td><td>1234 1810009904E1</td><td>0.0000</td><td>158026.3400</td><td>0.0000</td><td>0.0000</td>
</tr></tbody>
</table>

</body>

</html>

[berukenParticipant]

I just noticed it does work as intended when there is only one group item in the second to last group as your samples demonstrate. If more than one group item in the second to last group it fails to aggregate on any last group items when expanded.

[Author]

Posts