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 4,080
Online Diecast Creations Co. Vintage Cars 1911 Ford Town Car 2,755
Online Diecast Creations Co. Vintage Cars 1932 Alfa Romeo 8C2300 Spider Sport 1,660
Online Diecast Creations Co. Vintage Cars 1936 Mercedes Benz 500k Roadster 1,729
Blauer See Auto, Co. Vintage Cars 1932 Model A Ford J-Coupe 2,702
Blauer See Auto, Co. Vintage Cars 1928 Mercedes-Benz SSK 4,344
Blauer See Auto, Co. Vintage Cars 1939 Chevrolet Deluxe Coupe 1,464
Blauer See Auto, Co. Vintage Cars 1938 Cadillac V-16 Presidential Limousine 2,040
Vitachrome Inc. Vintage Cars 1937 Lincoln Berline 3,726
Vitachrome Inc. Vintage Cars 1936 Mercedes-Benz 500K Special Roadster 1,768
Baane Mini Imports Classic Cars 1952 Alpine Renault 1300 5,572
Baane Mini Imports Classic Cars 1962 LanciaA Delta 16V 5,026
Baane Mini Imports Trucks and Buses 1958 Setra Bus 3,284
Baane Mini Imports Trucks and Buses 1940 Ford Pickup Truck 3,308
Baane Mini Imports Trucks and Buses 1926 Ford Fire Engine 1,283
Baane Mini Imports Vintage Cars 1913 Ford Model T Speedster 2,489
Baane Mini Imports Vintage Cars 1934 Ford V8 Coupe 2,164
Baane Mini Imports Vintage Cars 18th Century Vintage Horse Carriage 2,173
Baane Mini Imports Vintage Cars 1917 Maxwell Touring Car 3,970
Baane Mini Imports Trucks and Buses 1940s Ford truck 3,531
Baane Mini Imports Vintage Cars 1939 Cadillac Limousine 1,671
Baane Mini Imports Trucks and Buses 1962 Volkswagen Microbus 3,864
Baane Mini Imports Vintage Cars 1936 Chrysler Airflow 2,216
Baane Mini Imports Trucks and Buses 1980’s GM Manhattan Express 2,866
Baane Mini Imports Trucks and Buses 1996 Peterbilt 379 Stake Bed with Outrigger 2,851
Baane Mini Imports Classic Cars 1982 Camaro Z28 3,951
Euro+ Shopping Channel Classic Cars 1969 Corvair Monza 4,469
Euro+ Shopping Channel Trucks and Buses 1957 Chevy Pickup 4,567
Euro+ Shopping Channel Classic Cars 1998 Chrysler Plymouth Prowler 3,262
Euro+ Shopping Channel Trucks and Buses 1964 Mercedes Tour Bus 3,559
Euro+ Shopping Channel Classic Cars 1992 Ferrari 360 Spider red 3,817
Euro+ Shopping Channel Classic Cars 1970 Triumph Spitfire 4,530
Euro+ Shopping Channel Classic Cars 1970 Dodge Coronet 1,821
Euro+ Shopping Channel Classic Cars 1958 Chevy Corvette Limited Edition 1,338
Euro+ Shopping Channel Classic Cars 1992 Porsche Cayenne Turbo Silver 2,768
Euro+ Shopping Channel Trucks and Buses 1954 Greyhound Scenicruiser 1,818
Euro+ Shopping Channel Trains 1950's Chicago Surface Lines Streetcar 2,771
Euro+ Shopping Channel Trucks and Buses Diamond T620 Semi-Skirted Tanker 3,781
Euro+ Shopping Channel Trains 1962 City of Detroit Streetcar 1,706
Danish Wholesale Imports Classic Cars 1972 Alfa Romeo GTA 6,392
Danish Wholesale Imports Classic Cars 2001 Ferrari Enzo 8,435
Danish Wholesale Imports Classic Cars 1969 Ford Falcon 4,115
Danish Wholesale Imports Vintage Cars 1903 Ford Model A 3,005
Danish Wholesale Imports Trains Collectable Wooden Train 3,334
Danish Wholesale Imports Vintage Cars 1904 Buick Runabout 3,095
Danish Wholesale Imports Ships 18th century schooner 5,073
Danish Wholesale Imports Vintage Cars 1912 Ford Model T Delivery Wagon 3,232
Danish Wholesale Imports Vintage Cars 1940 Ford Delivery Sedan 3,774
Danish Wholesale Imports Ships The Schooner Bluenose 2,214
Danish Wholesale Imports Ships The Mayflower 2,512
Danish Wholesale Imports Ships The USS Constitution Ship 1,882
Danish Wholesale Imports Ships The Titanic 3,594
Danish Wholesale Imports Ships The Queen Mary 2,185
Danish Wholesale Imports Ships Pont Yacht 1,119
Rovelli Gifts Planes 1980s Black Hawk Helicopter 4,825
Rovelli Gifts Planes P-51-D Mustang 2,757
Rovelli Gifts Ships 1999 Yamaha Speed Boat 3,315
Rovelli Gifts Vintage Cars 1941 Chevrolet Special Deluxe Cabriolet 3,863
Rovelli Gifts Planes 1928 British Royal Navy Airplane 3,002
Rovelli Gifts Planes 1900s Vintage Bi-Plane 3,223
Rovelli Gifts Vintage Cars 1937 Horch 930V Limousine 1,733
Rovelli Gifts Planes Corsair F4U ( Bird Cage) 2,798
Rovelli Gifts Planes 1900s Vintage Tri-Plane 1,846
Rovelli Gifts Vintage Cars 1928 Ford Phaeton Deluxe 2,157
Rovelli Gifts Vintage Cars 1930 Buick Marquette Phaeton 1,395
Rovelli Gifts Planes American Airlines: B767-300 2,832
Rovelli Gifts Ships HMS Bounty 2,553
Rovelli Gifts Planes America West Airlines B757-200 3,390
Rovelli Gifts Planes ATA: B757-300 3,645
Rovelli Gifts Planes F/A 18 Hornet 1/72 3,344
Rovelli Gifts Planes American Airlines: MD-11S 3,376
Rovelli Gifts Planes Boeing X-32A JSF 2,098
Land of Toys Inc. Motorcycles 1969 Harley Davidson Ultimate Chopper 2,441
Land of Toys Inc. Motorcycles 1996 Moto Guzzi 1100i 4,129
Land of Toys Inc. Motorcycles 2003 Harley-Davidson Eagle Drag Bike 4,654
Land of Toys Inc. Motorcycles 2002 Suzuki XREO 2,562
Land of Toys Inc. Motorcycles 1936 Harley Davidson El Knucklehead 1,528
Land of Toys Inc. Motorcycles 1997 BMW R 1100 S 2,423
Land of Toys Inc. Motorcycles 1960 BSA Gold Star DBD34 2,779
Land of Toys Inc. Motorcycles 1997 BMW F650 ST 1,778
Cruz & Sons Co. Classic Cars 1968 Ford Mustang 5,458
Cruz & Sons Co. Classic Cars 1968 Dodge Charger 4,334
Cruz & Sons Co. Classic Cars 1970 Plymouth Hemi Cuda 2,957
Cruz & Sons Co. Classic Cars 1969 Dodge Charger 3,856
Cruz & Sons Co. Classic Cars 1948 Porsche 356-A Roadster 2,575
Cruz & Sons Co. Classic Cars 1969 Dodge Super Bee 1,902
Cruz & Sons Co. Classic Cars 1976 Ford Gran Torino 3,836
Cruz & Sons Co. Motorcycles 1957 Vespa GS150 2,272
Cruz & Sons Co. Classic Cars 1957 Corvette Convertible 6,154
Cruz & Sons Co. Motorcycles 1982 Ducati 900 Monster 2,254
Cruz & Sons Co. Classic Cars 1971 Alpine Renault 1600s 1,800
Cruz & Sons Co. Classic Cars 1956 Porsche 356A Coupe 5,280
Cruz & Sons Co. Classic Cars 1961 Chevrolet Impala 2,080
Cruz & Sons Co. Motorcycles 1982 Ducati 996 R 978
Cruz & Sons Co. Motorcycles 1974 Ducati 350 Mk3 Desmo 2,721
Cruz & Sons Co. Motorcycles 2002 Yamaha YZR M1 2,545
Motor Mint Distributors Inc. Classic Cars 1993 Mazda RX-7 3,054
Motor Mint Distributors Inc. Classic Cars 1995 Honda Civic 5,243
Motor Mint Distributors Inc. Classic Cars 1999 Indy 500 Monte Carlo SS 3,295
Motor Mint Distributors Inc. Classic Cars 1992 Ferrari 360 Spider red 7,400
Motor Mint Distributors Inc. Classic Cars 1948 Porsche Type 356 Roadster 5,910
Motor Mint Distributors Inc. Classic Cars 1982 Lamborghini Diablo 931
AV Stores, Co. Classic Cars 1965 Aston Martin DB5 4,374
AV Stores, Co. Vintage Cars 1917 Grand Touring Sedan 6,426
AV Stores, Co. Vintage Cars 1911 Ford Town Car 1,647
AV Stores, Co. Vintage Cars 1932 Model A Ford J-Coupe 3,818
AV Stores, Co. Vintage Cars 1928 Mercedes-Benz SSK 5,074
AV Stores, Co. Vintage Cars 1932 Alfa Romeo 8C2300 Spider Sport 2,293
AV Stores, Co. Classic Cars 1957 Ford Thunderbird 2,604
AV Stores, Co. Classic Cars 1970 Chevy Chevelle SS 454 2,593
AV Stores, Co. Classic Cars 1966 Shelby Cobra 427 S/C 1,255
AV Stores, Co. Vintage Cars 1939 Chevrolet Deluxe Coupe 578
AV Stores, Co. Vintage Cars 1938 Cadillac V-16 Presidential Limousine 1,590
AV Stores, Co. Classic Cars 1949 Jaguar XK 120 3,556
AV Stores, Co. Classic Cars 1952 Citroen-15CV 5,186
AV Stores, Co. Classic Cars 1969 Chevrolet Camaro Z28 2,173
AV Stores, Co. Classic Cars 2002 Chevy Corvette 3,566
AV Stores, Co. Vintage Cars 1936 Mercedes Benz 500k Roadster 1,694
Mini Wheels Co. Vintage Cars 1937 Lincoln Berline 2,882
Mini Wheels Co. Vintage Cars 1936 Mercedes-Benz 500K Special Roadster 2,329
Mini Wheels Co. Vintage Cars 1934 Ford V8 Coupe 1,487
Mini Wheels Co. Vintage Cars 18th Century Vintage Horse Carriage 4,053
Mini Wheels Co. Vintage Cars 1917 Maxwell Touring Car 3,560
Mini Wheels Co. Vintage Cars 1936 Chrysler Airflow 2,228
Volvo Model Replicas, Co Classic Cars 1952 Alpine Renault 1300 5,718
Volvo Model Replicas, Co Vintage Cars 1913 Ford Model T Speedster 1,957
Mini Gifts Distributors Ltd. Trucks and Buses 1958 Setra Bus 2,554
Mini Gifts Distributors Ltd. Trucks and Buses 1940 Ford Pickup Truck 4,974
Mini Gifts Distributors Ltd. Vintage Cars 1939 Cadillac Limousine 2,164
Mini Gifts Distributors Ltd. Trucks and Buses 1996 Peterbilt 379 Stake Bed with Outrigger 1,353
La Corne D'abondance, Co. Classic Cars 1962 LanciaA Delta 16V 3,984
La Corne D'abondance, Co. Trucks and Buses 1964 Mercedes Tour Bus 4,164
La Corne D'abondance, Co. Trucks and Buses 1926 Ford Fire Engine 2,407
La Corne D'abondance, Co. Classic Cars 1992 Ferrari 360 Spider red 8,128
La Corne D'abondance, Co. Trucks and Buses 1940s Ford truck 4,319
La Corne D'abondance, Co. Trucks and Buses 1962 Volkswagen Microbus 2,147
La Corne D'abondance, Co. Classic Cars 1958 Chevy Corvette Limited Edition 687
La Corne D'abondance, Co. Trucks and Buses 1980’s GM Manhattan Express 2,836
La Corne D'abondance, Co. Trucks and Buses 1954 Greyhound Scenicruiser 1,227
La Corne D'abondance, Co. Classic Cars 1982 Camaro Z28 3,483
Classic Legends Inc. Trucks and Buses 1957 Chevy Pickup 5,124
Classic Legends Inc. Classic Cars 1998 Chrysler Plymouth Prowler 6,477
Classic Legends Inc. Classic Cars 1970 Dodge Coronet 2,662
Classic Legends Inc. Classic Cars 1992 Porsche Cayenne Turbo Silver 4,684
Classic Legends Inc. Trucks and Buses Diamond T620 Semi-Skirted Tanker 2,719
Royale Belge Trains 1950's Chicago Surface Lines Streetcar 1,628
Dragon Souveniers, Ltd. Classic Cars 2001 Ferrari Enzo 6,446
Dragon Souveniers, Ltd. Classic Cars 1969 Corvair Monza 6,367
Dragon Souveniers, Ltd. Classic Cars 1969 Ford Falcon 6,748
Dragon Souveniers, Ltd. Vintage Cars 1903 Ford Model A 3,161
Dragon Souveniers, Ltd. Trains Collectable Wooden Train 1,715
Dragon Souveniers, Ltd. Classic Cars 1970 Triumph Spitfire 2,686
Dragon Souveniers, Ltd. Vintage Cars 1904 Buick Runabout 1,696
Dragon Souveniers, Ltd. Ships 18th century schooner 4,887
Dragon Souveniers, Ltd. Trains 1962 City of Detroit Streetcar 1,169
Dragon Souveniers, Ltd. Ships The Mayflower 2,863
Dragon Souveniers, Ltd. Ships The Queen Mary 4,022
Dragon Souveniers, Ltd. Ships Pont Yacht 2,621
Enaco Distributors Ships The Titanic 3,101
Salzburg Collectables Classic Cars 1972 Alfa Romeo GTA 5,192
Salzburg Collectables Planes 1980s Black Hawk Helicopter 6,509
Salzburg Collectables Ships 1999 Yamaha Speed Boat 1,572
Salzburg Collectables Vintage Cars 1941 Chevrolet Special Deluxe Cabriolet 2,573
Salzburg Collectables Planes 1900s Vintage Bi-Plane 2,640
Salzburg Collectables Vintage Cars 1912 Ford Model T Delivery Wagon 2,540
Salzburg Collectables Vintage Cars 1937 Horch 930V Limousine 1,262
Salzburg Collectables Vintage Cars 1940 Ford Delivery Sedan 2,876
Salzburg Collectables Planes Corsair F4U ( Bird Cage) 1,739
Salzburg Collectables Ships The Schooner Bluenose 1,434
Salzburg Collectables Ships HMS Bounty 2,153
Salzburg Collectables Ships The USS Constitution Ship 2,554
Salzburg Collectables Planes American Airlines: MD-11S 1,655
Salzburg Collectables Planes Boeing X-32A JSF 1,126
Australian Collectors, Co. Motorcycles 1996 Moto Guzzi 1100i 3,449
Australian Collectors, Co. Motorcycles 2003 Harley-Davidson Eagle Drag Bike 7,305
Australian Collectors, Co. Planes P-51-D Mustang 2,401
Australian Collectors, Co. Motorcycles 1936 Harley Davidson El Knucklehead 2,647
Australian Collectors, Co. Motorcycles 1997 BMW R 1100 S 3,866
Australian Collectors, Co. Planes 1928 British Royal Navy Airplane 3,627
Australian Collectors, Co. Motorcycles 1960 BSA Gold Star DBD34 2,460
Australian Collectors, Co. Planes 1900s Vintage Tri-Plane 2,080
Australian Collectors, Co. Motorcycles 1997 BMW F650 ST 2,088
Australian Collectors, Co. Vintage Cars 1928 Ford Phaeton Deluxe 1,995
Australian Collectors, Co. Vintage Cars 1930 Buick Marquette Phaeton 2,032
Australian Collectors, Co. Planes American Airlines: B767-300 4,293
Australian Collectors, Co. Planes America West Airlines B757-200 1,962
Australian Collectors, Co. Planes ATA: B757-300 2,563
Australian Collectors, Co. Planes F/A 18 Hornet 1/72 3,096
Reims Collectables Motorcycles 1969 Harley Davidson Ultimate Chopper 2,928
Reims Collectables Motorcycles 2002 Suzuki XREO 6,326
Reims Collectables Motorcycles 1982 Ducati 900 Monster 1,862
Reims Collectables Motorcycles 1974 Ducati 350 Mk3 Desmo 2,398
Reims Collectables Motorcycles 2002 Yamaha YZR M1 3,186
Marseille Mini Autos Classic Cars 1968 Ford Mustang 6,538
Marseille Mini Autos Classic Cars 1968 Dodge Charger 4,215
Marseille Mini Autos Classic Cars 1970 Plymouth Hemi Cuda 2,094
Marseille Mini Autos Classic Cars 1969 Dodge Charger 2,096
Marseille Mini Autos Classic Cars 1993 Mazda RX-7 3,898
Marseille Mini Autos Classic Cars 1948 Porsche 356-A Roadster 2,682
Marseille Mini Autos Classic Cars 1995 Honda Civic 3,528

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