KoolReport
  1. Home
  2. Pivot
  3. How to set Pivot Widgets Mapping

PivotTable's and PivotMatrix's Map

Mapping fields, headers and data cells 


PivotTable::create(array(
    ...
    'map' => array(
        'dataField' => function($dataField, $fieldInfo) {
            $v = $dataField;
            if ($v === 'dollar_sales - sum')
                $v = 'Sales (in USD)';
            else if ($v === 'dollar_sales - count')
                $v = 'Number of Sales';
            else if ($v === 'dollar_sales - avg')
                $v = 'Sales Average';
            return $v;
        },
    ),
    ...
));
Sales (in USD) | Number of Sales | Sales Average
2004
2005
Total
AV Stores, Co.
$99,984
$35
$2,857
-
-
-
$99,984
$35
$2,857
Amica Models & Co.
$82,223
$26
$3,162
-
-
-
$82,223
$26
$3,162
Anna's Decorations, Ltd
-
-
-
$56,932
$19
$2,996
$56,932
$19
$2,996
Alpha Cognac
-
-
-
$12,432
$5
$2,486
$12,432
$5
$2,486
Atelier graphique
$7,743
$3
$2,581
-
-
-
$7,743
$3
$2,581
Total
$189,950
$64
$2,968
$69,365
$24
$2,890
$259,315
$88
$2,947 



PivotTable::create(array(
    ...
    'map' => array(
        'dataFieldZone' => function($dataFields) {
            return implode("__", $dataFields);
        },
    ),
    ...
));
dollar_sales - sum - dollar_sales - count - dollar_sales - avg
2004
2005
Total
AV Stores, Co.
$99,984
$35
$2,857
-
-
-
$99,984
$35
$2,857
Amica Models & Co.
$82,223
$26
$3,162
-
-
-
$82,223
$26
$3,162
Anna's Decorations, Ltd
-
-
-
$56,932
$19
$2,996
$56,932
$19
$2,996
Alpha Cognac
-
-
-
$12,432
$5
$2,486
$12,432
$5
$2,486
Atelier graphique
$7,743
$3
$2,581
-
-
-
$7,743
$3
$2,581
Total
$189,950
$64
$2,968
$69,365
$24
$2,890
$259,315
$88
$2,947 



PivotMatrix::create(array(
    ...
    'map' => array(
        'rowField' => function($rowField, $fieldInfo) {
            $v = $dataField;
            if ($v === 'customerName')
                $v = 'Customer';
            else if ($v === 'productLine')
                $v = 'Category';
            else if ($v === 'productName')
                $v = 'Product';
            return $v;
        },
        'columnField' => function($colField, $fieldInfo) {
            $v = $dataField;
            if ($v === 'orderYear')
                $v = 'Year';
            else if ($v === 'orderMonth')
                $v = 'Month';
            return $v;
        },
    ),
    ...
));
 
  dollar_sales - sum  
  Year   orderQuarter  
  Customer   Category   Product  
2004
2005
Total
AV Stores, Co.
Amica Models & Co.
Anna's Decorations, Ltd
Alpha Cognac
Atelier graphique
Total
$99,984
-
$99,984
$82,223
-
$82,223
-
$56,932
$56,932
-
$12,432
$12,432
$7,743
-
$7,743
$189,950
$69,365
$259,315
Page size:

This example shows you how to customize the field name so that Pivot displaying a nicer column, row or data headers to your users.

Under the "map" properties, you defined a custom function for "dataField". The function will receive name of datafield. Base on that name, you may return a new name for it. For example: "dollar_sales - sum" will be transformed to "Sales (in USD)".

<?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 Map</h1>
        <p class="lead">
            Mapping fields, headers and data cells
        </p>
    </div>

    <pre style="font-weight:bold"><code>
PivotTable::create(array(
    ...
    'map' => array(
        'dataField' => function($dataField, $fieldInfo) {
            $v = $dataField;
            if ($v === 'dollar_sales - sum')
                $v = 'Sales (in USD)';
            else if ($v === 'dollar_sales - count')
                $v = 'Number of Sales';
            else if ($v === 'dollar_sales - avg')
                $v = 'Sales Average';
            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" => "PivotTable",
            "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(
                'dataField' => function($dataField, $fieldInfo) {
                    $v = $dataField;
                    if ($v === 'dollar_sales - sum')
                        $v = 'Sales (in USD)';
                    else if ($v === 'dollar_sales - count')
                        $v = 'Number of Sales';
                    else if ($v === 'dollar_sales - avg')
                        $v = 'Sales Average';
                    return $v;
                },
            ),
            // 'showDataHeaders' => true,
        ));
    ?>
    </div>
    <br><br>


    <pre style="font-weight:bold"><code>
PivotTable::create(array(
    ...
    'map' => array(
        'dataFieldZone' => function($dataFields) {
            return implode("__", $dataFields);
        },
    ),
    ...
));</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(
                'dataFieldZone' => function($dataFields) {
                    return implode(" - ", $dataFields);
                },
            ),
        ));
    ?>
    </div>
    <br><br>

    <pre style="font-weight:bold"><code>
PivotMatrix::create(array(
    ...
    'map' => array(
        'rowField' => function($rowField, $fieldInfo) {
            $v = $dataField;
            if ($v === 'customerName')
                $v = 'Customer';
            else if ($v === 'productLine')
                $v = 'Category';
            else if ($v === 'productName')
                $v = 'Product';
            return $v;
        },
        'columnField' => function($colField, $fieldInfo) {
            $v = $dataField;
            if ($v === 'orderYear')
                $v = 'Year';
            else if ($v === 'orderMonth')
                $v = 'Month';
            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" => "PivotMatrix",
            "dataStore" => $dataStore,
            "rowDimension" => "row",
            "columnDimension" => "column",
            "measures"=>array(
                "dollar_sales - sum",
            ),
            '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(
                'rowField' => function($rowField, $fieldInfo) {
                    $v = $rowField;
                    if ($v === 'customerName')
                        $v = 'Customer';
                    else if ($v === 'productLine')
                        $v = 'Category';
                    else if ($v === 'productName')
                        $v = 'Product';
                    return $v;
                },
                'columnField' => function($colField, $fieldInfo) {
                    $v = $colField;
                    if ($v === 'orderYear')
                        $v = 'Year';
                    else if ($v === 'orderMonth')
                        $v = 'Month';
                    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