DataTables

How to use client-based row group with DataTables

customerName productLine productName dollar_sales
Online Diecast Creations Co. Vintage Cars 1917 Grand Touring Sedan 4080
Online Diecast Creations Co. Vintage Cars 1911 Ford Town Car 2754.5
Online Diecast Creations Co. Vintage Cars 1932 Alfa Romeo 8C2300 Spider Sport 1660.12
Online Diecast Creations Co. Vintage Cars 1936 Mercedes Benz 500k Roadster 1729.21
Blauer See Auto, Co. Vintage Cars 1932 Model A Ford J-Coupe 2701.5
Blauer See Auto, Co. Vintage Cars 1928 Mercedes-Benz SSK 4343.56
Blauer See Auto, Co. Vintage Cars 1939 Chevrolet Deluxe Coupe 1463.8500000000001
Blauer See Auto, Co. Vintage Cars 1938 Cadillac V-16 Presidential Limousine 2040.1000000000001
Vitachrome Inc. Vintage Cars 1937 Lincoln Berline 3726.45
Vitachrome Inc. Vintage Cars 1936 Mercedes-Benz 500K Special Roadster 1768.3300000000002
Baane Mini Imports Classic Cars 1952 Alpine Renault 1300 5571.8
Baane Mini Imports Classic Cars 1962 LanciaA Delta 16V 5026.14
Baane Mini Imports Trucks and Buses 1958 Setra Bus 3284.28
Baane Mini Imports Trucks and Buses 1940 Ford Pickup Truck 3307.5
Baane Mini Imports Trucks and Buses 1926 Ford Fire Engine 1283.48
Baane Mini Imports Vintage Cars 1913 Ford Model T Speedster 2489.13
Baane Mini Imports Vintage Cars 1934 Ford V8 Coupe 2164.4
Baane Mini Imports Vintage Cars 18th Century Vintage Horse Carriage 2173
Baane Mini Imports Vintage Cars 1917 Maxwell Touring Car 3970.26
Baane Mini Imports Trucks and Buses 1940s Ford truck 3530.5199999999995
Baane Mini Imports Vintage Cars 1939 Cadillac Limousine 1670.75
Baane Mini Imports Trucks and Buses 1962 Volkswagen Microbus 3864.2400000000002
Baane Mini Imports Vintage Cars 1936 Chrysler Airflow 2215.5
Baane Mini Imports Trucks and Buses 1980’s GM Manhattan Express 2866.2599999999998
Baane Mini Imports Trucks and Buses 1996 Peterbilt 379 Stake Bed with Outrigger 2850.75
Baane Mini Imports Classic Cars 1982 Camaro Z28 3950.9399999999996
Euro+ Shopping Channel Classic Cars 1969 Corvair Monza 4468.96
Euro+ Shopping Channel Trucks and Buses 1957 Chevy Pickup 4566.99
Euro+ Shopping Channel Classic Cars 1998 Chrysler Plymouth Prowler 3261.6000000000004
Euro+ Shopping Channel Trucks and Buses 1964 Mercedes Tour Bus 3559.17
Euro+ Shopping Channel Classic Cars 1992 Ferrari 360 Spider red 3816.85
Euro+ Shopping Channel Classic Cars 1970 Triumph Spitfire 4529.6
Euro+ Shopping Channel Classic Cars 1970 Dodge Coronet 1820.7
Euro+ Shopping Channel Classic Cars 1958 Chevy Corvette Limited Edition 1338.04
Euro+ Shopping Channel Classic Cars 1992 Porsche Cayenne Turbo Silver 2767.7000000000003
Euro+ Shopping Channel Trucks and Buses 1954 Greyhound Scenicruiser 1818.25
Euro+ Shopping Channel Trains 1950's Chicago Surface Lines Streetcar 2770.95
Euro+ Shopping Channel Trucks and Buses Diamond T620 Semi-Skirted Tanker 3781.4700000000003
Euro+ Shopping Channel Trains 1962 City of Detroit Streetcar 1705.92
Danish Wholesale Imports Classic Cars 1972 Alfa Romeo GTA 6392
Danish Wholesale Imports Classic Cars 2001 Ferrari Enzo 8434.52
Danish Wholesale Imports Classic Cars 1969 Ford Falcon 4114.5199999999995
Danish Wholesale Imports Vintage Cars 1903 Ford Model A 3004.98
Danish Wholesale Imports Trains Collectable Wooden Train 3333.7400000000002
Danish Wholesale Imports Vintage Cars 1904 Buick Runabout 3094.6800000000003
Danish Wholesale Imports Ships 18th century schooner 5072.71
Danish Wholesale Imports Vintage Cars 1912 Ford Model T Delivery Wagon 3232.24
Danish Wholesale Imports Vintage Cars 1940 Ford Delivery Sedan 3773.5
Danish Wholesale Imports Ships The Schooner Bluenose 2214
Danish Wholesale Imports Ships The Mayflower 2511.69
Danish Wholesale Imports Ships The USS Constitution Ship 1882.32
Danish Wholesale Imports Ships The Titanic 3594.24
Danish Wholesale Imports Ships The Queen Mary 2184.82
Danish Wholesale Imports Ships Pont Yacht 1119.25
Rovelli Gifts Planes 1980s Black Hawk Helicopter 4825.44
Rovelli Gifts Planes P-51-D Mustang 2757.3999999999996
Rovelli Gifts Ships 1999 Yamaha Speed Boat 3315.2599999999998
Rovelli Gifts Vintage Cars 1941 Chevrolet Special Deluxe Cabriolet 3863.02
Rovelli Gifts Planes 1928 British Royal Navy Airplane 3002.44
Rovelli Gifts Planes 1900s Vintage Bi-Plane 3222.73
Rovelli Gifts Vintage Cars 1937 Horch 930V Limousine 1732.59
Rovelli Gifts Planes Corsair F4U ( Bird Cage) 2798
Rovelli Gifts Planes 1900s Vintage Tri-Plane 1846
Rovelli Gifts Vintage Cars 1928 Ford Phaeton Deluxe 2156.5499999999997
Rovelli Gifts Vintage Cars 1930 Buick Marquette Phaeton 1395.42
Rovelli Gifts Planes American Airlines: B767-300 2831.54
Rovelli Gifts Ships HMS Bounty 2552.7000000000003
Rovelli Gifts Planes America West Airlines B757-200 3390.48
Rovelli Gifts Planes ATA: B757-300 3644.8
Rovelli Gifts Planes F/A 18 Hornet 1/72 3344
Rovelli Gifts Planes American Airlines: MD-11S 3375.84
Rovelli Gifts Planes Boeing X-32A JSF 2097.6000000000004
Land of Toys Inc. Motorcycles 1969 Harley Davidson Ultimate Chopper 2440.5
Land of Toys Inc. Motorcycles 1996 Moto Guzzi 1100i 4128.54
Land of Toys Inc. Motorcycles 2003 Harley-Davidson Eagle Drag Bike 4653.72
Land of Toys Inc. Motorcycles 2002 Suzuki XREO 2562
Land of Toys Inc. Motorcycles 1936 Harley Davidson El Knucklehead 1528.3000000000002
Land of Toys Inc. Motorcycles 1997 BMW R 1100 S 2423
Land of Toys Inc. Motorcycles 1960 BSA Gold Star DBD34 2778.5600000000004
Land of Toys Inc. Motorcycles 1997 BMW F650 ST 1778
Cruz & Sons Co. Classic Cars 1968 Ford Mustang 5457.54
Cruz & Sons Co. Classic Cars 1968 Dodge Charger 4333.5
Cruz & Sons Co. Classic Cars 1970 Plymouth Hemi Cuda 2956.59
Cruz & Sons Co. Classic Cars 1969 Dodge Charger 3855.6
Cruz & Sons Co. Classic Cars 1948 Porsche 356-A Roadster 2574.88
Cruz & Sons Co. Classic Cars 1969 Dodge Super Bee 1902.42
Cruz & Sons Co. Classic Cars 1976 Ford Gran Torino 3836.41
Cruz & Sons Co. Motorcycles 1957 Vespa GS150 2272.1200000000003
Cruz & Sons Co. Classic Cars 1957 Corvette Convertible 6154.280000000001
Cruz & Sons Co. Motorcycles 1982 Ducati 900 Monster 2254.35
Cruz & Sons Co. Classic Cars 1971 Alpine Renault 1600s 1800.3
Cruz & Sons Co. Classic Cars 1956 Porsche 356A Coupe 5280
Cruz & Sons Co. Classic Cars 1961 Chevrolet Impala 2080.1
Cruz & Sons Co. Motorcycles 1982 Ducati 996 R 977.6700000000001
Cruz & Sons Co. Motorcycles 1974 Ducati 350 Mk3 Desmo 2720.56
Cruz & Sons Co. Motorcycles 2002 Yamaha YZR M1 2544.8999999999996
Motor Mint Distributors Inc. Classic Cars 1993 Mazda RX-7 3054.48
Motor Mint Distributors Inc. Classic Cars 1995 Honda Civic 5243.24
Motor Mint Distributors Inc. Classic Cars 1999 Indy 500 Monte Carlo SS 3294.72
Motor Mint Distributors Inc. Classic Cars 1992 Ferrari 360 Spider red 7400.02
Motor Mint Distributors Inc. Classic Cars 1948 Porsche Type 356 Roadster 5909.78
Motor Mint Distributors Inc. Classic Cars 1982 Lamborghini Diablo 930.9000000000001
AV Stores, Co. Classic Cars 1965 Aston Martin DB5 4374.14
AV Stores, Co. Vintage Cars 1917 Grand Touring Sedan 6426
AV Stores, Co. Vintage Cars 1911 Ford Town Car 1646.72
AV Stores, Co. Vintage Cars 1932 Model A Ford J-Coupe 3817.77
AV Stores, Co. Vintage Cars 1928 Mercedes-Benz SSK 5074.39
AV Stores, Co. Vintage Cars 1932 Alfa Romeo 8C2300 Spider Sport 2293.48
AV Stores, Co. Classic Cars 1957 Ford Thunderbird 2604
AV Stores, Co. Classic Cars 1970 Chevy Chevelle SS 454 2592.72
AV Stores, Co. Classic Cars 1966 Shelby Cobra 427 S/C 1254.8300000000002
AV Stores, Co. Vintage Cars 1939 Chevrolet Deluxe Coupe 577.6
AV Stores, Co. Vintage Cars 1938 Cadillac V-16 Presidential Limousine 1590.0300000000002
AV Stores, Co. Classic Cars 1949 Jaguar XK 120 3555.67
AV Stores, Co. Classic Cars 1952 Citroen-15CV 5186.04
AV Stores, Co. Classic Cars 1969 Chevrolet Camaro Z28 2172.69
AV Stores, Co. Classic Cars 2002 Chevy Corvette 3565.69
AV Stores, Co. Vintage Cars 1936 Mercedes Benz 500k Roadster 1693.92
Mini Wheels Co. Vintage Cars 1937 Lincoln Berline 2881.89
Mini Wheels Co. Vintage Cars 1936 Mercedes-Benz 500K Special Roadster 2328.96
Mini Wheels Co. Vintage Cars 1934 Ford V8 Coupe 1486.52
Mini Wheels Co. Vintage Cars 18th Century Vintage Horse Carriage 4052.75
Mini Wheels Co. Vintage Cars 1917 Maxwell Touring Car 3559.5299999999997
Mini Wheels Co. Vintage Cars 1936 Chrysler Airflow 2228.2000000000003
Volvo Model Replicas, Co Classic Cars 1952 Alpine Renault 1300 5717.64
Volvo Model Replicas, Co Vintage Cars 1913 Ford Model T Speedster 1957.3
Mini Gifts Distributors Ltd. Trucks and Buses 1958 Setra Bus 2554.44
Mini Gifts Distributors Ltd. Trucks and Buses 1940 Ford Pickup Truck 4973.5
Mini Gifts Distributors Ltd. Vintage Cars 1939 Cadillac Limousine 2163.5
Mini Gifts Distributors Ltd. Trucks and Buses 1996 Peterbilt 379 Stake Bed with Outrigger 1352.86
La Corne D'abondance, Co. Classic Cars 1962 LanciaA Delta 16V 3984.43
La Corne D'abondance, Co. Trucks and Buses 1964 Mercedes Tour Bus 4164.42
La Corne D'abondance, Co. Trucks and Buses 1926 Ford Fire Engine 2406.6
La Corne D'abondance, Co. Classic Cars 1992 Ferrari 360 Spider red 8128.32
La Corne D'abondance, Co. Trucks and Buses 1940s Ford truck 4318.9400000000005
La Corne D'abondance, Co. Trucks and Buses 1962 Volkswagen Microbus 2146.83
La Corne D'abondance, Co. Classic Cars 1958 Chevy Corvette Limited Edition 687.36
La Corne D'abondance, Co. Trucks and Buses 1980’s GM Manhattan Express 2835.52
La Corne D'abondance, Co. Trucks and Buses 1954 Greyhound Scenicruiser 1227.24
La Corne D'abondance, Co. Classic Cars 1982 Camaro Z28 3483.48
Classic Legends Inc. Trucks and Buses 1957 Chevy Pickup 5123.94
Classic Legends Inc. Classic Cars 1998 Chrysler Plymouth Prowler 6477.26
Classic Legends Inc. Classic Cars 1970 Dodge Coronet 2662.08
Classic Legends Inc. Classic Cars 1992 Porsche Cayenne Turbo Silver 4683.8
Classic Legends Inc. Trucks and Buses Diamond T620 Semi-Skirted Tanker 2718.9
Royale Belge Trains 1950's Chicago Surface Lines Streetcar 1627.56
Dragon Souveniers, Ltd. Classic Cars 2001 Ferrari Enzo 6445.89
Dragon Souveniers, Ltd. Classic Cars 1969 Corvair Monza 6366.58
Dragon Souveniers, Ltd. Classic Cars 1969 Ford Falcon 6747.780000000001
Dragon Souveniers, Ltd. Vintage Cars 1903 Ford Model A 3160.8199999999997
Dragon Souveniers, Ltd. Trains Collectable Wooden Train 1715.2800000000002
Dragon Souveniers, Ltd. Classic Cars 1970 Triumph Spitfire 2685.7599999999998
Dragon Souveniers, Ltd. Vintage Cars 1904 Buick Runabout 1695.7900000000002
Dragon Souveniers, Ltd. Ships 18th century schooner 4887.2
Dragon Souveniers, Ltd. Trains 1962 City of Detroit Streetcar 1168.6499999999999
Dragon Souveniers, Ltd. Ships The Mayflower 2863.2999999999997
Dragon Souveniers, Ltd. Ships The Queen Mary 4022.1
Dragon Souveniers, Ltd. Ships Pont Yacht 2621
Enaco Distributors Ships The Titanic 3101.4
Salzburg Collectables Classic Cars 1972 Alfa Romeo GTA 5192.48
Salzburg Collectables Planes 1980s Black Hawk Helicopter 6509.34
Salzburg Collectables Ships 1999 Yamaha Speed Boat 1571.64
Salzburg Collectables Vintage Cars 1941 Chevrolet Special Deluxe Cabriolet 2572.56
Salzburg Collectables Planes 1900s Vintage Bi-Plane 2640.4
Salzburg Collectables Vintage Cars 1912 Ford Model T Delivery Wagon 2540.2999999999997
Salzburg Collectables Vintage Cars 1937 Horch 930V Limousine 1262.3999999999999
Salzburg Collectables Vintage Cars 1940 Ford Delivery Sedan 2876.3
Salzburg Collectables Planes Corsair F4U ( Bird Cage) 1738.8
Salzburg Collectables Ships The Schooner Bluenose 1433.5
Salzburg Collectables Ships HMS Bounty 2152.67
Salzburg Collectables Ships The USS Constitution Ship 2554.36
Salzburg Collectables Planes American Airlines: MD-11S 1655.42
Salzburg Collectables Planes Boeing X-32A JSF 1126.1599999999999
Australian Collectors, Co. Motorcycles 1996 Moto Guzzi 1100i 3449.2599999999998
Australian Collectors, Co. Motorcycles 2003 Harley-Davidson Eagle Drag Bike 7304.8
Australian Collectors, Co. Planes P-51-D Mustang 2400.9100000000003
Australian Collectors, Co. Motorcycles 1936 Harley Davidson El Knucklehead 2646.84
Australian Collectors, Co. Motorcycles 1997 BMW R 1100 S 3865.75
Australian Collectors, Co. Planes 1928 British Royal Navy Airplane 3627.3900000000003
Australian Collectors, Co. Motorcycles 1960 BSA Gold Star DBD34 2460.24
Australian Collectors, Co. Planes 1900s Vintage Tri-Plane 2080.17
Australian Collectors, Co. Motorcycles 1997 BMW F650 ST 2087.8
Australian Collectors, Co. Vintage Cars 1928 Ford Phaeton Deluxe 1994.91
Australian Collectors, Co. Vintage Cars 1930 Buick Marquette Phaeton 2031.54
Australian Collectors, Co. Planes American Airlines: B767-300 4292.9800000000005
Australian Collectors, Co. Planes America West Airlines B757-200 1962.48
Australian Collectors, Co. Planes ATA: B757-300 2562.96
Australian Collectors, Co. Planes F/A 18 Hornet 1/72 3096
Reims Collectables Motorcycles 1969 Harley Davidson Ultimate Chopper 2928.42
Reims Collectables Motorcycles 2002 Suzuki XREO 6326
Reims Collectables Motorcycles 1982 Ducati 900 Monster 1861.76
Reims Collectables Motorcycles 1974 Ducati 350 Mk3 Desmo 2398.25
Reims Collectables Motorcycles 2002 Yamaha YZR M1 3186.04
Marseille Mini Autos Classic Cars 1968 Ford Mustang 6537.72
Marseille Mini Autos Classic Cars 1968 Dodge Charger 4215.04
Marseille Mini Autos Classic Cars 1970 Plymouth Hemi Cuda 2094.08
Marseille Mini Autos Classic Cars 1969 Dodge Charger 2096
Marseille Mini Autos Classic Cars 1993 Mazda RX-7 3898.1000000000004
Marseille Mini Autos Classic Cars 1948 Porsche 356-A Roadster 2681.91
Marseille Mini Autos Classic Cars 1995 Honda Civic 3527.7999999999997

