• Overview
• Installation
  • By downloading .zip file
  • By composer
• Usage

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 #

By downloading .zip file #

1. Download zip file from My Licenses
2. Unzip
3. Copy the folder pivot into koolreport folder, it will look like below:

koolreport
├── core
├── pivot

By composer #

If you have purchased the package then you can follow these steps to install

1. Login to koolreport.com
2. Go to My Licenses
3. Click Get Token For Composer button
4. Copy the text and save to file auth.json next to composer.json
5. Add the repositories to composer.json like below
6. Run composer update to install package

composer.json

{
    "repositories":[
      {"type":"composer","url":"https://repo.koolreport.com"}
    ],
    "require":{
        "koolreport/pivot":"*",
        ...
    }
}

Your auth.json will look like this:

{
    "http-basic": {
        "repo.koolreport.com": {
            "username": "your@email.com",
            "password": "your-secret-token"
        }
    }
}

Note: Please add your auth.json to .gitignore as it contains your secret login information.

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.