PivotTable and PivotMatrix

Properties #

Templates #

A new template named Bun is added to both PivotTable and PivotMatrix since version 5.0.0. It is similar to Excel's PivotTable format which help reduce horizontal space when expanding multiple row fields.

<?php
PivotTable::create(array(
    ...
    'template' => 'PivotTable-Bun',
    ...
));

PivotMatrix::create(array(
    ...
    'template' => 'PivotMatrix-Bun',
    ...
));

Measures #

By default, a pivot table/matrix widget shows all summarized data available in a dataStore. If you only want to show some of them, you could specify those in the measures property:

<?php
PivotTable::create(array(
    "dataStore"=>$this->dataStore('salesReport'),
    'measures'=>array(
        'dollar_sales - sum'
        'order_id - count'
    ),
));

PivotMatrix::create(array(
    "dataStore"=>$this->dataStore('salesReport'),
    'measures'=>array(
        'dollar_sales - sum'
        'order_id - count'
    ),
));

An aggregated field's name is formatted as ' - '.

Sort #

A pivot table/matrix could be sorted simultaneously in each of its dimension (e.g column and row). In each dimensional sort, you could specify either label fields or a summarized data field. Sorting order is either ascending, descending or a custom function comparing two values.

<?php
PivotTable::create(array(
  ...
    'rowSort' => array(
        'orderMonth' => function($a, $b) {
            return (int)$a < (int)$b;
        }, 
        'orderDay' => 'asc'
    ),
    'columnSort' => array(
        'dollar_sales - sum' => 'desc',
        'orderYear' => function($a, $b) {
            return (int)$a < (int)$b;
        }, 
    ),
  ...
));

PivotMatrix::create(array(
    ...
    'rowSort' => array(
        'orderMonth' => function($a, $b) {
            return (int)$a < (int)$b;
        }, 
        'orderDay' => 'asc'
    ),
    'columnSort' => array(
        'dollar_sales - sum' => 'desc',
        'orderYear' => function($a, $b) {
            return (int)$a < (int)$b;
        }, 
    ),
    ...
));

headerMap and dataMap #

Mapping allows you an option to change displaying the data in dataStore. There are header map for headers/labels and data map for summarized data. The map could be either an array or a custom function with a value and its belonging field arguments.

<?php
PivotTable::create(array(
    ...
    'headerMap' => array(
        'dollar_sales - sum' => 'Sales (in USD)',
        'dollar_sales - count' => 'Number of Sales',
    ),
    '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;
    },
    'dataMap' => function($v, $f) {return $v;},
    ...
));

map (version >= 5.0.0) #

This new map options include a second parameter containing various information about the mapped fields, headers and data cells.

<?php
PivotMatrix::create(array(
    ...
    'map' => array(
        'rowField' => function($rowField, $fieldInfo) {
            return $rowField;
        },
        'columnField' => function($colField, $fieldInfo) {
            return $colField;
        },
        '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';
            return $v;
        },
        'waitingField' => function($waitingField, $fieldInfo) {
            return $waitingField;
        },
        '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;
        },
        'dataCell' => function($value, $cellInfo) {
            $rfOrder = $cellInfo['row']['fieldOrder'];
            $cfOrder = $cellInfo['column']['fieldOrder'];
            $df = $cellInfo['fieldName'];
            $dfOrder = $cellInfo['fieldOrder'];
            return "$rfOrder:$cfOrder:$df. $value";
            
            return $cellInfo['formattedValue'];
        },
    ),
    ...
));

