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 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:

label{dynamic column name 1}...{dynamic column name N}
{dynamic row name 1 }{pivot data}{pivot data}{pivot data}
...{pivot data}{pivot data}{pivot data}
{dynamic row name N }{pivot data}{pivot data}{pivot data}

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:

label2020 -||- 01 -||- dollar_sales - sum...2020 -||- 12 -||- dollar_sales - sum
Alpha Cognac -||- Classic Cars16413.79...18324.08
............
Alpha Cognac -||- Trains7873.58...9351.86

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
    PivotTable::create(array(
        ...
        "measures" => [
            "dollar_sales - sum",
            "percentage change"
        ],
        ...
    ))

Get started with KoolReport

KoolReport will help you to construct good php data report by gathering your data from multiple sources, transforming them into valuable insights, and finally visualizing them in stunning charts and graphs.