Pivot's Dimensional Compurations

The example shows how to compute custom dimensional (row, column, etc) values

The report shows how to compute custom dimensional (row, column, etc) values:

        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "column"=>[
                    "orderYear" => [
                        "computations" => [
                            "yearlyDifference" => "{2004} - {2003}",
                            "yearlyDifference" => function($aggRow, $measure) {
                                return ($aggRow['2004'] ?? 0) - ($aggRow['2003'] ?? 0);
                            },
                        ],
                    ],
                    "orderMonth" => [
                        "computations" => [
                            "halfYearDifference" => "{12} + {11} + {10} + {9} + {8} + {7} - {1} - {2} - {3} - {4} - {5} - {6}",
                            "halfYearDifference" => function($aggRow, $measure) {
                                $result = 0;
                                $firstHalf = [1, 2, 3, 4, 5, 6];
                                $secondHalf = [7, 8, 9, 10, 11, 12];
                                foreach ($firstHalf as $month) $result -= $aggRow[$month] ?? 0;
                                foreach ($secondHalf as $month) $result += $aggRow[$month] ?? 0;
                                return $result;
                            }
                        ]
                    ],
                ],
                "row"=>"customerName, productLine",
            ),
            "aggregates"=>array(
                "sum"=>"dollar_sales",
            ),
        )))
<?php
require_once "YearsMonthsCustomersCategories.php";
$YearsMonthsCustomersCategories = new YearsMonthsCustomersCategories;
$YearsMonthsCustomersCategories->run()->render();
?>    
<?php
require_once "../../../load.koolreport.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;

class YearsMonthsCustomersCategories extends koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources" => array(
                "dollarsales"=>array(
                    'filePath' => '../../../databases/customer_product_dollarsales2.csv',
                    'fieldSeparator' => ';',
                    'class' => "\koolreport\datasources\CSVDataSource"      
                ), 
            )
        );
    }
    function setup()
    {
        $node = $this->src('dollarsales');
        
        $node->pipe(new Filter(array(
            array('customerName', '<', 'B'),
            array('orderYear', '<', 2005)
        )))
        ->pipe(new ColumnMeta(array(
            "dollar_sales"=>array(
                'type' => 'number',
                "prefix" => "$",
            ),
        )))
        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "column"=>[
                    "orderYear" => [
                        "computations" => [
                            "yearlyDifference" => "{2004} - {2003}",
                            "yearlyDifference" => function($aggRow, $measure) {
                                return ($aggRow['2004'] ?? 0) - ($aggRow['2003'] ?? 0);
                            },
                        ],
                    ],
                    "orderMonth" => [
                        "computations" => [
                            "halfYearDifference" => "{12} + {11} + {10} + {9} + {8} + {7} - {1} - {2} - {3} - {4} - {5} - {6}",
                            "halfYearDifference" => function($aggRow, $measure) {
                                $result = 0;
                                $firstHalf = [1, 2, 3, 4, 5, 6];
                                $secondHalf = [7, 8, 9, 10, 11, 12];
                                foreach ($firstHalf as $month) $result -= $aggRow[$month] ?? 0;
                                foreach ($secondHalf as $month) $result += $aggRow[$month] ?? 0;
                                return $result;
                            }
                        ]
                    ],
                ],
                "row"=>"customerName, productLine",
            ),
            "aggregates"=>array(
                "sum"=>"dollar_sales",
                // "count"=>"dollar_sales",
            ),
        )))
        ->pipe($this->dataStore('sales'));  
    }
}
<?php

use \koolreport\pivot\widgets\PivotTable;
?>
<div class="report-content" style="overflow: scroll">
    <div class="text-center">
        <h1>Pivot's Dimensional Compurations</h1>
        <p class="lead">
            The example shows how to compute custom dimensional (row, column, etc) values
        </p>
    </div>

    <?php
    $dataStore = $this->dataStore('sales');
    PivotTable::create(array(
        "dataStore" => $dataStore,
        "rowDimension" => "row",
        "columnDimension" => "column",
        "measures" => array(
            "dollar_sales - sum",
            // 'dollar_sales - count',
        ),
        // 'rowSort' => array(
        //     'dollar_sales - count' => 'desc',
        // ),
        'columnSort' => array(
            'orderMonth' => function ($a, $b) {
                return (int)$a < (int)$b;
            },
        ),
        // "showDataHeaders" => true,
        // 'rowCollapseLevels' => array(0),
        // 'columnCollapseLevels' => array(0),
        'width' => '100%',
        'headerMap' => array(
            'dollar_sales - sum' => 'Sales',
            'yearlyDifference' => 'Yearly Difference',
            'halfYearDifference' => 'Half Year Difference',
            '1' => 'January',
            '2' => 'February',
            '3' => 'March',
            '4' => 'April',
            '5' => 'May',
            '6' => 'June',
            '7' => 'July',
            '8' => 'August',
            '9' => 'September',
            '10' => 'October',
            '11' => 'November',
            '12' => 'December',
        ),
    ));
    ?>

</div>
customerNameproductNameproductLineorderDateorderDayorderMonthorderYearorderQuarterdollar_sales
Vitachrome Inc. 1937 Lincoln Berline Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 3726.45
Vitachrome Inc. 1936 Mercedes-Benz 500K Special Roadster Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 1768.3300000000002
Baane Mini Imports 1952 Alpine Renault 1300 Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5571.8
Baane Mini Imports 1962 LanciaA Delta 16V Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5026.14
Baane Mini Imports 1958 Setra Bus Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3284.28
Baane Mini Imports 1940 Ford Pickup Truck Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3307.5
Baane Mini Imports 1926 Ford Fire Engine Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 1283.48
Baane Mini Imports 1913 Ford Model T Speedster Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2489.13
Baane Mini Imports 1934 Ford V8 Coupe Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2164.4
Baane Mini Imports 18th Century Vintage Horse Carriage Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2173

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport Get KoolReport Pro