Example values of the information parameters:

    $fieldInfo = ["fieldOrder => 0];
    $headerInfo = [
        "childOrder"=> "2",
        "numChildren"=> 1,
        "numLeaf"=> 1,
        "fieldName"=> "customerName",
        "fieldOrder"=> 0
    ];
    $cellInfo = [
        "row"=> {
            "customerName"=> {
                "childOrder"=> "5",
                "numChildren"=> 1,
                "numLeaf"=> 1
            },
            "productLine"=> {
                "total"=> true,
                "numChildren"=> 1,
                "numLeaf"=> 1,
                "level"=> 2
            },
            "productName"=> {
                "total"=> true
            },
            "hasTotal"=> true,
            "fieldOrder"=> 0
        },
        "column"=> {
            "orderYear"=> {
                "childOrder"=> "4",
                "total"=> true,
                "numChildren"=> 2,
                "numLeaf"=> 2,
                "level"=> 4
            },
            "orderQuarter"=> {
                "total"=> true
            },
            "orderMonth"=> {
                "total"=> true
            },
            "orderDay"=> {
                "total"=> true
            },
            "hasTotal"=> true,
            "fieldOrder"=> -1
        },
        "fieldName"=> "dollar_sales - sum",
        "fieldOrder"=> 0,
        "formattedValue"=> "$82,223"
    ];

cssClass (version >= 6.0.0) #

This property is similar to map but instead of mapping content, it adds css classes to PivotTable's and PivotMatrix's elements.

<?php
PivotMatrix::create(array(
    ...
    'cssClass' => array(
        'waitingField' => function($field, $fieldInfo) {
            return 'wf-' . $field;
        },
        'dataField' => function($field, $fieldInfo) {
            return 'df-' . $field;
        },
        'columnField' => function($field, $fieldInfo) {
            return 'cf-' . $field;
        },
        'rowField' => function($field, $fieldInfo) {
            return 'rf-' . $field;
        },
        'columnHeader' => function($header, $headerInfo) {
            return 'ch-' . $header;
        },
        'rowHeader' => function($header, $headerInfo) {
            return 'rh-' . $header;
        },
        'dataCell' => function($value, $cellInfo) {
            return 'dc-' . $value;
        },
    ),
    ...
));

Collapse level #

If you have a large pivot table and don't want it to fully expand at initial loading you could set up its initial collapse levels for each dimension.

<?php
PivotTable::create(array(
    ...
    'rowCollapseLevels' => array(0),
    'columnCollapseLevels' => array(0, 1, 2),
    ...
));

Total name #

This property helps you change the label of the "total" rows and columns. By default it's "Total".

<?php
PivotTable::create(array(
    ...
    'totalName' => 'All',
    ...
));

Hide total/subtotal column/row (version >=4.2.0) #

These properties allows you to hide the grand total/sub total columns/rows:

<?php
PivotTable::create(array(
    ...
    'hideTotalRow' => true,
    'hideTotalColumn' => true,
    'hideSubtotalRow' => true,
    'hideSubtotalColumn' => true,
    ...
));

Show data field headers (version >= 5.0.0) #

By default, data field headers are turned off. Now you could show them above the data zone part for clearer information.

<?php
PivotTable::create(array(
    ...
    'showDataHeaders' => true,
    ...
));

Width #

This property let us defined the width css of the pivot table widget. Default value is 'auto'.

<?php
PivotTable::create(array(
    ...
        'width' => '100%',
    ...
));

height #

This property let us defined the width css of the pivot table widget. Default value is 'auto'.

<?php
PivotTable::create(array(
    ...
        'height' => '500px',
    ...
));

PivotMatrix exclusive properties #

The following properties only apply for the PivotMatrix widget

paging #

<?php
PivotMatrix::create(array(
  ...
    'paging' => array(
        'size' => 5,
        'maxDisplayedPages' => 5,
        'sizeSelect' => array(5, 10, 20, 50, 100)
    )
  ...
));

paging : size #

Set the page size for a PivotMatrix widget. Type is integer, default value is 10 rows.

paging : maxDisplayedPages #

Set the maximum number of displayed pages in the paging area. Type is integer,default values is 5 displayed pages.

paging : sizeSelect #

Set the page size select options for the paging. Type is array of integer, default values is array(5, 10, 20, 50, 100).

columnWidth #

Set the column width of the data table. Default value is '70px'.

Working examples

Click here to view pivot examples.

Enjoy!