KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Need sorting with amount in datagrid row group #2317

Open Sawan Ruparel opened this topic on on Sep 2 - 17 comments

Sawan Ruparel commented on Sep 2

Hello !!

I am using datagrid with columnGroup functionality. I need sorting on amount but it is taking sorting of the column which is grouped by. I used options to sort the amount column. But, that didn't work.

"options" => [
                  "order"=>array(
                    array(10,"desc"), 
                ),
            ],

I started counting from 0 and 10 the number is my amount column.

Can you pls help me ?

Sawan Ruparel commented on Sep 6

I dont see any response from 4 days. I need to know about above on urgent basis. Hope you understand. Otherwise need to take refund.

Sebastian Morales commented on Sep 6

We had public holidays last week so some answers must wait. This issue of yours is not a simple one to solve. It will take a few days to find a solution. If you want to refund pls send an email to sales@koolreport.com.

Sawan Ruparel commented on Sep 8

Refund will not solve my query. I will appreciate if you can solve the query ASAP.

Sebastian Morales commented on Sep 9

Pls try this:

<script>
    var groupAmount = {};
</script>
<?php
DataTables::create(array(
    "name" => "myTable1",
    "dataStore" => $this->dataStore('sales'),
    "columns" => [
        "customerName", "productLine", "productName", 
        "dollar_sales" => [
            "type" => "num-fmt",
            "render" => "function(data, type, row, meta) {
                var customerGroupName = row[0]; // because group field 'customerName' index is 0
                var customerGroupAmount = groupAmount[customerGroupName];
                return (type !== 'sort' || customerGroupAmount === undefined) ? 
                    $.fn.dataTable.render.number( '.', ',', 0, '' ).display(data) : customerGroupAmount;
            }"
        ],
    ],
    "clientRowGroup" => [
        "customerName" => [
            'direction' => 'asc',
            'calculate' => [
                'totalSales' => [
                    "sum",  // sum, count, avg, min, max 
                    "dollar_sales",
                    "format" => "function(agg) {
                        groupAmount[group] = agg; // save group amount of this customer group
                        return agg;
                    }",
                ],
            ],
            "top" => "<td colspan='999'>{expandCollapseIcon} Top: Customer: {customerName} | Total: {totalSales}</td>",
        ],
    ],
    "options" => [
        "paging" => false,
    ],
    "onReady" => "function() {
        myTable1
        .order( [ 3, 'desc' ] ) // sort 'dollar_sales' field, index of which is 3
        .draw();
    }",
));
Sawan Ruparel commented on Sep 9

Thank you !! Above fixed issue.

Sawan Ruparel commented on Sep 10

It didn't fix. I checked with adding more data.

I have below columns

'name','pname','status','prname','pn','rf','tx','txn','nd','dt','amount','hp'

so I added in column like this

"amount" => [
                    "label" => 'Amount',
                    'formatValue'=>function($value, $row, $cKey)
                    {
                        return '$'.$row['amount'];
                    },
                    "type" => "num-fmt",
                    "render" => "function(data, type, row, meta) {
                        var customerGroupName = row[0]; // because group field 'customerName' index is 0                       
                        var customerGroupAmount = groupAmount[customerGroupName];
                        return (type !== 'sort' || customerGroupAmount === undefined) ? 
                            $.fn.dataTable.render.number( '.', ',', 0, '' ).display(data) : customerGroupAmount;
                    }"
                ],

In client row group below code

'totalAmount' => [
                                'sum', //'sum', 'count', 'avg', 'min', 'max'
                                'amount',
                                 "format" => "function(agg) {
                                    groupAmount[group] = agg; // save group amount of this customer group
                                    return '$'+agg.toFixed(2);
                                }",
                            ],

Also added below

"onReady" => "function() {
                myTable1
                .order( [ 10, 'desc' ] ) // sort 'dollar_sales' field, index of which is 3
                .draw();
            }"

It is not showing in desc order. Please check and advise. ASAP.

Sebastian Morales commented on Sep 10

Pls post your full DataTables' create code.

Sawan Ruparel commented on Sep 10

