Data Visualization

Creating your beautiful report with charts and tables.

Overview

We have learned in previous section how to connect to database, pulling data and piping them through series of processes to get meaningful data. Those data are stored in the data stores of the report ready to be used to create beautiful visualization. In this section, we will learn about the view of report and how to use Widgets in your view.

What is report's view?

It is another PHP file containing html/css determined how your report will appear. Normally, the view file will be put in the same folder with your report class file and has suffix .view.php.


/
├── SaleReport.php
└── SaleReport.view.php

                    

What is Widget?

Widget is the component of your report view. It takes the data from report's dataStore and represent them in meaningful way. Widget can be anything, text, tables or charts. All of them are derived from \koolreport\core\Widget class.

Example


<?php
    use \koolreport\widgets\koolphp\Table;
?>
<h1>Sale Report</h1>
<?php 
    Table::create(array(
        "dataStore"=>$this->dataStore('sales'),
        "columns"=>array("product","sale_amount")
    ));
?>
                

Multiple views

A report can have many different views. Each view may hold different charts/tales describe the same data or each view is for different purpose. The purpose could be to display html on web or to generate PDF, XML, JSON.


<?php
...
$saleReport = new SaleReport();
$saleReport->run();
$saleReport->render(); //--> This will render the default view SaleReport.view.php
...
$saleReport->render("SaleReport_AnotherView");//--> This will render the view: SaleReport_AnotherView.view.php
                

Get rendered content

Sometime we may need to get the report rendered content to further process. By enter true in second parameter of render() function, it will return the content instead of sending content to browser.


$content = $saleReport->render("SaleReport",true);//--> Return the view content
                

Koolphp Widgets

Table

Table widget help us to display data in table format. It's classname is \koolreport\widgets\koolphp\Table Let look at below example before we go details on the options.

Examples


<?php
    use \koolreport\widgets\koolphp\Table;
?>
<h1>Sale Report</h1>
<?php 
    Table::create(array(
        "dataStore"=>$this->dataStore('sales'),
        "columns"=>array(
            "product"=>array(
                "label"=>"Product Name"
            ),
            "sale_amount"=>array(
                "type"=>"number",
                "label"=>"Sale Amount",
                "prefix"=>"$"
            )
        ),
        "cssClass"=>array(
            "table"=>"table table-bordered"
        )
    ));
?>
                    

Options

Name type default description
dataSource mixed Set data source for Widget, dataSource accepts DataStore, DataSource, Process object or even data in array format

<?php
//Use data store
Table::create(array(
    "dataSource"=>$this->dataStore("mydata"),
    ...
))
?>

<?php
//Use DataSource
Table::create(array(
    "dataSource"=>$this->src("sakila")->query("select * from orders"),
    ...
))
?>

<?php
//Use Process
Table::create(array(
    "dataSource"=>(
        $this->src("sakila")->query("select * from orders")
        ->pipe(new Filter(array(
            array("payment_date",">","2014-01-01")
        )))
    
    ),
    ...
))
?>

<?php
//Use associate array
Table::create(array(
    "dataSource"=>array(
        array("name"=>"Peter","age"=>35),
        array("name"=>"Karl","age"=>32),
    )
));
?>

<?php
//Use normal array
Table::create(array(
    "dataSource"=>array(
        array("name","age"),
        array("Peter",35),
        array("Karl",32),
    )
));
?>

                                        
dataStore object Alternative to dataSource
data array * Deprecated, use dataSource instead If you do not want to use dataStore, you may use this properies to set data on the fly. The data is format of array of associate array.
"data"=>array(
    array("name"=>"Peter","age"=>30),
    array("name"=>"Karl","age"=>25)
)
columns array List of column to be rendered in table. More details.
cssClass array Contain css class for table, table's row and table's cell. More details
removeDuplicate array array() Set the list of columns which duplicated values will be removed. Eg.removeDuplicate=>array("country","state")

Columns options

"columns" options simply contain the list of column you want to show on the table. The simplest form of columns settings is array of columns name for example: array("id","name","address"). More advanced, you can add extra meta data for columns like below:


<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "sale_amount"=>array(
                "type"=>"number",
                "label"=>"Sale Amount",
                "prefix"=>"$"
            )
        ),
        ...
    ));
?>
                        

