Pivot data produced by PivotSQL process
PivotSQL works with large SQL datasets
PivotSQL
is a process to prroduce pivot data from large SQL datasets, which is then visualized with PivotMatrix
widget:
->pipe(new \koolreport\pivot\processes\PivotSQL([
"column" => "orderYear",
"row" => "customerName",
"aggregates"=>array(
"sum"=>"dollar_sales",
),
]))
In order to use PivotSQL
users need to turn on PHP's session at the beginning of the page:
session_start();
<?php
@session_start();
require_once "CustomersYears.php";
$CustomersYears = new CustomersYears;
$CustomersYears->run()->render();
?>
<?php
require_once "../../../load.koolreport.php";
use \koolreport\processes\ColumnMeta;
class CustomersYears extends koolreport\KoolReport
{
function settings()
{
$config = include "../../../config.php";
return array(
"dataSources"=>array(
"automaker"=>$config["automaker"]
)
);
}
function setup()
{
$this->src('automaker')
->query('select * from customer_product_dollarsales2')
->pipe(new \koolreport\pivot\processes\PivotSQL([
"column" => "orderYear",
"row" => "customerName",
"aggregates"=>array(
"sum"=>"dollar_sales",
),
]))
->pipe(new ColumnMeta(array(
"dollar_sales - sum"=>array(
'type' => 'number',
"prefix" => "$",
),
)))
->pipe($this->dataStore('pivotData'));
}
}
<?php
use \koolreport\pivot\widgets\PivotMatrix;
?>
<div class="report-content">
<div class="text-center">
<h1>Pivot data produced by PivotSQL process</h1>
<p class="lead">PivotSQL works with large SQL datasets
</p>
</div>
<form id='form1' class="form-inline" method="post">
<?php
PivotMatrix::create(array(
"id" => "pivotMatrix1",
'dataSource' => $this->dataStore('pivotData'),
'waitingFields' => array(
"productLine" => "label",
"productName" => "label",
"orderMonth" => "label",
"dollar_sales - count" => "data",
),
'paging' => array(
'size' => 10,
'maxDisplayedPages' => 5,
'sizeSelect' => array(5, 10, 20, 50, 100)
)
));
?>
</form>
</div>
customerName | productName | productLine | orderDate | orderDay | orderMonth | orderYear | orderQuarter | dollar_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