DataTables supports row grouping through its RowGroup plugin but its setting is a bit complicated. We simplify that setting with clientRowGroup property. For example:

<?php 
DataTables::create(array(
    ...
    "clientRowGroup" => [
        "customerName" => [
            'direction' => 'asc', //'asc', 'desc'
            'calculate' => [
                'totalSales' => [
                    'sum', //'sum', 'count', 'avg', 'min', 'max'
                    'dollar_sales'
                    "format" => "function(value) {return value.toFixed(2);}",
                ], 
            ],
            "top" => "<td colspan='999'>{expandCollapseIcon} Top: Customer: {customerName} | Total: {totalSales}</td>",
            "bottom" => "<td colspan='999'>{expandCollapseIcon} Bottom: Customer: {customerName} | Customer sales: {totalSales}</td>",
        ],
        "productLine" => [
            'direction' => 'desc',
            'calculate' => [
                'customAvgSales' => [
                    "aggregate" => "function(rows, group, aggFieldIndex) {
                        return rows
                        .data()
                        .pluck(aggFieldIndex)
                        .reduce( function (a, b) {
                            return a + 1*b.replace(/[^\d\.]/g, '');
                        }, 0) / rows.count()}
                    ",
                    'field' => 'dollar_sales',
                ],
            ],
            "top" => "<td colspan='999'>{expandCollapseIcon} Top: Line: {productLine} | Custom avg: {customAvgSales}",
        ],
    ],
));
?>
<?php
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();
<?php
//Step 1: Load KoolReport
require_once "../../../load.koolreport.php";