Table has special "{others}" columns. This mean that beside columns has been specified settings in "columns", other columns will be added and use the settings specified in {others}. This is necessary when you want to give common settings for many columns at the same time. Or in the case that you do not know name of columns but you know the settings for them.


<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "item"=>array(
                "type"=>"string",
            ),
            "{others}"=>array(
                "type"=>"number",
                "prefix"=>"$",
            )
        ),
        ...
    ));
?>
                        

Format column value

One of important feature in Table is ability to format value dynamically. Normally, by settings type for column, the value of column will be formatted automatically. However, in some case, you may need manual format, you can do so with formatValue settings. formatValue can be a string or a function. For simple format, you may use string:


<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "amount"=>array(
                'formatValue'=>'$ @value', // format to $12
            ),
        ),
        ...
    ));
?>
                        

Or you can specify in function:


<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "amount"=>array(
                'formatValue'=>function($value)
                {
                    return "$ ".$value;
                }
            ),
        ),
        ...
    ));
?>
                        

Set cssStyle for a column

You can specify css for a specific column. The "cssStyle" can be css string or an array of component "th", "td", "tf"


<?php 
    Table::create(array(
        ...
        "columns"=>array(
            "amount"=>array(
                "cssStyle"=>"text-align:right"
            ),
            "customerName"=>array(
                "cssStyle"=>array(
                    "th"=>"font-weight:bold;text-align:center",
                    "tr"=>"text-align:center",
                    "tf"=>"text-align:center",
                )
            )
        ),
        ...
    ));
?>                    
                    

Show/hide header

You can show or hide the header of table simply by setting "showHeader"=>true or "showHeader"=>false

Aggregated footer

Table has capability to show aggregated result of a column at footer. To do so you need to turn on footer by setting "showFooter"=>"top" or "showFooter"=>"bottom". On the column you want to aggregate, you set "footer"=>"sum". The Table support "avg", "min", "max" operation as well.


<?php 
    Table::create(array(
        ...
        "showFooter"=>"bottom",
        "columns"=>array(
            "amount"=>array(
                "footer"=>"sum"
            ),
            "sale"=>array(
                "footer"=>"avg",
                "footerText"=>"Avg Sale: @value",
            )
        ),
        ...
    ));
?>                    
                    

The "footerText" can be used to set any text at footer of column, it also can act as template. In above example, the @value will be replace with average of sale.

CssClass options

Name type default description
table string List of class for <table> tag. If you use Bootstrap, you can set "table"=>"table table-bordered"
tr string, function

//Set tring to "tr"
"tr"=>"row-css-class"
//or set function to it, the function will take $row as parameter. Based on data, you can set class for the row
"tr"=>function($row){
    return "row-css-class";
}
                                        
th string, function

//Set tring to "tr"
"th"=>"row-css-class"
//or set function to it, the function will take $columnName as parameter. Based on columnName, you can set class for this header
"th"=>function($columnName){
    return "header-css-class";
}
                                        
td string, function Example:

//Set tring to "td"
"td"=>"cell-css-class"
//or set function to it, the function will take $row and $columnName as parameters. Based on data, you can set class for the cell
"td"=>function($row,$columnName){
    return "cell-css-class";
}
                                        

Table paging

Table can be paginated with following settings:


<?php 
    Table::create(array(
        ...
        "paging"=>array(
            "pageSize"=>7,
            "pageIndex"=>0,
            "align"=>"center"
        )
    ));
?>                    
                    

The parameter pageSize sets number of row in a page. The pageIndex can be used to set current page for Table, its default value is 0 which indicated table is in the first page. The align is used to set position of pager, its value can be "left", "right" and "center".

Client events

Table supports the following client events

Name description
rowClick Triggered when table row is clicked

<?php 
Table::create(array(
    ...
    "clientEvents"=>array(
        "rowClick"=>"function(e){
            console.log(e.rowIndex);
            console.log(e.rowData);
            console.log(e.table);
        }"
    ));
?> 
                    

Google Charts

Google Charts is a great package providing various types of charts more than enough to visualize your data. What is the best thing? Google Charts is totally free.

Google Charts can not be used offline!

According to Google Charts term of service, you need to load chart library from google which means that if you are offline, Google Charts will not load.

Common Settings

Below are common settings for Google Charts.