Below is the full code

 DataTables::create(array(
            "name" => "myTable1",
            "dataSource" => $this->dataStore("test"),
            "themeBase" => "bs4", // Optional option to work with Bootsrap 4
            "plugins"=>array("Responsive","Buttons"),
            "cssClass" => array(
                "table"=>"table table-striped table-bordered"
            ),
            "columns" => [
                "name" => [
                    "label" => 'Name',
                ],
                "pname" => [
                    "label" => 'PName'
                ],
                "status" => [
                    "label" => 'Status'
                ],
                "prname" => [
                    "label" => 'prname'
                ],
                "pn" => [
                    "label" => 'pn'
                ],
                "rf" => [
                    "label" => 'rf'
                ],
                "tx" => [
                    "label" => 'tx'
                ],
                "txn" => [
                    "label" => 'txn'
                ],
                "nd" => [
                    "label" => 'nd'
                ],
                "dt" => [
                    "label" => 'dt'
                ],
                "amount" => [
                    "label" => 'Amount',
                    'formatValue'=>function($value, $row, $cKey)
                    {
                        return '$'.$row['amount'];
                    },
                    "type" => "num-fmt",
                    "render" => "function(data, type, row, meta) {
                        var customerGroupName = row[0]; // because group field 'customerName' index is 0
                        var customerGroupAmount = groupAmount[customerGroupName];
                        return (type !== 'sort' || customerGroupAmount === undefined) ? 
                            $.fn.dataTable.render.number( '.', ',', 0, '' ).display(data) : customerGroupAmount;
                    }"
                ],
                "hp" => [
                    "label" => 'hp'
                ],
            ],
            "clientRowGroup" => 'name' => [
                        'calculate' => [
                            'totalAmount' => [
                                'sum', //'sum', 'count', 'avg', 'min', 'max'
                                'amount',
                                 "format" => "function(agg) {
                                    groupAmount[group] = agg; // save group amount of this customer group
                                    return agg;
                                }",
                            ],
                            'countPatient' => ['count', 'pn'],
                        ],
                        "top" => "<td colspan='999'>{expandCollapseIcon} {".$this->params["selected_group"]."} </td>",
                        "bottom" => "<td >{expandCollapseIcon}  Total</td><td> {countPatient}</td><td></td><td></td><td></td><td></td><td></td><td></td><td> Amount</td><td>{totalAmount}</td>",
                    ]
                ],
            "fastRender" => true,
            "options" => [
                "paging" => true,
                "searching" => false,
                "pageLength" => 50,
                'columnDefs' => array(
                    array(
                        'visible' => false,
                        'targets' => $target, //hide the first column
                    )
                ),
                'ordering' => $ordering
                // "colReorder" => true,
                // "select"=>true,
                // "dom" => 'Bfrtip',
                // "buttons" => [
                //     "excel", "pdf", "print", "colvis"
                // ],
                
            ],
            "onReady" => "function() {
                myTable1
                .order( [ 10, 'desc' ] ) // sort 'dollar_sales' field, index of which is 3
                .draw();
            }",
        ));
Sebastian Morales commented on Sep 10

I see that you set "paging" => true. Pls note that DataTable's client row group only groups the rows in the current page. If you change page, the total amount changes, too. It's advisable to set "paging" => false when using client row group. If the problem remains, pls share a screenshot of the problem and mark where it is in the screenshot.

Sawan Ruparel commented on Sep 10

Changed above still having issue. Please check below screenshot.

Sebastian Morales commented on Sep 10

It looks like the total amount is sorted by string instead of formatted number. Pls try to change your amount's type to the following to see if it works:

                "amount" => [
                    "label" => 'Amount',
                    'formatValue'=>...,
                    "type" => "num", // num-fmt, html-num, html-num-fmt
                    "render" => ...,
                ], 
Sawan Ruparel commented on Sep 10

No it is not working. same records are showing.

Sebastian Morales commented on Sep 13

Pls send a sample of your data and your report's code to support@koolreport.com for us to replicate this problem and fix it for you. Tks,

Sawan Ruparel commented 3 days ago

Okay. I sent the mail please look into it and Let me know.

Sebastian Morales commented 3 days ago

We received your attachment and found that if you set "ordering" => false then the amount column can not be ordered. It explains why your groups can not be ordered by the total amount. Pls try to set "ordering" => true in DataTables' options and see if it solves the problem for you.

In case you don't want to enable ordering for other columns you could set them like this:

    ...
    "options" => [
        'columnDefs' => array(
            array(
                'orderable' => false,
                'targets' => [0, 1, 2, 3], //disable ordering option for the 1st, 2nd, 3rd and 4th columns
            ),
        ),
        'ordering' => true, 
Sawan Ruparel commented 2 days ago

Not working.

Sebastian Morales commented 2 days ago

Next time please clarify exactly and specifically how sorting doesn't work. It looks like group amount sorting works fine now that "ordering" => true. Only the row sorting in each group hasn't worked yet. Pls try this method:

                    "amount" => [
                        "formatValue" => function($value) {
                            return "$" . $value;
                        },
                        "type" => "num-fmt",
                        "render" => "function(data, type, row, meta) {
                            var customerGroupName = row[0]; // because group field 'customerName' index is 0
                            var customerGroupAmount = groupAmount[customerGroupName];
                            var numData = data.replace('$', '') * 1;
                            return (type !== 'sort' || customerGroupAmount === undefined) ? 
                                $.fn.dataTable.render.number( '.', ',', 0, '' ).display(data) : (customerGroupAmount * 10000000 + numData ); // use 10000000 multiplying here if your data is always less than 10000000. Otherwise increase to 10000000000
                        }"
                    ], 

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed

DataGrid