//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "automaker"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=automaker",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        ); 
    } 
    protected function setup()
    {
        $this->src('automaker')
        ->query('select * from customer_product_dollarsales2')
        ->pipe(new \koolreport\processes\Limit(array(200, 0)))
        ->pipe($this->dataStore("sales"));
    } 

}
<?php
    use \koolreport\datagrid\DataTables;
?>
<div class="report-content">
    <div class="text-center">
        <h1>DataTables</h1>
        <p class="lead">
        How to use client-based row group with DataTables
        </p>
    </div>
    <style>
        tr[role="row"] td:first-child {
            padding-left: 50px;
        }
    </style>
    <?php
    DataTables::create(array(
        "dataSource"=>$this->dataStore("sales"),
        "themeBase"=>"bs4", // Optional option to work with Bootsrap 4
        "cssClass"=>array(
            "table"=>"table table-striped table-bordered"
        ),
        "columns" => ["customerName", "productLine", "productName", "dollar_sales"],
        "clientRowGroup" => [
            "customerName" => [
                'direction' => 'asc',
                'calculate' => [
                    'totalSales' => [
                        'sum',  //'sum', 'count', 'avg', 'min', 'max' 
                        'dollar_sales'
                    ],
                ],
                "top" => "<td colspan='999'>{expandCollapseIcon} Top: Customer: {customerName} | Total: {totalSales}</td>",
                "bottom" => "<td colspan='999'>{expandCollapseIcon} Bottom: Customer: {customerName} | Customer sales: {totalSales}</td>",
            ],
            "productLine" => [
                'direction' => 'desc',
                'calculate' => [
                    'totalSales' => ['sum', 'dollar_sales',
                        "format" => "function(value) {return value.toFixed(3);}",
                    ],
                    'avgSales' => [
                        'field' => 'dollar_sales',
                        "aggregate" => "function(rows, group, aggFieldIndex) {
                            return rows
                            .data()
                            .pluck(aggFieldIndex)
                            .reduce( function (a, b) {
                                return a + 1*b.replace(/[^\d\.]/g, '');
                            }, 0) / rows.count()}",
                        "format" => "function(value) {return value.toFixed(5);}",
                    ],
                    'countSales' => ['count', 'dollar_sales'],
                    'minSales' => ['min', 'dollar_sales'],
                    'maxSales' => ['max', 'dollar_sales'],
                ],
                "top" => "<td colspan='999'>{expandCollapseIcon} Top: Line: {productLine} | Total: {totalSales} | Avg: {avgSales} | Count: {countSales} | Min: {minSales} | max: {maxSales}</td>",
                // "bottom" => "<td colspan='999'>{expandCollapseIcon} Bottom: Line: {productLine} | Line sales: {totalSales}</td>",
            ],
        ],
        "options" => [
            "paging" => true,
            "searching" => true,
            "pageLength" => 25,
        ],
    ));
    ?>
</div>

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport Get KoolReport Pro