Name type default description
dataSource object Set the source of data for widget, accept DataStore, DataSource, Process and even array
//Use dataStore
"dataSource"=>$this->dataStore("data")
//Use datasource
"dataSource"=>$this->src("sakila")->query("...")
//Use process
"dataSource"=>(
    $this->src("sakila")->query("...")
    ->pipe(new Filter(array(
        ...
    )))
)
//Use associate array
"dataSource"=>array(
    array("name"=>"Peter", "age"=>35),
    array("name"=>"Karl", "age"=>32),
)
//Use array
"dataSource"=>array(
    array("nane","age")
    array"Peter",35),
    array"Karl",32),
)
dataStore object This is alias name of dataSource
colorScheme array Set color scheme for google charts ex. "colorScheme"=>array("#000","#444","#888","#ccc");
columns array The list of columns to export to chart
width string "400px" Width of chart
height string "300px" Height of chart
options array This is the options for specific charts. It may vary from charts to charts so for more information, please look at example for specific chart below and its options.

Client events

Google Charts supports following events:

name description params
itemSelect Trigger when an item on chart is selected for example the bar in BarChart or a pie in PieChart
  • params.selectedRowIndex: Index of selected row
  • params.selectedColumnIndex: Index of selected column
  • params.selectedValue: Selected value
  • params.selectedRow: Selected row data
  • params.columnName: Name of selected column
  • table: Return the whole datatable of the chart
rowSelect Trigger when a row on chart is selected, it is used in GeoChart widget
  • params.selectedRowIndex: Index of selected row
  • params.selectedRow: Selected row data
  • table: Return the whole datatable of the chart
columnSelect Trigger when a column on chart is selected
  • params.selectedColumnIndex: Index of selected column
  • params.columnName: Name of selected column
  • table: Return the whole datatable of the chart
select Trigger on all selection on chart
  • params.selectedRowIndex: Index of selected column
  • params.selectedColumnIndex: Index of selected column
  • table: Return the whole datatable of the chart

Example:


<?php 
    ColumnChart::create(array(
        ...
        "clientEvents"=>array(
            "itemSelect"=>"function(params){
                console.log(params.selectedValue);
            }"
        )
    ));
?>
                        

LineChart

LineChart class name is \koolreport\widgets\google\LineChart. Please see the example code below

Example


<?php
    use \koolreport\widgets\google\LineChart;
?>
<?php 
    LineChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

BarChart

BarChart class name is \koolreport\widgets\google\BarChart. Please see the example code below

Example


<?php
    use \koolreport\widgets\google\BarChart;
?>
<?php 
    BarChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

ColumnChart

ColumnChart class name is \koolreport\widgets\google\ColumnChart. Please see the example code below

Example


<?php
    use \koolreport\widgets\google\ColumnChart;
?>
<?php 
    ColumnChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

PieChart

PieChart class name is \koolreport\widgets\google\PieChart. Please see the example code below

Example


<?php
    use \koolreport\widgets\google\PieChart;
?>
<?php 
    PieChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

DonutChart

DonutChart class name is \koolreport\widgets\google\DonutChart. Please see the example code below

Example


<?php
    use \koolreport\widgets\google\DonutChart;
?>
<?php 
    DonutChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

ScatterChart

LineChart class name is \koolreport\widgets\google\ScatterChart. Please see the example code below

Example


<?php
    use \koolreport\widgets\google\ScatterChart;
?>
<?php 
    ScatterChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

AreaChart

AreaChart class name is \koolreport\widgets\google\AreaChart. Please see the example code below

Example


<?php
    use \koolreport\widgets\google\AreaChart;
?>
<?php 
    AreaChart::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

Map

Map class name is \koolreport\widgets\google\Map. Please see the example code below

Example


<?php
    use \koolreport\widgets\google\Map;
?>
<?php 
    Map::create(array(
        "dataStore"=>$this->dataStore('sales')
        "width"=>"400px",
        "height"=>"300px",
        "columns"=>array(
            "date"=>array(
                "label"=>"Date",
                "type"=>"datetime"
            ),
            "sale_amount"=>array(
                "label"=>"Sale Amount",
                "type"=>"number",
                "prefix"=>"$"
            )        	
        ),
        "options"=>array(
            "title"=>"Sale Performance"
        )
    ));
?>
                        

