KoolReport
  1. Home
  2. Pivot
  3. How to use Pivot Widgets Field Order

PivotTable's and PivotMatrix's Field Order

Adding order number to row and column headers 


PivotTable::create(array(
    ...
    'map' => array(
        'rowHeader' => function($rowHeader, $headerInfo) {
            $v = $rowHeader;
            if (isset($headerInfo['childOrder']))
                $v = $headerInfo['childOrder'] . ". " . $v;
            return $v;
        },
        'columnHeader' => function($colHeader, $headerInfo) {
            $v = $colHeader;
            if (isset($headerInfo['childOrder']))
                $v = $headerInfo['childOrder'] . ". " . $v;

            return $v;
        },
    ),
    ...
));



PivotMatrix::create(array(
    ...
    'map' => array(
        'rowHeader' => function($rowHeader, $headerInfo) {
            $v = $rowHeader;
            if (isset($headerInfo['childOrder']))
                $v = $headerInfo['childOrder'] . ". " . $v;
            return $v;
        },
        'columnHeader' => function($colHeader, $headerInfo) {
            $v = $colHeader;
            if (isset($headerInfo['childOrder']))
                $v = $headerInfo['childOrder'] . ". " . $v;

            return $v;
        },
    ),
    ...
));
 
  dollar_sales - sum   dollar_sales - count   dollar_sales - avg  
  orderYear   orderQuarter  
  customerName   productLine   productName  
1. Year-2004
2. Year-2005
Total
Total
Total
1. AV Stores, Co.
Total
2. Amica Models & Co.
Total
3. Anna's Decorations, Ltd
Total
4. Alpha Cognac
Total
5. Atelier graphique
Total
Total
$99,984
$35
$2,857
-
-
-
$99,984
$35
$2,857
$82,223
$26
$3,162
-
-
-
$82,223
$26
$3,162
-
-
-
$56,932
$19
$2,996
$56,932
$19
$2,996
-
-
-
$12,432
$5
$2,486
$12,432
$5
$2,486
$7,743
$3
$2,581
-
-
-
$7,743
$3
$2,581
$189,950
$64
$2,968
$69,365
$24
$2,890
$259,315
$88
$2,947
Page size:

This example shows how to customize the row header and column header of Pivot Table and PivotMatrix widget.

Under the "map" property, you add "rowHeader" and "columnHeader" sub properties. Those properties are defined by a function that will receive row or column header text as parameters. You can add custom processes to transform the row and column headers to desired one and return it.

As you can see in above example, the row and column header of pivot are prefixed with order: "AV Stores, Co." becomes "1. AV Stores, Co.".

<?php
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();

<?php
//Step 1: Load KoolReport
require_once "../../../load.koolreport.php";
use \koolreport\pivot\processes\Pivot;
use \koolreport\pivot\processes\PivotExtract;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Filter;

//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources" => array(
                "dollarsales"=>array(
                    'filePath' => '../../../databases/customer_product_dollarsales2.csv',
                    'fieldSeparator' => ';',
                    'class' => "\koolreport\datasources\CSVDataSource"      
                ), 
            )
        );
    }

    public function setup()
    {
        $node = $this->src('dollarsales');

        $node->pipe(new Filter(array(
            array('customerName', '<', 'Au'),
            array('orderYear', '>', 2003),
        )))
        ->pipe(new ColumnMeta(array(
            "dollar_sales" => array(
                'type' => 'number',
                "prefix" => "$",
            ),
        )))
        ->saveTo($node2);

        $node2->pipe(new Pivot(array(
            "dimensions" => array(
                // "column" => "orderYear, orderQuarter, orderMonth, orderDay",
                "column" => "orderYear, orderQuarter",
                "row" => "customerName, productLine, productName",
                // "row" => "customerName, productLine",
            ),
            "aggregates" => array(
                "sum" => "dollar_sales",
                "count" => "dollar_sales",
                "avg" => "dollar_sales",
                'sum percent' => 'dollar_sales',
                'count percent' => 'dollar_sales',
            ),
        )))->pipe($this->dataStore('salesTable'));

        $node2->pipe(new Pivot(array(
            "dimensions" => array(
                // "column" => "orderYear, orderQuarter, orderMonth, orderDay",
                "column" => "orderYear, orderQuarter",
                "row" => "customerName, productLine, productName",
                // "row" => "customerName, productLine",
            ),
            "aggregates" => array(
                "sum" => "dollar_sales",
                "count" => "dollar_sales",
                "avg" => "dollar_sales",
                'sum percent' => 'dollar_sales',
                'count percent' => 'dollar_sales',
            ),
            'partialProcessing' => true,
        )))->pipe($this->dataStore('salesMatrix'));
    }
}
<?php
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\pivot\widgets\PivotMatrix;
?>

