PDF Extract

The example shows how we can extract a table from multi-level tables of pivot.
The extracted table can be used to draw chart or any other purposes.

Original Pivot Table

Extracted Table 1

customerName20042005{{all}}
AV Stores, Co. 0 0 0
Alpha Cognac 0 0 0
{{all}} 0 0 0

Extracted Table 2

productLine10 | dollar_sales - sum10 | dollar_sales - count11 | dollar_sales - sum11 | dollar_sales - count{{all}} | dollar_sales - sum{{all}} | dollar_sales - count
Vintage Cars 0 0 0 0 0 0
Classic Cars 0 0 0 0 0 0
Ships 0 0 0 0 0 0
Trains 0 0 0 0 0 0
{{all}} 0 0 0 0 0 0

This example show you how to extract part of information from pivot table. Due to hierarchical structure of pivot tables, the data of pivot table is complex and it is not easy to get.

The PivotExtract process can help you to get data from pivot table. The resulted data is in normal tabular format so you can use them to display on table or draw chart.

More information of PivotExtract and its documentation you may find here.

<?php
require_once "SalesPivotExtract.php";
$salesPivotExtract = new SalesPivotExtract;
$salesPivotExtract->run()->render();
?>    
<?php
require_once "SalesPivotExtract.php";
$report = new SalesPivotExtract();

$report->run()
->export("SalesPivotExtractPdf")
->pdf(
  array(
    // "format"=>"A4",
    // "orientation"=>"landscape",
    "width" => '29cm',
    "height" => '21cm',
    "margin" => '2cm'
  )
)
->toBrowser("SalesPivotExtract.pdf");
<?php
require_once "SalesPivotExtract.php";
$report = new SalesPivotExtract();

$report->run()
->exportToExcel(array(
  "dataStores" => array(
    'sales' => array(
      'rowSort' => array(
        'dollar_sales - count' => 'desc',
      ),
      'headerMap' => function($v, $f) {
        if ($v === 'dollar_sales - sum')
          $v = 'Sales (in USD)';
        if ($v === 'dollar_sales - count')
          $v = 'Number of Sales';
        if ($f === 'orderYear')
          $v = 'Year ' . $v;
        return $v;
      },
    )
  ),
))
->toBrowser("SalesPivotExtract.xlsx");
<?php
// error_reporting(E_ALL);
require_once "../../../../koolreport/autoload.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\ValueMap;
use \koolreport\pivot\processes\Pivot;
use \koolreport\pivot\PivotExcelExport;
use \koolreport\pivot\processes\PivotExtract;

class SalesPivotExtract extends koolreport\KoolReport
{
    use \koolreport\excel\ExcelExportable;
    use \koolreport\export\Exportable;
  
    function settings()
    {
        return array(
            "dataSources" => array(
                "dollarsales"=>array(
                    'filePath' => '../../../databases/customer_product_dollarsales2.csv',
                    'fieldSeparator' => ';',
                    'class' => "\koolreport\datasources\CSVDataSource"      
                ), 
            )
        );
    }
    function setup()
    {
        $node = $this->src('dollarsales');
        $node->pipe(new Filter(array(
            array('customerName', '<', 'Am'),
            array('orderYear', '>', 2003)
        )))
        ->pipe(new ColumnMeta(array(
            "dollar_sales"=>array(
                'type' => 'number',
                "prefix" => "$",
            ),
        )))
        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "column"=>"orderYear, orderMonth",
                "row"=>"customerName, productLine, productName"
            ),
            "aggregates"=>array(
                "sum"=>"dollar_sales",
                "count"=>"dollar_sales"
            )
        )))->saveTo($node2);
        $node2->pipe($this->dataStore('sales')); 

        $node2->pipe(new PivotExtract(array(
            "row" => array(
                "parent" => array(),
            ),
            "column" => array(
                "parent" => array(
                ),
            ),
            "measures"=>array(
                "dollar_sales - sum", 
            ),
        )))
        ->pipe($this->dataStore('salesTable1'));

        $node2->pipe(new PivotExtract(array(
            "row" => array(
                "parent" => array(
                    "customerName" => "AV Stores, Co."
                ),
                "sort" => array(
                    'dollar_sales - sum' => 'desc',
                ),
            ),
            "column" => array(
                "parent" => array(
                    "orderYear" => "2004"
                ),
                "sort" => array(
                    'orderMonth' => function($a, $b) {
                        return (int)$a < (int)$b;
                    },
                ),
            ),
            "measures"=>array(
                "dollar_sales - sum", 
                "dollar_sales - count", 
            ),
        )))
        ->pipe($this->dataStore('salesTable2'));
    }
}
<?php
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\pivot\widgets\PivotTable;
?>
<div class="report-content">
  
  <div class="text-center">
    <h1>PDF Extract</h1>
    <p class="lead">
      The example shows how we can extract a table from multi-level tables of pivot.
      <br/>
      The extracted table can be used to draw chart or any other purposes.
    </p>
    <div class="form-group">
      <a href="export.php" class="btn btn-primary">Download PDF</a>
      <a href="exportExcel.php" class="btn btn-primary">Download Excel</a>
    </div>
  </div>

    <h3>Original Pivot Table</h3>  
    <?php 
        PivotTable::create(array(
            "dataStore"=>$this->dataStore('sales'),
            'rowCollapseLevels' => array(0),
            'columnCollapseLevels' => array(0),
        ));
    ?>

    <h3>Extracted Table 1</h3>
    <?php
        Table::create(array(
            "dataStore"=>$this->dataStore('salesTable1'),
        ));
    ?>

    <h3>Extracted Table 2</h3>
    <?php
        Table::create(array(
            "dataStore"=>$this->dataStore('salesTable2'),
        ));
    ?>
  
</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.3300000000002
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