Other available charts

Other that those common chart above, Google library contains many other charts such as Histogram, OrgChart, GeoChart etc. All are included in \koolreport\widgets\google namespace which you can explore further. The common settings are the same but you need to refer to Google Charts documentation for details of data structure and availale options for each charts.

SubReport

SubReport is a feature of KoolReport that allows another report can be loaded inside a main one. The greatest feature is the ability to update a sub report through AJAX.

The SubReport feature comes from the needs of dividing a big report into smaller reports for better management which we called "divide and conquer" strategy. Also by dividing into smaller and separated reports, we promote the reusable code among reports, for example a subreport can be used in two different report.

Use cases

  • When you have big report and would like to "divide and conquer", handle part by part
  • When you like to create reusable code among different reports
  • When you want to render render different reports dynamically
  • When you want to update report partially, for example
    • Update charts via ajax
    • Make related data-driven drop-down combobox
    • Real-time reports, automatically updated report

Setup

Let say we have a ParentReport and would like to have a ChildReport inside.


<?php
// ChildReport.php
class ChildReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources"=>array(
                "automaker"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=automaker",
                    "username"=>"root",
                    "password"=>"",
                    "charset"=>"utf8"
                ),
            )
        )
    }

    function setup()
    {
        $this->src("automaker")->query("
            SELECT customerNumber,customerName from customers
        ")->pipe($this->dataStore("customers")))
    }
}            
                

<?php
// ChildReport.view.php
use \koolreport\widgets\koolphp\Table;    
?>

<?php
Table::create(array(
    "dataStore"=>$this->dataStore("customers"),
));
?>
                

<php

require "ChildReport.php";

class ParentReport extends \koolreport\KoolReport
{
    use \koolreport\core\SubReport;

    function settings()
    {
        return array(
            "subReports"=>array(
                "childreport"=>ChildReport::class
            )
        )
    }

    function setup()
    {

    }
}
                

<?php
//ParentReport.view.php
?>
<html>
    <head>
        <title>Parent Report
    </head>
    <body>
        <h1>Parent Report

        <div>
            <?php $this->subReport("childreport"); ?>
        </div>
    </body>
</html>
                

As you expected, the parent report will render with child report inside.

Partially rendering

One of greatest feature of sub report is that you may only render the child report through parent report. If you want to render any child report, you do:


<?php
$report = ParentReport(array(
    "@subReport"=>"childreport"
));
                

Update from client-side

We are able to partially update the view of child report at client-side from parent report. To do so, in the parent report, you do:


<script type='text/javascript'>
    function update()
    {
        subReport.update("childreport",{
            "any_param":"any_value"
        });
    }
</script>
                

By calling the update() function the child report will update its view. The parameters inserted at the end will be transferred to child report at server-side, the child report will render updated view and refeshed at client-side.

Create new widget

A widget normally contain a widget class and a widget template. The widget class will take care of the logic layer preparing data to be rendered in widget template.


/
├── my_widget_asset_folder/
│   ├── mywidget.css
│   └── mywidget.js
├── MyWidget.php
└── MyWidget.tpl.php
                
MyWidget.php

<?php
class MyWidget extends \koolreport\core\Widget
{
    protected $anyParam;
    protected function onInit()
    {
        // Get called when the widget is initiated
        // You may access the widget settings params through $this->params
        $this->anyValue= $this->params["anyValue"];
    }

    protected function resourceSettings()
    {
        // You only need this function if your widget requires client resource like js or css
        // Or it need library such as jquery, font-awesome
        return array(
            "library"=>array("jQuery"),//If you require "jquery","font-awesome" or "raphael" or just delete it
            "folder"=>"my_widget_asset_folder", // Folder that contains resources for client
            "js"=>array("mywidget.js"), //List all required js
            "css"=>array("mywidget.css"), // List all required css
        )
    }

    protected function onRender()
    {
        // Here you got to prepare data to render
        // Call $this->template() to render widget template with data.
                
        $this->template("MyWidget",array(
            "any_variable"=>$any_variable
        ));
    }
}
                
MyWidget.tpl.php

<h3>My widget rendering</h3>


<?php
// Your variable input in the $this->template() function will be available as well
echo $any_variable;

//The $this variable refers to the your widget's object. 
?>

                
Data Processing Class Reference