jQWidgets Forums

Tagged: grid sorting filtering aggregates, html5 grid control

This topic contains 3 replies, has 2 voices, and was last updated by Peter Stoev 12 years, 2 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)

Author

Aggregates With Filters & Row Numbers… Posts

April 15, 2013 at 7:28 pm Aggregates With Filters & Row Numbers… #19308

Member

Two quick questions:

1) I’m calling grid.jqxGrid(‘getcolumnaggregateddata’, event.args.column.datafield, [‘min’, ‘max’, ‘sum’, ‘avg’, ‘count’]); to get aggregate data for a column and it works fine initially but when the column is filtered I’m still getting aggregate results for the entire data set. Should I be doing something else to get the new aggregate data when the column is filtered? I need to use the grid API to get the data as we are displaying it in a div external to the grid.

2) I’ve implemented row numbers as suggested in the Excel demo example and it works fine but when the grid is sorted the row numbers sort as well. Is there a way to “lock” the row number column so that it is exempt from any grid sorting and will remain in order (e.g. 1, 2, 3, 4, etc….) regardless of how the grid is sorted?

Thanks,

Craig

April 16, 2013 at 6:19 am Aggregates With Filters & Row Numbers… #19329

Peter Stoev

Keymaster

Hi Craig,

1. Do you use the latest version of jQWidgets?
2. Aggregates are updated as expected after filtering in jQWidgets 2.8.1.

Filter by the Price column in the following example and you will see that aggregates are updated at the bottom of the Grid.

<!DOCTYPE html>
<html lang="en">
<head>
    <title id='Description'>In this sample is demonstrated how to display custom aggregates in jqxGrid.</title>
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
    <script type="text/javascript" src="../../scripts/jquery-1.8.3.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.filter.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/jqxgrid.selection.js"></script> 
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.aggregates.js"></script> 
    <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script>
    <script type="text/javascript" src="../../scripts/gettheme.js"></script>
    <script type="text/javascript" src="generatedata.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var theme = getDemoTheme();
            // prepare the data
            var data = generatedata(200);
            var source =
            {
                localdata: data,
                datatype: "array",
                datafields:
                [
                    { name: 'firstname', type: 'string' },
                    { name: 'lastname', type: 'string' },
                    { name: 'productname', type: 'string' },
                    { name: 'quantity', type: 'number' },
                    { name: 'price', type: 'number' }
                ],
                updaterow: function (rowid, rowdata, commit) {
                    // synchronize with the server - send update command   
                    commit(true);
                }
            };
            var dataAdapter = new $.jqx.dataAdapter(source);
            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                width: 670,
                source: dataAdapter,
                theme: theme,
                showstatusbar: true,
                statusbarheight: 25,
                altrows: true,
                showaggregates: true,
                filterable: true,
                columns: [
                  { text: 'First Name', columntype: 'textbox', datafield: 'firstname', width: 90 },
                  { text: 'Last Name', datafield: 'lastname', columntype: 'textbox', width: 90 },
                  { text: 'Product', datafield: 'productname', width: 170 },
                  { text: 'Quantity', datafield: 'quantity', width: 100, cellsalign: 'right', cellsformat: 'n2' },
                  { text: 'Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2', aggregates: [{ '<b>Total</b>':
                            function (aggregatedValue, currentValue, column, record) {
                                var total = currentValue * parseInt(record['quantity']);
                                return aggregatedValue + total;
                            }
                      }]                  
                  }
                ]
            });
        });
    </script>
</head>
<body class='default'>
    <div id='jqxWidget'>
        <div id="jqxgrid"></div>
    </div>
</body>
</html>

3. I am unable to reproduce that with jQWidgets 2.8.1. The rows numbers are not changed if you perform sorting on the Grid.

April 22, 2013 at 7:37 pm Aggregates With Filters & Row Numbers… #19775

craigkoster

Member

Peter,

I pulled down the latest version of jqWidgets and am still not getting the grid to work correctly. In your reply you mentioned the aggregates at the bottom of the grid – I am not using those – I am using the grid API to get aggregates for a column and then displaying these aggregates elsewhere on the page. It looks like the ‘getcolumnaggregateddata’ method does not take into consideration when the grid is filtered when it returns aggregate data for a column.

Please send me an example where row numbers are *NOT* sorted when other columns are sorted – I cannot find an example on the site where this is working as you describe.

Thanks very much for your help.

April 23, 2013 at 3:46 am Aggregates With Filters & Row Numbers… #19778

Peter Stoev

Keymaster

Hi,

1. The “getcolumnaggregateddata” method from the Grid API uses the data source records by default. As a 4th param, you may pass the result of the Grid’s “getrows” method which returns an array with the rows currently displayed in the Grid.

2. Here’s a working sample:

<!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-2.0.0.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/jqxcheckbox.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/jqxgrid.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.sort.js"></script> 
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.pager.js"></script> 
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script> 
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.edit.js"></script> 
    <script type="text/javascript">
        $(document).ready(function () {
            var theme = '';
            var url = "../sampledata/products.xml";
            // prepare the data
            var source =
            {
                datatype: "xml",
                datafields: [
                    { name: 'ProductName', type: 'string' },
                    { name: 'QuantityPerUnit', type: 'int' },
                    { name: 'UnitPrice', type: 'float' },
                    { name: 'UnitsInStock', type: 'float' },
                    { name: 'Discontinued', type: 'bool' }
                ],
                root: "Products",
                record: "Product",
                id: 'ProductID',
                url: url
            };
            var cellsrenderer = function (row, columnfield, value, defaulthtml, columnproperties) {
                if (value < 20) {
                    return '<span style="margin: 4px; float: ' + columnproperties.cellsalign + '; color: #ff0000;">' + value + '</span>';
                }
                else {
                    return '<span style="margin: 4px; float: ' + columnproperties.cellsalign + '; color: #008000;">' + value + '</span>';
                }
            }
            var dataAdapter = new $.jqx.dataAdapter(source, {
                downloadComplete: function (data, status, xhr) { },
                loadComplete: function (data) { },
                loadError: function (xhr, status, error) { }
            });
            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                width: 670,
                source: dataAdapter,
                theme: theme,
                pageable: true,
                autoheight: true,
                sortable: true,
                altrows: true,
                enabletooltips: true,
                editable: true,
                selectionmode: 'multiplecellsadvanced',
                columns: [
                  { text: 'Product Name', columntype: 'number', sortable: false, cellsrenderer: function (row, column, value) {
                      return '<div style="text-align: center; margin-top: 5px;">' + (1 + value) + '</div>';
                  }, width: 250 },
                  { text: 'Quantity per Unit', datafield: 'QuantityPerUnit', cellsalign: 'right', align: 'right', width: 120 },
                  { text: 'Unit Price', datafield: 'UnitPrice', align: 'right', cellsalign: 'right', cellsformat: 'c2', width: 100 },
                  { text: 'Units In Stock', datafield: 'UnitsInStock', cellsalign: 'right', cellsrenderer: cellsrenderer, width: 100 },
                  { text: 'Discontinued', columntype: 'checkbox', datafield: 'Discontinued' },
                ]
            });
        });
    </script>
</head>
<body class='default'>
    <div id='jqxWidget' style="font-size: 13px; font-family: Verdana; float: left;">
        <div id="jqxgrid">
        </div>
     </div>
</body>
</html>

Best Regards,
Peter Stoev

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

Author

Posts

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.