<div class="report-content">
    <div class="text-center">
        <h1>PivotTable's and PivotMatrix's Field Order</h1>
        <p class="lead">
            Adding order number to row and column headers
        </p>
    </div>

    <pre style="font-weight:bold"><code>
PivotTable::create(array(
    ...
    'map' => array(
        'rowHeader' => function($rowHeader, $headerInfo) {
            $v = $rowHeader;
            if (isset($headerInfo['childOrder']))
                $v = $headerInfo['childOrder'] . ". " . $v;
            return $v;
        },
        'columnHeader' => function($colHeader, $headerInfo) {
            $v = $colHeader;
            if (isset($headerInfo['childOrder']))
                $v = $headerInfo['childOrder'] . ". " . $v;

            return $v;
        },
    ),
    ...
));</code></pre>
    <i class="fa fa-arrow-down" style="font-size:24px;"></i>
    <div style="margin-top:20px;">
    
     <?php
        $dataStore = $this->dataStore('salesTable');
        PivotTable::create(array(
            "name" => "PivotTable2",
            "dataStore" => $dataStore,
            "rowDimension" => "row",
            "columnDimension" => "column",
            "measures"=>array(
                "dollar_sales - sum",
                'dollar_sales - count',
                'dollar_sales - avg',
            ),
            'rowSort' => array(
                'dollar_sales - sum' => 'desc',
            ),
            'columnSort' => array(
                'orderMonth' => function ($a, $b) {
                    return (int) $a < (int) $b;
                },
            ),
            'rowCollapseLevels' => array(0, 1),
            'columnCollapseLevels' => array(0),
            'width' => '100%',
            'map' => array(
                'rowHeader' => function($rowHeader, $headerInfo) {
                    $v = $rowHeader;
                    if (isset($headerInfo['childOrder']))
                        $v = $headerInfo['childOrder'] . ". " . $v;
                    return $v;
                },
                'columnHeader' => function($colHeader, $headerInfo) {
                    $v = $colHeader;
                    if ($headerInfo['fieldName'] === 'orderYear')
                        $v = 'Year-' . $v;
                    else if ($headerInfo['fieldName'] === 'orderQuarter')
                        $v = 'Quarter-' . $v;
                    if (isset($headerInfo['childOrder']))
                        $v = $headerInfo['childOrder'] . ". " . $v;
                    return $v;
                },
            ),
            // 'showDataHeaders' => true,
        ));
    ?>
    </div>

    <br><br>
    <pre style="font-weight:bold"><code>
PivotMatrix::create(array(
    ...
    'map' => array(
        'rowHeader' => function($rowHeader, $headerInfo) {
            $v = $rowHeader;
            if (isset($headerInfo['childOrder']))
                $v = $headerInfo['childOrder'] . ". " . $v;
            return $v;
        },
        'columnHeader' => function($colHeader, $headerInfo) {
            $v = $colHeader;
            if (isset($headerInfo['childOrder']))
                $v = $headerInfo['childOrder'] . ". " . $v;

            return $v;
        },
    ),
    ...
));</code></pre>
    <i class="fa fa-arrow-down" style="font-size:24px;"></i>
    <div style="margin-top:20px;">
    
    <?php
        $dataStore = $this->dataStore('salesMatrix');
        PivotMatrix::create(array(
            "name" => "PivotMatrix2",
            "dataStore" => $dataStore,
            "rowDimension" => "row",
            "columnDimension" => "column",
            "measures"=>array(
                "dollar_sales - sum",
                'dollar_sales - count',
                'dollar_sales - avg',
            ),
            'rowSort' => array(
                'dollar_sales - sum' => 'desc',
            ),
            'columnSort' => array(
                'orderMonth' => function ($a, $b) {
                    return (int) $a < (int) $b;
                },
            ),
            'width' => '100%',
            'paging' => array(
                'size' => 20
            ),
            'map' => array(
                'rowHeader' => function($rowHeader, $headerInfo) {
                    $v = $rowHeader;
                    if (isset($headerInfo['childOrder']))
                        $v = $headerInfo['childOrder'] . ". " . $v;
                    return $v;
                },
                'columnHeader' => function($colHeader, $headerInfo) {
                    $v = $colHeader;
                    if ($headerInfo['fieldName'] === 'orderYear')
                        $v = 'Year-' . $v;
                    else if ($headerInfo['fieldName'] === 'orderQuarter')
                        $v = 'Quarter-' . $v;
                    if (isset($headerInfo['childOrder']))
                        $v = $headerInfo['childOrder'] . ". " . $v;
                    return $v;
                },
            ),
            // 'showDataHeaders' => true,
        ));
    ?>
    </div>

</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