Grand total and Group total not showing actual totals for local paging

[TeeJT]

I am using your demo at https://paramquery.com/pro/demos/group_rows

I added a local paging model there:

Code: [Select]

pageModel: { type: "local", rPP: 5, rPPOptions: [5, 10, 20, 100], strRpp: "{0}", strDisplay: "{0} to {1} of {2}" }, 

In local paging model, all the records are already available to the grid - only the display is showing 5 records per page. However my customers want to display the Grouped total e.g. for Argentina to be $598.58
The first page shows only the 5 records and the Group total shown is $306.64.
To my customer - this is not the total for Argentina. It should be paged but the totals should reflect the complete total for Argentina  - $598.58

Also the grand total when paged is showing the total for that page ($306.64) but not the complete total - $64,942.69

So could there be a boolean flag in PageModel which allows the Group total and Grand total to show the actual Group total and Grand total when in local paging mode.

In the grid I have over a million records and it is too sluggish to show without paging but the customer wants to see actual Group totals and Grand total not the paged Group total and Grand total.

Is this possible?

To switch between the paging and no paging - I comment the PageMode or uncomment the PageModel


So that the whole code becomes:

Code: [Select]


    $(function () {
        function fillOptions(grid) {
            var column = grid.getColumn({ dataIndx: 'ShipCountry' });
            column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
            column.filter.cache = null;
            grid.refreshHeader();
        }
        var colM = [
            { title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
                filter: {
                    type: 'select',
                    prepend: { '': 'All Countries' },
                    valueIndx: 'ShipCountry',
                    labelIndx: 'ShipCountry',
                    condition: 'equal',
                    listeners: ['change']
                }
            },
            { title: "Customer Name", width: 130, dataIndx: "ContactName" },
            { title: "Freight", width: 120, format: '$##,###.00',
                summary: {
                    type: "sum"
                },
                dataType: "float", dataIndx: "Freight"
            },
            { title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
    { title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
            { title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
            { title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
        var dataModel = {
            location: "remote",
            dataType: "JSON",
            method: "GET",
            url: "/Content/orders.json"
            //url: "/pro/orders/get",//for ASP.NET
            //url: "orders.php",//for PHP
        };
        var groupModel = {
            on: true,
            summaryInTitleRow: 'all', //to display summary in the title row.
            dataIndx: ['ShipCountry', 'ContactName'],
            showSummary: [true], //to display summary at end of every group.           
            grandSummary: true,
            title: [
                "{0} ({1})",
                "{0} - {1}"
            ]
        };
        var obj = {
            height: 500,
            toolbar: {
                items: [{
                    type: 'button',
                    label: "Toggle grouping",
                    listener: function (evt) {
                        this.groupOption({
                            on: !grid.option('groupModel.on')
                        });
                    }
                }]
            },
            dataModel: dataModel,
            scrollModel: { autoFit: true },
            colModel: colM,
pageModel: { type: "local", rPP: 5, rPPOptions: [5, 10, 20, 100], strRpp: "{0}", strDisplay: "{0} to {1} of {2}" },   
            numberCell: { show: false },
            filterModel: { on: true, header: true, type: "local" },
            selectionModel: { type: 'cell' },
            groupModel: groupModel,
            load: function (evt, ui) {
                //options for ShipCountry filter.   
                fillOptions(grid);
            },
            showTitle: false,
            resizable: true,
            virtualX: true,
            virtualY: true,
            hwrap: false,
            wrap: false
        };
        var grid = pq.grid("#grid_group_rows", obj);

    });


[paramvir]

Sorry there is no such setting currently to display grouped totals and grand totals based on entire data instead of paged data.

Assume if we try to do that, that would mean applying grouping to whole data and then divide the grouped data among pages, but then  there is no performance gain by using paging because most of the time is spent in grouping the data.

[TeeJT]

The grid becomes very lagging when it comes to a few hundred thousand records and I am forced to use paging but the customer wants to see actual Group Totals and Grand Totals and not the paged totals.

[TeeJT]

The customer says all the data is there - why can't the group total and grand total be shown as if these were pages in a report? When you browse through a pdf report with many pages - the group total and grand total are actual totals and group totals do not appear on every page but at the start of the group rows.

[paramvir]

I can't comment on pdf as my knowledge about how pdf works is limited and I'm not sure whether pdf groups the rows on its own the way grid does.

The reason group rows and grand totals are not applied to whole dataset in grid is that grouping is done after whole dataset is divided into pages. And grouping done on the current paged data has no knowledge of data on other pages. It's the intentional design after analyzing all pros and cons and there is no immediate plan to change it.

If you want grouping applied to whole dataset rather than on current page, I suggest you not to use paging.

You have mentioned that you experience lagging when grouping is used with few hundred thousand rows, but you haven't mentioned the details ( like on what kind of user action ) do you experience the lagging. I would be glad to look into it to see if there is any possible optimization to speed it up. If you have a url/ test case to share, that would be very helpful.

[TeeJT]

I created on my personal website this test with about 23000 records and the whole grid is so sluggish - http://teejt.drivehq.com/testing/Visual/ParamQuery/Samples/Sample.html

I also have the same grid but with a smaller sample at
http://teejt.drivehq.com/testing/Visual/ParamQuery/Samples/SampleSmall.html

I have a customized aggregate function to show both sum and average - it works in the small sample but not in the large sample.

[paramvir]

Looks like you are not using virtual mode. I'm surprised the browser didn't crash.

Please use virtualX: true, virtualY: true

[TeeJT]

Thank you  very much. Now it's working well.