Pivot data produced by PivotSQL process

PivotSQL works with large SQL datasets

  productLine   productName   orderMonth   dollar_sales - count  
  dollar_sales - sum  
  orderYear  
  customerName  
2003
2004
2005
Total
AV Stores, Co.
Alpha Cognac
Amica Models & Co.
Anna's Decorations, Ltd
Atelier graphique
Australian Collectables, Ltd
Australian Collectors, Co.
Australian Gift Network, Co
Auto Associés & Cie.
Auto Canal+ Petit
Auto-Moto Classics Inc.
Baane Mini Imports
Bavarian Collectables Imports, Co.
Blauer See Auto, Co.
Boards & Toys Co.
CAF Imports
Cambridge Collectables Co.
Canadian Gift Exchange Network
Classic Gift Ideas, Inc
Classic Legends Inc.
Clover Collections, Co.
Collectable Mini Designs Co.
Collectables For Less Inc.
Corporate Gift Ideas Co.
Corrida Auto Replicas, Ltd
Cruz & Sons Co.
Daedalus Designs Imports
Danish Wholesale Imports
Diecast Classics Inc.
Diecast Collectables
Double Decker Gift Stores, Ltd
Down Under Souveniers, Inc
Dragon Souveniers, Ltd.
Enaco Distributors
Euro+ Shopping Channel
Extreme Desk Decorations, Ltd
Frau da Collezione
FunGiftIdeas.com
Gift Depot Inc.
Gift Ideas Corp.
Gifts4AllAges.com
GiftsForHim.com
Handji Gifts& Co
Heintze Collectables
Herkku Gifts
Iberia Gift Imports, Corp.
Kelly's Gift Shop
King Kong Collectables, Co.
L'ordine Souveniers
La Corne D'abondance, Co.
La Rochelle Gifts
Land of Toys Inc.
Lyon Souveniers
Marseille Mini Autos
Marta's Replicas Co.
Men 'R' US Retailers, Ltd.
Microscale Inc.
Mini Auto Werke
Mini Caravy
Mini Classics
Mini Creations Ltd.
Mini Gifts Distributors Ltd.
Mini Wheels Co.
Motor Mint Distributors Inc.
Muscle Machine Inc
Norway Gifts By Mail, Co.
Online Diecast Creations Co.
Online Mini Collectables
Osaka Souveniers Co.
Oulu Toy Supplies, Inc.
Petit Auto
Québec Home Shopping Network
Reims Collectables
Rovelli Gifts
Royal Canadian Collectables, Ltd.
Royale Belge
Salzburg Collectables
Saveley & Henriot, Co.
Scandinavian Gift Ideas
Signal Collectibles Ltd.
Signal Gift Stores
Souveniers And Things Co.
Stylish Desk Decors, Co.
Suominen Souveniers
Super Scale Inc.
Technics Stores Inc.
Tekni Collectables Inc.
The Sharp Gifts Warehouse
Tokyo Collectables, Ltd
Toms Spezialitäten, Ltd
Toys of Finland, Co.
Toys4GrownUps.com
UK Collectables, Ltd.
Vida Sport, Ltd
Vitachrome Inc.
Volvo Model Replicas, Co
West Coast Collectables Co.
giftsbymail.co.uk
Total
$48,426
$99,984
-
$148,410
$48,051
-
$12,432
$60,483
-
$82,223
-
$82,223
$80,102
-
$56,932
$137,034
$14,571
$7,743
-
$22,314
$35,506
$9,415
$10,945
$55,866
$53,429
$127,156
-
$180,585
$33,758
-
$21,432
$55,190
-
$58,876
-
$58,876
-
$74,246
$12,191
$86,437
$6,037
$15,517
-
$21,554
$51,710
$52,514
-
$104,225
-
$31,310
-
$31,310
$34,651
$41,287
-
$75,938
$4,466
-
$3,453
$7,919
$31,428
$15,323
-
$46,751
-
$32,199
-
$32,199
$36,528
$33,595
-
$70,122
$34,606
$23,333
-
$57,939
$43,708
$25,506
-
$69,214
-
$49,898
-
$49,898
-
$80,375
-
$80,375
$32,680
$12,531
$28,323
$73,534
$85,559
-
$46,782
$132,341
$57,132
$55,308
-
$112,440
$71,645
$15,823
-
$87,468
$42,784
$18,998
-
$61,782
$53,959
$53,487
$21,639
$129,085
-
$98,509
$5,849
$104,359
$59,265
$6,277
-
$65,542
$22,276
$7,310
-
$29,586
$2,880
$76,722
$75,020
$154,622
$150,123
$2,612
$3,516
$156,251
$43,575
$24,945
-
$68,520
$189,840
$340,831
$290,019
$820,690
-
$66,705
$23,627
$90,332
$17,746
$7,612
-
$25,358
$9,978
$48,356
$30,294
$88,627
$24,879
$42,045
$28,501
$95,425
$23,664
$26,304
-
$49,968
-
$33,533
$50,807
$84,340
$20,220
$37,769
$36,442
$94,432
-
$105,421
$2,326
$107,747
$36,164
$53,745
-
$89,910
$85,024
$12,538
-
$97,562
$47,513
$3,475
-
$50,988
$55,041
$52,166
$30,254
$137,461
$45,481
-
-
$45,481
$38,524
$51,619
$35,362
$125,506
$33,383
$53,170
-
$86,554
-
$67,426
$91,147
$158,573
$22,293
$126,793
-
$149,085
$23,419
$26,312
$17,928
$67,659
$50,825
$18,888
$1,835
$71,548
-
$90,545
-
$90,545
$20,453
$21,054
-
$41,506
-
$29,230
-
$29,230
$42,253
-
$8,807
$51,060
-
$40,702
$35,158
$75,859
$36,092
$42,340
-
$78,432
$93,565
-
$8,307
$101,873
$167,783
$231,563
$192,482
$591,827
$40,462
$26,249
-
$66,711
$25,833
$51,893
-
$77,727
$117,635
$60,279
-
$177,914
$36,799
$32,260
-
$69,059
$65,650
$50,800
-
$116,449
-
$55,577
-
$55,577
-
$62,361
-
$62,361
$32,723
$16,213
$46,771
$95,706
-
$62,254
$8,598
$70,852
$16,910
$22,997
$29,070
$68,978
$30,621
$49,706
$46,657
$126,983
$89,876
$37,654
-
$127,530
-
$66,812
-
$66,812
$2,756
$12,082
$14,380
$29,217
$35,826
$49,234
$52,420
$137,480
$39,712
$90,593
-
$130,305
$44,167
$48,810
$27,967
$120,944
$29,997
$12,573
-
$42,570
$32,642
$47,539
-
$80,181
$24,014
$67,642
$42,252
$133,907
$66,885
$13,672
-
$80,557
$29,717
$28,395
$45,785
$103,897
$38,139
$32,239
-
$70,379
$89,419
$2,434
$12,692
$104,545
-
$38,282
$43,525
$81,807
-
$59,551
$83,985
$143,536
-
$70,966
$34,583
$105,549
$27,988
$61,235
-
$89,223
$37,602
$34,341
$23,603
$95,546
$54,223
-
$39,581
$93,803
$23,908
$45,444
$37,259
$106,611
-
$108,778
-
$108,778
$5,495
$67,003
-
$72,498
$7,675
$59,020
-
$66,695
$7,678
$36,070
-
$43,749
-
$71,784
-
$71,784
$3,317,348
$4,515,906
$1,770,937
$9,604,191
Page size:

PivotSQL is a process to prroduce pivot data from large SQL datasets, which is then visualized with PivotMatrix widget:

->pipe(new \koolreport\pivot\processes\PivotSQL([
    "column" => "orderYear",
    "row" => "customerName",
    "aggregates"=>array(
        "sum"=>"dollar_sales",
    ),
]))

In order to use PivotSQL users need to turn on PHP's session at the beginning of the page:

session_start();
<?php
@session_start();
require_once "CustomersYears.php";
$CustomersYears = new CustomersYears;
$CustomersYears->run()->render();
?>    
<?php
require_once "../../../load.koolreport.php";
use \koolreport\processes\ColumnMeta;

class CustomersYears extends koolreport\KoolReport
{
    function settings()
    {
        $config = include "../../../config.php";

        return array(
            "dataSources"=>array(
                "automaker"=>$config["automaker"]
            )
        );
    }
    function setup()
    {
        $this->src('automaker')
        ->query('select * from customer_product_dollarsales2')
        ->pipe(new \koolreport\pivot\processes\PivotSQL([
            "column" => "orderYear",
            "row" => "customerName",
            "aggregates"=>array(
                "sum"=>"dollar_sales",
            ),
        ]))
        ->pipe(new ColumnMeta(array(
            "dollar_sales - sum"=>array(
                'type' => 'number',
                "prefix" => "$",
            ),
        )))
        ->pipe($this->dataStore('pivotData'));

    }
}
<?php
        use \koolreport\pivot\widgets\PivotMatrix;
?>

<div class="report-content">  
    <div class="text-center">
        <h1>Pivot data produced by PivotSQL process</h1>
        <p class="lead">PivotSQL works with large SQL datasets
        </p>
    </div>
    
    <form id='form1' class="form-inline" method="post">

        <?php
        PivotMatrix::create(array(
            "id" => "pivotMatrix1",
            'dataSource' => $this->dataStore('pivotData'),
            'waitingFields' => array(
                "productLine" => "label",
                "productName" => "label",
                "orderMonth" => "label",
                "dollar_sales - count" => "data",
            ),
            'paging' => array(
                'size' => 10,
                'maxDisplayedPages' => 5,
                'sizeSelect' => array(5, 10, 20, 50, 100)
            )
        ));
        ?>
    </form>
</div>
customerNameproductNameproductLineorderDateorderDayorderMonthorderYearorderQuarterdollar_sales
Vitachrome Inc. 1937 Lincoln Berline Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 3726.45
Vitachrome Inc. 1936 Mercedes-Benz 500K Special Roadster Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 1768.33
Baane Mini Imports 1952 Alpine Renault 1300 Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5571.8
Baane Mini Imports 1962 LanciaA Delta 16V Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5026.14
Baane Mini Imports 1958 Setra Bus Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3284.28
Baane Mini Imports 1940 Ford Pickup Truck Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3307.5
Baane Mini Imports 1926 Ford Fire Engine Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 1283.48
Baane Mini Imports 1913 Ford Model T Speedster Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2489.13
Baane Mini Imports 1934 Ford V8 Coupe Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2164.4
Baane Mini Imports 18th Century Vintage Horse Carriage Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2173

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