Pivot

Overview #

A pivot table is a data summarization tool necessary in pretty much any data analytical program. It can count, total or average data based on multi dimensional groups to provide insight from raw data. Basically, a pivot table has two parts, one is multi-dimensional labels or headers, the other is aggregated or summarized data for such labels. Here is an example:

The above example is a two dimensional (column and row) pivot table which is visualized by the columns' and rows' label/header and the summarized data (total sum in this case) of them. Each dimension can consist of multiple fields (Year and Month in column, Customer and Product in row) so you could drill down (like expanding) or roll up (like collapsing) the labels and their summarized data.

Installation #

  1. Download and unzip the zipped file.
  2. Copy the folder pivot into koolreport/packages folder

Usage #

Our Pivot package contains a Pivot process for creating up a pivot table's structure. Then you could use a PivotTable or PivotMatrix widget to visualize it or extract a normal tabular data structure with PivotExtract process.

To set up a Pivot process, you would have to specify at least one dimension of label fields and a list of data fields (e.g dollar_sales) with aggregation methods (sum, count or average). In theory, a pivot could have multiple dimensions. In practice, users often set up one or two dimensions for easier viewing. The pivot process would aggregate data and save the result to a dataStore:

<?php
use \koolreport\pivot\processes\Pivot;

class CustomersCategoriesProducts extends koolreport\KoolReport
{
    function setup()
    {
        $node = $this->src('sales')
        ->query("SELECT customerName, productLine, orderYear, orderMonth, dollar_sales
        FROM customer_product_dollarsales")
        ->pipe(new Pivot(array(
        "dimensions" => array(
            "column" => "orderYear, orderMonth",
            "row" => "customerName, productLine"
        ),
        "aggregates"=>array(
            "sum" => "dollar_sales",
            "count" => "dollar_sales"
        )
        )))
        ->pipe($this->dataStore('salesReport'));  
    }
}

If you only want to use a part of the pivot data, there's a process called PivotExtract allowing you to extract a data table and save it to a datastore:

<?php
use \koolreport\pivot\processes\Pivot;
use \koolreport\pivot\processes\PivotExtract;

class CustomersCategoriesProducts extends koolreport\KoolReport
{
  function setup()
  {
    $node = $this->src('sales')
    ->query("SELECT customerName, productLine, orderYear, orderMonth, dollar_sales
    FROM customer_product_dollarsales")
    ->pipe(new Pivot(array(
        "dimensions" => array(
            "column" => "orderYear, orderMonth",
            "row" => "customerName, productLine"
        ),
        "aggregates"=>array(
            "sum" => "dollar_sales",
            "count" => "dollar_sales"
        )
    )))
    ->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('salesTable'));  
  }
}

In the report view, you can use a PivotTable or a PivotMatrix widgets to visualize the labels and the summarized data in the dataStore. While the PivotTable widget is a simple table for just viewing the data, the PivotMarix one is a interactive widget where you can drag and drop fields, sort by data fields or label fields, has paging and scrolling for viewing a lot of data in a fixed size widget.

<?php
use \koolreport\pivot\widgets\PivotTable;
PivotTable::create(array(
    "dataStore"=>$this->dataStore('salesReport')
));

use \koolreport\pivot\widgets\PivotMatrix;
PivotMatrix::create(array(
    "dataStore"=>$this->dataStore('salesReport')
));

For the simplest configuration you only need to tell which pivot dataStore to be displayed, the widgets would use a default setup to show it. For finer tuning the widgets have detailed options for you to customize.