KoolReport's Forum

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

Customized Filtering/Sorting not happening in data tables #3051

Closed Lalitha opened this topic on on May 15, 2023 - 10 comments
Lalitha commented on May 15, 2023

DataTables::create(array(

"name" => "table1",
"dataStore" => isset($this->params["subject"]) ? $this->dataStore("Strategy_list_Subject") : [],
// "data"=>isset($this->params["customerNumber"])?null:array(),
//"plugins" => ["Buttons", "FixedColumns", "FixedHeader", "KeyTable", "Responsive", "RowReorder", "Scroller", "SearchPanes"],
"options" => array(
    //"dom" => 'Blfrtip',
    "select"=>true,
    "searching" => true,
           "fixedHeader"=>true,
    
    
    'columnDefs' => array(
        array(
            'type' => 'customType',
            'targets' => 9,
        ),
        array(
            'type' => 'customType',
            'targets' => 10,
        ),
        array(
            'type' => 'customType',
            'targets' => 11,
        ),
        array(
            'type' => 'customType',
            'targets' => 12,
        ),
        array(
            'type' => 'customType',
            'targets' => 13,
        ),
        array(
            'type' => 'customType',
            'targets' => 14,
        ),
        ),
        /*'order' => array(
            array(9, 'asc'),
            array(10, 'asc'),
            array(11, 'asc'),
            array(12, 'asc'),
            array(13, 'asc'),
            array(14, 'asc'),
        ),*/
),
"showFooter" => true,
"columns" => array(
   
    "subjectnm" => array(
        "label" => "Subject Name",
    ),
   
   
    "user2122" => array(
        "label" => "2021-22 Total User Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }
    ),
    "uservol2122" => array(
        "label" => "2021-22 Total User Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }

    ),
    "plnsch" => array(
        "label" => "2022-23 Total Schools Planned",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }
    ),
    "plnschvol" => array(
        "label" => "2022-23  Total Schools Planned Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }

    ),
    "retainpln" => array(
        "label" => "2022-23  Total Retain Schools Planned",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }

    ),
    "retainplnvol" => array(
        "label" => "2022-23 Total Retain Schools Planned Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }

    ),
    "newpln" => array(
        "label" => "2022-23 Total New Schools Planned",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }

    ),
    "newplnvol" => array(
        "label" => "2022-23 Total New Schools Planned Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }
      

    ),
    "user2223" => array(
        "label" => "2022-23 Total User Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'user2223')
            {
                if ($row["user2122"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["user2122"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["user2122"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        }, 
    ),
    "uservol2223" => array(
        "label" => "2022-23 Total User Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'uservol2223')
            {
                if ($row["uservol2122"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["uservol2122"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["uservol2122"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        }, 

    ),
   
    "retainuser" => array(
        "label" => "2022-23 Total Retain Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'retainuser')
            {
                if ($row["retainpln"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["retainpln"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["retainpln"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        }, 
    ),
    "retainuservol" => array(
        "label" => "2022-23 Total Retain Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'retainuservol')
            {
                if ($row["retainplnvol"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["retainplnvol"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["retainplnvol"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        }, 

    ),
    "newuser" => array(
        "label" => "2022-23 Total New Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'newuser')
            {
                if ($row["newpln"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["newpln"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["newpln"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        }, 
       
    ),
    "newuservol" => array(
        "label" => "2022-23 Total New Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'newuservol')
            {
                if ($row["newplnvol"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["newplnvol"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["newplnvol"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        }, 

    ),
    "lostuser" => array(
        "label" => "Total Lost Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
    ),
    "lostuservol" => array(
        "label" => "Total Lost Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }

    ),
),
"cssClass" => array(
    // "table" => "table table-bordered table-striped table-hover cssText is-datatable",
    
    "table" => "table table-bordered table-hover",
    "th"=>"cssHeader",
    "tr"=>"cssItem",
    "tf"=>"cssIFooter",
    "td"=>function($row,$colName)
        {
            if ($colName=="subjectnm" or $colName=="seriesnm" or $colName=="execcode" or $colName=="execname")
                {
                    return "cssTd";
                }
            else if ($colName=="user2122" or $colName=="uservol2122" or $colName=="user2223" or $colName=="retainuser" or $colName=="retainuservol" or 
                     $colName=="newuser" or $colName=="newuservol" )
                {
                    return "cssTdw";
                }
            else
                {
                    return "cssTdw1";
                }
        },
),

));

in columndef, for the type: customtype it is sorting based on indicator/color i have given, I need sorting based on the number present on that column....its not happening like remaining columns, Please help me in this regard.

Sebastian Morales commented on May 17, 2023

Pls try this code for custom type sorting:

        <?php
            DataTables::create(array(
                ...
                "options" => array(
                    ...
                    'columnDefs' => array(
                        array(
                            'type' => 'customType',
                            'targets' => [0, 1, 2] // target the first three columns
                        )
                    ),
                ),
                ...
                "onBeforeInit" => "dtBeforeInit",
            ));
        ?>
        <script>
            function dtBeforeInit() {
                // console.log('dtBeforeInit');
                jQuery.extend( jQuery.fn.dataTable.ext.type.order, {
                    "customType-pre": function (a) {
                        // console.log('pre', a);
                        if (a === 'Danish Wholesale Imports') a = 'A' + a;
                        return a;
                    },
                    "customType-asc": function (a, b) {
                        // console.log('asc', a, b);
                        return ((a < b) ? 1 : ((a > b) ? -1 : 0));
                    },
                    "customType-desc": function (a, b) {
                        // console.log('desc', a, b);
                        return ((a < b) ? -1 : ((a > b) ? 1 : 0));
                    }
                } );
            }
        </script>

Change the sorting logic if you want. Let us know the result. Cheers,

Lalitha commented on May 22, 2023

its not working, still applying filter on color only, i need on numericals

Sebastian Morales commented on May 22, 2023

Pls post screenshots with the problem description for us to better understand your issue.

Lalitha commented on May 22, 2023

In the above images you can clearly see the sorting/filtering is happening to color not for the numbers.... Please check once and i have implemented the above code only

Sebastian Morales commented on May 24, 2023

Could it be because you overwrite the type of that column with "customType":

    'columnDefs' => array(
        array(
            'type' => 'customType',
            'targets' => ...,
        ),

Thus it doesn't sort by type "num-fmt" anymore. Try removing "customType" or replacing it with "num-fmt" to see how it works. Rgds,

Lalitha commented on May 24, 2023

I have tried before only, with num-fmt and by removing the custom type, still no improvements, even they won't sort by color atleast and numbering also not working

Sebastian Morales commented on May 25, 2023

Pls post your customType's sorting function "customType-asc" and "customType-desc" for us to check them for you.

Lalitha commented on May 26, 2023

DataTables::create(array(

"name" => "table1",
"dataStore" => isset($this->params["subject"]) ? $this->dataStore("Strategy_list_Subject") : [],
// "data"=>isset($this->params["customerNumber"])?null:array(),
//"plugins" => ["Buttons", "FixedColumns", "FixedHeader", "KeyTable", "Responsive", "RowReorder", "Scroller", "SearchPanes"],
"options" => array(
    //"dom" => 'Blfrtip',
    "select"=>true,
    "searching" => true,
           "fixedHeader"=>true,
    
    
    'columnDefs' => array(
        array(
            'type' => 'customType-asc',
            'targets' => 9,
        ),
        array(
            'type' => 'customType-asc',
            'targets' => 10,
        ),
        array(
            'type' => 'customType-asc',
            'targets' => 11,
        ),
        array(
            'type' => 'customType-asc',
            'targets' => 12,
        ),
        array(
            'type' => 'customType-asc',
            'targets' => 13,
        ),
        array(
            'type' => 'customType-asc',
            'targets' => 14,
        ),
        ),
    /*    'order' => array(
            array(9, 'asc'),
            array(10, 'asc'),
            array(11, 'asc'),
            array(12, 'asc'),
            array(13, 'asc'),
            array(14, 'asc'),
        ),*/
),
"showFooter" => true,
"columns" => array(
   
    "subjectnm" => array(
        "label" => "Subject Name",
    ),
   
   
    "user2122" => array(
        "label" => "2021-22 Total User Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        },
        "render" => "function(data, type, row) {
            var subjNM = row[0];
           
                var allParams = 'subjectnm=' + subjNM + '&cRmsUB=$CRMSUB&cRmcURR=$CRMCUR&LCN=$CRMLOC&ExeCd=$CRMEXECD&WANTFLAG=USER2122SUB';
           
        
            if (type === 'display' ) {
                return '<a href=\"http://datahubstaging.orientblackswandigital.com/modules/crm/MR/MarketReportFinMain.php?' + allParams + '\" target=\"_blank\">' + data + '</a>';
            } else {
                return data;
            }
        }"
    ),
    "uservol2122" => array(
        "label" => "2021-22 Total User Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }

    ),
    "plnsch" => array(
        "label" => "2022-23 Total Schools Planned",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue" => function($value, $row) {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value);
        },
        "render" => "function(data, type, row) {
            var subjNM = row[0];
           
                var allParams = 'subjectnm=' + subjNM + '&cRmsUB=$CRMSUB&cRmcURR=$CRMCUR&LCN=$CRMLOC&ExeCd=$CRMEXECD&WANTFLAG=PLANSCHSUB';
           
        
            if (type === 'display' ) {
                return '<a href=\"http://datahubstaging.orientblackswandigital.com/modules/crm/MR/MarketReportFinMain.php?' + allParams + '\" target=\"_blank\">' + data + '</a>';
            } else {
                return data;
            }
        }"

    ),
    
    "plnschvol" => array(
        "label" => "2022-23  Total Schools Planned Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        },
       

    ),
    "retainpln" => array(
        "label" => "2022-23  Total Retain Schools Planned",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        },
       

    ),
    "retainplnvol" => array(
        "label" => "2022-23 Total Retain Schools Planned Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        },
        

    ),
    "newpln" => array(
        "label" => "2022-23 Total New Schools Planned",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        },
       

    ),
    "newplnvol" => array(
        "label" => "2022-23 Total New Schools Planned Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        },
       
      

    ),
    "user2223" => array(
        "label" => "2022-23 Total User Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'user2223')
            {
                if ($row["user2122"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["user2122"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["user2122"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        },
        "render" => "function(data, type, row) {
            var subjNM = row[0];
           
                var allParams = 'subjectnm=' + subjNM + '&cRmsUB=$CRMSUB&cRmcURR=$CRMCUR&LCN=$CRMLOC&ExeCd=$CRMEXECD&WANTFLAG=USER2223SUB';
           
        
            if (type === 'display' ) {
                return '<a href=\"http://datahubstaging.orientblackswandigital.com/modules/crm/MR/MarketReportFinMain.php?' + allParams + '\" target=\"_blank\">' + data + '</a>';
            } else {
                return data;
            }
        }"
    ),
    "uservol2223" => array(
        "label" => "2022-23 Total User Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'uservol2223')
            {
                if ($row["uservol2122"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["uservol2122"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["uservol2122"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        },
       

    ),
   
    "retainuser" => array(
        "label" => "2022-23 Total Retain Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'retainuser')
            {
                if ($row["retainpln"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["retainpln"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["retainpln"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        },
        "render" => "function(data, type, row) {
            var subjNM = row[0];
           
                var allParams = 'subjectnm=' + subjNM + '&cRmsUB=$CRMSUB&cRmcURR=$CRMCUR&LCN=$CRMLOC&ExeCd=$CRMEXECD&WANTFLAG=RETAINUSERSUB';
           
        
            if (type === 'display' ) {
                return '<a href=\"http://datahubstaging.orientblackswandigital.com/modules/crm/MR/MarketReportFinMain.php?' + allParams + '\" target=\"_blank\">' + data + '</a>';
            } else {
                return data;
            }
        }"
    ),
    "retainuservol" => array(
        "label" => "2022-23 Total Retain Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'retainuservol')
            {
                if ($row["retainplnvol"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["retainplnvol"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["retainplnvol"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        },
        

    ),
    "newuser" => array(
        "label" => "2022-23 Total New Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'newuser')
            {
                if ($row["newpln"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["newpln"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["newpln"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        },
        "render" => "function(data, type, row) {
            var subjNM = row[0];
           
                var allParams = 'subjectnm=' + subjNM + '&cRmsUB=$CRMSUB&cRmcURR=$CRMCUR&LCN=$CRMLOC&ExeCd=$CRMEXECD&WANTFLAG=NEWUSERSUB';
           
        
            if (type === 'display' ) {
                return '<a href=\"http://datahubstaging.orientblackswandigital.com/modules/crm/MR/MarketReportFinMain.php?' + allParams + '\" target=\"_blank\">' + data + '</a>';
            } else {
                return data;
            }
        }"
    ),
    "newuservol" => array(
        "label" => "2022-23 Total New Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row, $cKey)
        {
            if ($cKey === 'newuservol')
            {
                if ($row["newplnvol"] == $value) {
                    $a = 100;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                } elseif  ($row["newplnvol"] > $value) {
                    $a = 50;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                elseif  ($row["newplnvol"] < $value){
                    $a = 101;
                    $css = indicators3($val = $a, $indicator_array = array(0, 99, 100), $indicator_rating = "normal");
                    return "<div class='" . $css . "'>" . preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value ) . "</div>";
                }
                else{
                    return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );     
                }
            }
            else{
                return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
            }   
        },
       

    ),
    "lostuser" => array(
        "label" => "Total Lost Schools",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "render" => "function(data, type, row) {
            var subjNM = row[0];
           
                var allParams = 'subjectnm=' + subjNM + '&cRmsUB=$CRMSUB&cRmcURR=$CRMCUR&LCN=$CRMLOC&ExeCd=$CRMEXECD&WANTFLAG=LOSTUSERSUB';
           
        
            if (type === 'display' ) {
                return '<a href=\"http://datahubstaging.orientblackswandigital.com/modules/crm/MR/MarketReportFinMain.php?' + allParams + '\" target=\"_blank\">' + data + '</a>';
            } else {
                return data;
            }
        }"
    ),
    "lostuservol" => array(
        "label" => "Total Lost Schools Volume",
        "type" => "num-fmt",
        "footer"=>"sum",
        "footerText"=>"<b>@value</b>",
        "formatValue"=>function($value,$row)
        {
            return preg_replace("/(\d+?)(?=(\d\d)+(\d)(?!\d))(\.\d+)?/i", "$1,", $value );
        }

    ),

),
"clientEvents" => array(
    "drawCallback" => "function(dt) {
        dt.$('td').each(function() {
            var cell = dt.cell(this);
            var colIndex = cell.index().column;
            var colName = dt.column(colIndex).header().innerText;

         
        });
    }"
),
"cssClass" => array(
    // "table" => "table table-bordered table-striped table-hover cssText is-datatable",
    
    "table" => "table table-bordered table-hover",
    "th"=>"cssHeader",
    "tr"=>"cssItem",
    "tf"=>"cssIFooter",
    "td"=>function($row,$colName)
        {
            if ($colName=="subjectnm" or $colName=="seriesnm" or $colName=="execcode" or $colName=="execname")
                {
                    return "cssTd";
                }
            else if ($colName=="user2122" or $colName=="uservol2122" or $colName=="user2223" or $colName=="retainuser" or $colName=="retainuservol" or 
                     $colName=="newuser" or $colName=="newuservol" )
                {
                    return "cssTdw";
                }
            else
                {
                    return "cssTdw1";
                }
        },
),

));

After placing customType-asc also, there is no use it is executing as below....

please help me to solve the issue

Sebastian Morales commented on May 26, 2023

Ok, there are multiple problems with your code:

1 . If you use custom type you must define customType-asc and customType-desc functions, don't use customType-asc as type.

2 . When not using custom type, type "num-fmt" doesn't work for your case because you format value the column to include <div> html tag, num-fmt can only sort formatted numbers, not html tag.

There are two solutions for your case:

1 . Remove all custom type and replace type "num-fmt" with type "html-num-fmt".

2 . Remove all custom type, still use type "num-fmt" but don't add <div> in your column's formatValue, only return the numeric value itself, instead use custom css class or style:

https://www.koolreport.com/docs/datagrid/datatables/#set-custom-css-classes

https://www.koolreport.com/docs/datagrid/datatables/#set-custom-css-styles

Lalitha commented on May 29, 2023

Thank You so much @Sebastian Morales... Removing all custom type and replace type "num-fmt" with type "html-num-fmt".......worked for me

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
None

Designed and built with by KoolPHP Inc.

Code licensed MIT.