jQWidgets Forums
jQuery UI Widgets › Forums › Grid › groupsRenderer show sum in Groups and subgroups
Tagged: aggregates, angular grid, grid, group, grouping, groups, groupsrenderer, jquery grid, jqxgrid, subgroup, subgroups, sum
This topic contains 8 replies, has 2 voices, and was last updated by Francisco 9 years, 6 months ago.
-
Author
-
Good afternoon people,
After several tests I understood that I needed really, so here’s my question:
In a Grid I have the following columns: [Customer, Customer_Unit, Supplier, Supplier_Unity, Item, Day, Value]
I know it’s possible, but do not know as yet whether to show which group I create, the total amount to the side of, for example:groupable: true,
groups: [‘nunidade’],
groupsRenderer: function (value, RowData, level){
var groupsTotal = 0;
var groupsQtde = 0;
var rows = $ (“# t_nf”) jqxDataTable (‘getRows.’);
for (var i = 0; i <rows.length; i ++) {
if (rows [i] .nunidade == value) {
groupsQtde = groupsQtde + = 1;
groupsTotal = groupsTotal + parseFloat (rows [i] .Total);
}
}
return “Client:” + value + “- Qt:” + groupsQtde + “- Total: R$” + groupsTotal;
}With this example I can show the total per unit, but only by unity, I would like to display the total for all circumstances, for example:
I drag the Customer and Customer_Unit column and with this I have a preview of the total per Customer_unit, or if I drag supplier and customer, thus I have a preview of the total provided for each customer and the total for supplier
I do not know if I explained well, but in a general summary, I would like to put together a groupsrenderer ever return me the total in front of independete group how many subgroups are mounted on the grid.
Hello Francisco,
Please take a look at the following example (based on the demo Groups Renderer). No matter what grouping is applied, the Total price is always shown, even for subgroups.
<!DOCTYPE html> <html lang="en"> <head> <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" /> <script type="text/javascript" src="../../scripts/jquery-1.11.1.min.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.edit.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.grouping.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxgrid.aggregates.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxlistbox.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxdropdownlist.js"></script> <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script> <script type="text/javascript" src="../../scripts/demos.js"></script> <script type="text/javascript" src="generatedata.js"></script> <script type="text/javascript"> $(document).ready(function () { // prepare the data var data = generatedata(200); var source = { localdata: data, datafields: [ { name: 'firstname', type: 'string' }, { name: 'lastname', type: 'string' }, { name: 'productname', type: 'string' }, { name: 'date', type: 'date' }, { name: 'quantity', type: 'number' }, { name: 'price', type: 'number' } ], datatype: "array", updaterow: function (rowid, rowdata) { // synchronize with the server - send update command } }; var dataAdapter = new $.jqx.dataAdapter(source); var toThemeProperty = function (className) { return className + " " + className + "-" + theme; } var groupsrenderer = function (text, group, expanded, data) { var number = dataAdapter.formatNumber(group, data.groupcolumn.cellsformat); var text = data.groupcolumn.text + ': ' + number; var aggregate = this.getcolumnaggregateddata('price', ['sum'], true, data.subItems); return '<div class="' + toThemeProperty('jqx-grid-groups-row') + '" style="position: absolute;"><span>' + text + ', </span>' + '<span class="' + toThemeProperty('jqx-grid-groups-row-details') + '">' + "Total price" + ' (' + aggregate.sum + ')' + '</span></div>'; } // initialize jqxGrid $("#jqxgrid").jqxGrid( { width: 850, source: dataAdapter, groupable: true, groupsrenderer: groupsrenderer, selectionmode: 'singlecell', groups: ['price'], columns: [ { text: 'First Name', groupable: true, datafield: 'firstname', width: 90 }, { text: 'Last Name', groupable: true, datafield: 'lastname', width: 90 }, { text: 'Product', groupable: false, columntype: 'dropdownlist', datafield: 'productname', width: 180 }, { text: 'Ship Date', groupable: false, datafield: 'date', width: 90, cellsalign: 'right' }, { text: 'Quantity', datafield: 'quantity', width: 70, cellsalign: 'right' }, { text: 'Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2' } ] }); }); </script> </head> <body class='default'> <div id='jqxWidget'> <div id="jqxgrid"> </div> </div> </body> </html>Best Regards,
DimitarjQWidgets team
http://www.jqwidgets.com/Good day Dimitar!
So it’s almost that, but as I said yesterday, I need it to return me the total for each group and subgroup, and this example you gave me only returns the total for the subgroup, for instance, if I group by customer it I retonar the total per user (OK), but if I group by customer and customer units, it me the total for customer units and customer show the text undefined, you know?
Another example of grouping, Customer, Customer Units and Supplier, was for him to return as each supplier produced for each customer unit (OK!) but also for the customer (the sum of all units) and it returns undefined!
I sought some algorithm to identify subgroups or groups and make the sum by grouping layers, but I’m not so familiar, can you help me or show me an example?| Costumer x |—–| Customer Unit x|
___________________________________________________________________________________________________________________________
| Customer | Customer Unit | Supplier | Supplier Unit | Day | Total |
—————————————————————————————————————————-
| > | Custumer A Total $50000 |
—————————————————————————————————————————-
| | > Unit A1 Total $ 25000 |
—————————————————————————————————————————-
| | > Unit A2 Total $ 10000 |
—————————————————————————————————————————-
| | > Unit A3 Total $ 15000 |
—————————————————————————————————————————-
| > | Custumer B Total $234000 |
—————————————————————————————————————————-
| | > Unit B1 Total $ 85000 |and so on!!!
Currently it shows:
___________________________________________________________________________________________________________________________
| > | Custumer B Total (undefined) <- the problem its here! |
—————————————————————————————————————————-
| | > Unit B1 Total $ 85000 |
—————————————————————————————————————————-Thank you for your patience and didactic me!
Hello Francisco,
There is no such built-in algorithm, the groupsrenderer function is entirely customizable. As you can see in our example, there is no “undefined” issue, so you may have to alter the function for your case. However, we cannot tell how to do so, because we do not have access to your grid’s code or data.
Best Regards,
DimitarjQWidgets team
http://www.jqwidgets.com/Well, so in this case I will show my code, but I believe this will not make a difference, because what I ALMECO and group with two, three or four layers layers and mater the total sum in each layer, but come on, see if that helps.
var source = {
dataType: “json”,
datafields:
[
{name: ‘actions’, type: ‘string’},
{name: ‘fornecedor’, type: ‘string’},
{name: ‘posto’, type: ‘string’},
{name: ‘pcnpj’, type: ‘string’},
{name: ‘cliente’, type: ‘string’},
{name: ‘unidade’, type: ‘string’},
{name: ‘ucnpj’, type: ‘string’},
{name: ‘numero’, type: ‘string’},
{name: ’emissao’, type: ‘date’},
{name: ‘total’, type: ‘string’},
{name: ‘chave’, type: ‘string’},
{name: ‘path’, type: ‘string’},
{name: ‘import’, type: ‘date’}
],
localdata: result
};var dataAdapter = new $.jqx.dataAdapter(source);
var toThemeProperty = function (className) {
return className + ” ” + className + “-” + theme;
};var groupsrenderer = function (text, group, expanded, data) {
var number = dataAdapter.formatNumber(group, data.groupcolumn.cellsformat);
var text = data.groupcolumn.text + ‘: ‘ + number;
var aggregate = this.getcolumnaggregateddata(‘total’, [‘sum’], true, data.subItems);
return ‘<div class=”‘ + toThemeProperty(‘jqx-grid-groups-row’) + ‘” style=”position: absolute;”><span>’ + text + ‘, </span>’ + ‘<span class=”‘ + toThemeProperty(‘jqx-grid-groups-row-details’) + ‘”>’ + “Total” + ‘ (‘ + aggregate.sum + ‘)’ + ‘</span></div>’;
};$(“#tfechamento”).jqxGrid({
width: ‘100%’,
sortable: true,
filterable: true,
autoshowfiltericon: true,
groupable: true,
autorowheight: true,
autoheight: true,
altrows: true,
localization: getLocalization(),
columnsresize: true,
showstatusbar: true,
statusbarheight: 90,
showaggregates: true,
groupsrenderer: groupsrenderer,
source: dataAdapter,
columns: [
{text: ‘Ações’, dataField: ‘actions’, width: ’80px’, cellsAlign: ‘right’},
{text: ‘Fornecedor’, dataField: ‘fornecedor’, width: ‘100px’},
{text: ‘Posto’, dataField: ‘posto’},
{text: ‘Cliente’, dataField: ‘cliente’},
{text: ‘Unidade’, dataField: ‘unidade’, width: ‘130px’},
{text: ‘Número’, dataField: ‘numero’, cellsAlign: ‘right’, align: ‘left’, width: ’70px’, aggregates: [‘count’],
aggregatesrenderer: function (aggregates, column, element) {
var renderstring = “<div class=’jqx-widget-content’ style=’float: left; background-color: #e5e5e5; width: 100%; height: 100%; ‘>”;
$.each(aggregates, function (key, value) {
var name = ‘Qt’;
renderstring += ‘<div style=”position: relative; margin: 6px; text-align: left; overflow: hidden;”>’ + name + ‘: <b>’ + value + ‘</b></div>’;
});
renderstring += “</div>”;
return renderstring;
}},
{text: ‘Emissao’, dataField: ’emissao’, cellsAlign: ‘right’, align: ‘left’, cellsFormat: ‘d’, width: ‘100px’},
{text: ‘Total’, dataField: ‘total’, cellsAlign: ‘right’, align: ‘left’, cellsFormat: ‘d2’, aggregates: [‘sum’, ‘max’, ‘avg’, ‘min’],
aggregatesrenderer: function (aggregates, column, element) {
var renderstring = “<div class=’jqx-widget-content’ style=’float: left; background-color: #e5e5e5; width: 100%; height: 100%; ‘>”;
$.each(aggregates, function (key, value) {
switch (key) {
case ‘sum’:
name = ‘Soma’;
color = ‘#000000’;
break;
case ‘avg’:
name = ‘Média’;
color = ‘#666666’;
break;
case ‘min’:
name = ‘Menor’;
color = ‘#777777’;
break;
case ‘max’:
name = ‘Maior’;
color = ‘#555555’;
break;
}
renderstring += ‘<div style=”color: ‘ + color + ‘; position: relative; margin: 6px; text-align: right; overflow: hidden;”>’ + name + ‘: <b>’ + value + ‘</b></div>’;
});
renderstring += “</div>”;
return renderstring;
}}
]
});Okay, that’s it, now imagine that I want to group the following columns: 1º customer 2º unit 3º supplier
but I want to bring up the total for all the customer’s total, total units of this client and the total supplier will be able to appear on each client drives, you know?Hi Francisco,
Please also share some data (the value of the result variable) so that we can actually test your example on our side. Moreover, please specify which of your columns are “customer”, “unit” and “supplier” (they are not in English). Finally, as I said earlier, you should modify our sample groupsrenderer function to suit your requirements (it is not applicable to all cases).
Best Regards,
DimitarjQWidgets team
http://www.jqwidgets.com/Good afternoon!
After much thought I could prepare an example in jsFiddle, and is almost the same as mine. The difficulty lies in showing the sum of the Total column for each level of grouping, for example, if I group them with Cliente, or Unidade or Fornecedor, works perfectly, but if I group with 2 levels, Cliente and Unidade, the sum only It appears for Unidade and not for Cliente.
Here is the link!
https://jsfiddle.net/condeso/k1sasd5b/3/What I intend to do and group by “Cliente”, “Unidade” and “Fornecedor”, ie 3 grouping levels, how can I implement this in the function?
Hello Francisco,
Here is how to fix the “undefined” issue: https://jsfiddle.net/Dimitar_jQWidgets/khy6hkuy/.
Best Regards,
DimitarjQWidgets team
http://www.jqwidgets.com/I understand now, thank you, you saved me!
-
AuthorPosts
You must be logged in to reply to this topic.