Pivot2D process

Instead of the Pivot process you could use Pivot2D one. The result data of Pivot2D has a simpler structure than Pivot's but could still be used by PivotTable or PivotMatrix widget. Pivot2D is especially useful if you want to customize pivot data. Here's an example of the process:

    ->pipe(new \koolreport\pivot\processes\Pivot2D(array(
        "column" => "orderYear, orderMonth",
        "row" => "customerName, productLine",
        "aggregates" => array(
            "sum" => "dollar_sales",
            "count" => "dollar_sales",
        ),
        "fieldDelimiter" => " -||- ",
    )))

The result of Pivot2D is a table in the form of:

Dynamic column names are of this form {column 1} {fieldDelimiter} {column N} {fieldDelimiter} {aggregate measure}. For example, in the previous example, a dynamic column name would be like 2020 -||- 01 -||- dollar_sales - sum where 2020 is a year value, 01 is a month value and dollar_sales - sum is an aggregated measure.

Dynamic row names are of this form {column 1} {fieldDelimiter} {column N}. For example, in the previous example, a dynamic row name would be like Alpha Cognac -||- Classic Cars where Alpha Cognac is a customer name value, Classic Cars is a product line value.

Here's an example of a part of a Pivot2D result data:

The power of Pivot2D comes in when you want some customization like percentage change between December (month 12) and January (month 01) of year 2020. In that case you could apply either a CalculatedColumn or Map process like this:

    //MyReport.php
    ->pipe(new Pivot2D(array(
        ...
    )))
    ->pipe(new Map(array(
        "{value}" => function($row) {
            $row["2020 -||- 12 -||- percentage change"] = 
                100 * $row["2020 -||- 12 -||- dollar_sales - sum"] / $row["2020 -||- 01 -||- dollar_sales - sum"];
            return $row;
        }
    )))

    //MyReport.view.php
    \koolreport\pivot\widgets\PivotTable::create(array(
        ...
        "measures" => [
            "dollar_sales - sum",
            "percentage change"
        ],
        ...
    ))