• Properties
  • Templates
  • Measures
  • Sort
  • map (version >= 5.0.0)
  • cssClass (version >= 6.0.0)
  • Collapse level
  • Total name
  • Hide total/subtotal column/row (version >=4.2.0)
  • Show data field headers (version >= 5.0.0)
  • Width
  • height
• PivotTable exclusive properties
  • `initVisible` (version >= 8.0.0)
• PivotMatrix exclusive properties
  • paging
  • columnWidth
  • serverPaging (version >= 8.5.0)

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
\koolreport\pivot\widgets\PivotTable::create(array(
    ...
    'template' => 'PivotTable-Bun',
    ...
));

\koolreport\pivot\widgets\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
\koolreport\pivot\widgets\PivotTable::create(array(
    "dataStore"=>$this->dataStore('salesReport'),
    'measures'=>array(
        'dollar_sales - sum'
        'order_id - count'
    ),
));

\koolreport\pivot\widgets\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
\koolreport\pivot\widgets\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;
        }, 
    ),
  ...
));

\koolreport\pivot\widgets\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;
        }, 
    ),
    ...
));

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
\koolreport\pivot\widgets\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;
        },
        'dataHeader' => function($dataField, $fieldInfo) {
            $v = $dataField;
            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
\koolreport\pivot\widgets\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
\koolreport\pivot\widgets\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
\koolreport\pivot\widgets\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
\koolreport\pivot\widgets\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
\koolreport\pivot\widgets\PivotTable::create(array(
    ...
    'showDataHeaders' => true,
    ...
));

Width #

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

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

height #

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

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

PivotTable exclusive properties #

initVisible (version >= 8.0.0)

By default PivotTable hides its dom visibility at first loading and only shows visible after some initial client functions finish executing. Users could change this behavior by setting the initVisible property to true.

<?php
\koolreport\pivot\widgets\PivotTable::create(array(
    ...
    "initVisible" => true,
));

PivotMatrix exclusive properties #

The following properties only apply for the PivotMatrix widget

paging #

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

size #

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

maxDisplayedPages #

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

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'.

serverPaging (version >= 8.5.0) #

By default, PivotMatrix loads all of its data to client and does client-side paging. If you have a big PivotMatrix, rendering it all at first loading would probably take a long time. To reduce both client rendering time and server output size you could set serverPaging property to be true.