• Dimensions
• Aggregates
  • =-9.5.0)'>Aggregates' computations (version >= 9.5.0)
  • Dimensional computations (version >= 9.5.0)

Pivot process

Dimensions #

Even though a pivot process can handle more than two dimension, a PivotTable or PivotMatrix widget can only show at most two dimensions at once. By default, the two dimensions to be display are "row" and "column" but you could change them according to your setup. For example, when setting up a Pivot process:

<?php
class CustomersCategoriesProducts extends koolreport\KoolReport
{
    function setup()
    {
        ...
        ->pipe(new \koolreport\pivot\processes\Pivot(array(
            "dimensions" => array(
                "row" => "orderYear, orderMonth",
                "column" => "customerName, productLine"
            ),
            "aggregates"=>array(
                "sum" => "dollar_sales",
            )
        )))
        ...
    }
}

Then when setting up a PivotTable or PivotMatrix widget:

<?php
\koolreport\pivot\widgets\PivotTable::create(array(
    "dataStore"=>$this->dataStore('salesReport'),
    'rowDimension'=>'row',
    'columnDimension'=>'column',
));

\koolreport\pivot\widgets\PivotMatrix::create(array(
    "dataStore"=>$this->dataStore('salesReport'),
    'rowDimension'=>'row',
    'columnDimension'=>'column',
));

Aggregates #

The Pivot process supports the following aggregating operators: sum, count, avg, min, max (version >= 1.0.0) and sum percent, count percent (version >= 4.1.0). When setting a Pivot process, you could add multiple fields for each operator like this:

<?php
class CustomersCategoriesProducts extends koolreport\KoolReport
{
    function setup()
    {
        ...
        ->pipe(new Pivot(array(
        ...
        "aggregates"=>array(
            "sum" => "dollar_sales",
            "sum percent" => "dollar_sales",
            "count" => "dollar_sales, order_id",
            "count percent" => "order_id",
        )
        )))
        ...
    }
}

Since version 9.5.0, 3 new operators are added. They are "count distinct", "sum distinct", and "count not null".

Aggregates' computations (version >= 9.5.0) #

Sometimes you want to combine various aggregates to build a new measure. That such cases "computations" might be helpful for you. For example, to compute average sales per distinct product line/category:

        ->pipe(new Pivot(array(
            ...
            "aggregates"=>array(
                "sum"=>"dollar_sales",
                "count distinct" => "productLine",
            ),
            "computations" => array(
                "avgSalesPerDistinctLine" => "{dollar_sales - sum} / {productLine - count distinct}",
                "avgSalesPerDistinctLine" => function($aggRow) {
                    return $aggRow["productLine - count distinct"] != 0 ?
                        $aggRow["dollar_sales - sum"] / $aggRow["productLine - count distinct"] : null;
                }
            ),
        )))
        ...

An aggregates' computation (e.g "avgSalesPerDistinctLine") could be a formula string of other aggregate measures or an anonymous function.

Dimensional computations (version >= 9.5.0) #

Similarly to aggregates' computations, you has the ability to combine dimensional values (i.e row, column ones). For example, to find total sum of several monthly measures or difference between yearly ones:

        ->pipe(new Pivot([
            "dimensions" => [
                "row" => ...,
                "column" => [
                    "orderYear" => [
                        "computations" => [
                            "yearlyDifference" => "{2004} - {2003}",
                            "yearlyDifference" => function($aggRow, $measure) {
                                return ($aggRow['2004'] ?? 0) - ($aggRow['2003'] ?? 0);
                            },
                        ],
                    ],
                    "orderMonth" => [
                        "computations" => [
                            "firstFiveMonthsTotal" => "{1} + {2} + {3} + {4} + {5}",
                            "firstFiveMonthsTotal" => function($aggRow, $measure) {
                                $result = 0;
                                $firstFiveMonths = [1, 2, 3, 4, 5];
                                foreach ($firstFiveMonths as $month) $result += $aggRow[$month] ?? 0;
                                return $result;
                            }
                        ]
                    ],
                ]
            ],
            "aggregates"=>[
                ...
            ],
        ]

A dimensional computation (e.g "yearlyDifference", "firstFiveMonthsTotal") could be a formula string of other dimensional values or an anonymous function. In the case of formula strings they will be computed the same for all aggregate and computed measures. With anonymous functions you can customize results based on the argument $measure.