Months vs Years Cube

Sales across months and year

Month200320042005Total
January $95,920 $292,385 $307,737 $696,042
February $128,404 $289,503 $317,192 $735,098
March $160,517 $217,691 $359,712 $737,920
April $185,849 $187,576 $344,821 $718,245
May $179,436 $248,325 $441,475 $869,235
June $150,471 $343,371 $0 $493,841
July $201,940 $325,563 $0 $527,504
August $178,257 $419,327 $0 $597,584
September $236,698 $283,800 $0 $520,497
October $514,336 $500,234 $0 $1,014,570
November $988,025 $979,292 $0 $1,967,316
December $276,723 $428,838 $0 $705,561

The report demonstrate the use of Cube package to analyze data. The raw data is pulled from CSV file containing sale amount, orderDay, orderMonth and orderYear. The time information will act as dimension. Those data after is piped through Cube process will be turn to 2 dimension table in which row will be group by month, column is grouped by year and data cell is sale amount purchase in particular year and month.

Cube can be considered a simple version of Pivot Tables that you see in Excel or any Speadsheet application. The different between Cube and Pivot is the number of dimension they handle. While Pivot can handle more than 2 dimension and support hierarchical dimension, Cube support only 2 dimensions and single level of dimension. Although it sounds simple but according to our observation, 70% cases Cube is enough.

Because of the simplicity, the power of Cube process lie on its speed to handle data compared to Pivot.

<?php
require_once "SalesMonthsYears.php";
$SalesMonthsYears = new SalesMonthsYears;
$SalesMonthsYears->run()->render();
<?php
require_once "../../../load.koolreport.php";
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Limit;
use \koolreport\processes\RemoveColumn;
use \koolreport\processes\OnlyColumn;
use \koolreport\processes\Sort;
use \koolreport\processes\ColumnsSort;
use \koolreport\processes\ValueMap;
use \koolreport\cube\processes\Cube;

class SalesMonthsYears 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')
        ->pipe(new ColumnMeta(array(
            "dollar_sales"=>array(
                'type' => 'number',
                "prefix" => "$",
            ),
        )));
        
        $node->pipe(new Cube(array(
            "column" => "orderYear",
            "sum" => "dollar_sales"
        )))->pipe(new ColumnMeta(array(
            "{{all}}"=>array(
                "label"=>"Total",
            ),
            "orderMonth"=>array(
                "label"=>"Month",
            ),
        )))->saveTo($node2);
        $node2->pipe($this->dataStore('salesYear'));
        $node2->pipe(new RemoveColumn(array(
        "{{all}}"
        )))->pipe($this->dataStore('salesYearNoAll'));
        
        $node->pipe(new Cube(array(
            "row" => "orderMonth",
            "column" => "orderYear",
            "sum" => "dollar_sales"
        )))
        ->pipe(new ColumnMeta(array(
            "{{all}}"=>array(
                "label"=>"Total",
            ),
            "orderMonth"=>array(
                "label"=>"Month",
            )
        )))
        ->pipe(new Sort(array(
            'orderMonth' => 'asc'
        )))
        ->pipe(new ColumnsSort(array(
            '{name}' => 'asc',
            'fixedColumns' => array(
                0 => 0,
                '{{all}}' => 10
            ),
        )))
        ->pipe(new ValueMap(array(
            "orderMonth"=>array(
                1 => "January",
                2 => "February",
                3 => "March",
                4 => "April",
                5 => "May",
                6 => "June",
                7 => "July",
                8 => "August",
                9 => "September",
                10 => "October",
                11 => "November",
                12 => "December",
            )
        )))
        ->saveTo($node2);
        $node2->pipe($this->dataStore('salesYearMonth'));
        $node2->pipe(new RemoveColumn(array(
            "{{all}}"
        )))->pipe($this->dataStore('salesYearMonthNoAll'));
        
    }
}
<?php
use \koolreport\widgets\koolphp\Table;
use \koolreport\widgets\google;
?>
<div class="report-content">

    <div class="text-center">
        <h1>Months vs Years Cube</h1>
        <p class="lead">
            Sales across months and year
        </p>
    </div>
    <?php 
    Table::create(array(
        "dataStore" => $this->dataStore('salesYearMonth'),
        'cssClass' => array('table' => 'table-condensed')
    ));
    ?>

    <div class='row'>
        <div class="col-md-4">
            <?php 
            google\ColumnChart::create(array(
                "dataStore"=>$this->dataStore('salesYearNoAll'),
                "options"=>array(
                    'title' => 'Yearly Sales',
                    'isStacked' => true,
                    'legend' => 'bottom'
                ),
                'width' => '100%',
            ));
            ?>
        </div>
        <div class="col-md-8">
            <?php 
            google\AreaChart::create(array(
                "dataStore"=>$this->dataStore('salesYearMonthNoAll'),
                "options"=>array(
                    'title' => 'Monthly Sales by Years',
                ),
                "width"=>'100%',
                // 'height'=>'300px',
            ));
            ?>
        </div>
    </div>

</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.33
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