Sale Report

Summarize amount of sales and number of sales by three dimensions: customers, categories and products

Choose Start Date and End Date

As you may know about the Pivot term through Microsoft Excel or other SpreadSheet. Basically pivot table help to summarize data in multi dimensions.

In above example, data is pulled from CSV file and piped through Pivot process. We defined customers, categories and products in row of pivot table. By this settings we can see data in a tree like above, going from customers to product categories and to products. The sale amount are showing on the right hand side.

PivotTable is a powerful tools for data analysis, more information of Pivot package of KoolReport you may find here.

<?php
require_once "CustomersCategoriesProducts.php";
$CustomersCategoriesProducts = new CustomersCategoriesProducts;
$CustomersCategoriesProducts->run()->render();
<?php

require_once "CustomersCategoriesProducts.php";
$report = new CustomersCategoriesProducts();

$filehandler = $report->run()
->export("CustomersCategoriesProductsPdf")
->pdf(
  array(
    // "format"=>"A4",
    // "orientation"=>"landscape",
    "width" => '29cm',
    "height" => '21cm',
    "margin" => '2cm'
  )
);

$filehandler->toBrowser("CustomersCategoriesProducts.pdf");
<?php
include "CustomersCategoriesProducts.php";
$report = new CustomersCategoriesProducts;
$report->run();
$report->exportToExcel('CustomersCategoriesProductsExcel')->toBrowser("CustomersCategoriesProducts.xlsx");
<?php
require_once "../../../load.koolreport.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;

class CustomersCategoriesProducts extends koolreport\KoolReport
{
    use \koolreport\excel\ExcelExportable;
    use \koolreport\export\Exportable;
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding;

    protected function defaultParamValues()
    {
        return array(
            "dateRange"=>array(date("2004-01-01"),date("2005-01-01")),
        );
    }
    protected function bindParamsToInputs()
    {
        return array(
            "dateRange",
        );
    }

    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');
        
        if(!empty($this->params['dateRange'])){
            $node->pipe(new Filter(array(
                array('orderDate', '>=', $this->params['dateRange'][0]),
                array('orderDate', '<=', $this->params['dateRange'][1]),
            )))
            ->saveTo($node);
        }

        $node->pipe(new Filter(array(
            array('customerName', '<', 'Am'),
        )))
        ->pipe(new ColumnMeta(array(
            'dollar_sales'=>array(
                'type' => 'number',
                'prefix' => '$',
                'decimals'=>2,
            ),
        )))
        ->pipe(new Pivot(array(
            'dimensions'=>array(
                'row'=>'customerName, productLine, productName'
            ),
            'aggregates'=>array(
                'sum'=>'dollar_sales',
                'count'=>'dollar_sales'
            )
        )))
        ->pipe($this->dataStore('sales'));  
    }
}
<?php

use \koolreport\pivot\widgets\PivotTable;
use \koolreport\inputs\DateRangePicker;
?>
<div class='report-content'>
    <div class="text-center">
        <h1>Sale Report</h1>
        <p class="lead">
            Summarize amount of sales and number of sales by three dimensions: customers, categories and products
        </p>
    </div>
    <form action="" method="post" class="">
        <div class="form-group mt-4 text-center ">
            <button type="submit" class="btn btn-primary " formaction='export.php'>Download PDF</button>
            <button type="submit" class="btn btn-primary " formaction='exportExcel.php'>Download Excel</button>
        </div>
        <div class="form-inline justify-content-center">

            <strong>Choose Start Date and End Date</strong>
            <div class="col-md-4 form-group  ">
                <?php
                DateRangePicker::create(array(
                    "name" => "dateRange",
                ));
                ?>
            </div>
            <div class="form-group ">
                <button class="btn btn-primary " id="submit">Submit</button>
            </div>
        </div>
    </form>
    <div style="margin-top :20px;">
        <?php
        $dataStore = $this->dataStore('sales');
        PivotTable::create(array(
            'dataStore' => $dataStore,
            'rowDimension' => 'row',
            'measures' => array(
                'dollar_sales - sum',
                'dollar_sales - count',
            ),
            'rowSort' => array(
                'dollar_sales - sum' => 'desc',
            ),
            'rowCollapseLevels' => array(1),
            'totalName' => 'All',
            'width' => '100%',
            'nameMap' => array(
                'dollar_sales - sum' => 'Sales (in USD)',
                'dollar_sales - count' => 'Number of Sales',
            ),
        ));
        ?>
    </div>

</div>
<?php

use \koolreport\pivot\widgets\PivotTable;

$document_root = str_replace("\\", "/", $_SERVER["DOCUMENT_ROOT"]);
$assets_folder = str_replace("\\", "/", realpath(dirname(__FILE__) . "/../../../assets"));
$asset_url = str_replace($document_root, "", $assets_folder);
?>
<!DOCTYPE html>
<html>

<head>
    <title>Sale Report : customers, categories and products</title>
    <link rel="stylesheet" href="../../../assets/bs3/bootstrap.min.css" />
    <link rel="stylesheet" href="../../../assets/bs3/bootstrap-theme.min.css" />
    <link rel="stylesheet" href="../../../assets/fontawesome/font-awesome.min.css" />
</head>
<style>
    .box-container {
        width: 29cm;
    }

    .pivot-data-cell {
        text-align: right;
    }
</style>

<body>
    <div>

        <h1>Sale Report : customers, categories and products</h1>
        <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(
                    // 'orderMonth' => function($a, $b) {
                    // return (int)$a > (int)$b;
                    // }, 
                    // 'orderDay' => function($a, $b) {
                    // return (int)$a > (int)$b;
                    // },
                    'dollar_sales - sum' => 'desc',
                ),
                'columnSort' => array(
                    'orderMonth' => function ($a, $b) {
                        return (int)$a < (int)$b;
                    },
                    // 'dollar_sales - sum' => 'desc',
                    // 'orderYear' => 'desc', 
                ),
                // 'rowCollapseLevels' => array(1),
                // 'columnCollapseLevels' => array(0),
                'width' => '100%',
                'headerMap' => array(
                    'dollar_sales - sum' => 'Sales (in USD)',
                    'dollar_sales - count' => 'Number of Sales',
                ),
            ));
            ?>
        </div>

    </div>
</body>

</html>
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