DrillDown

Build awesome drill-down, drill-through report easily.

Introduction

DrillDown is a powerful report type in data analysis. It allows your data to summarize in highest level then break down to smaller one. All level break-down data can be visualized in charts or tables for your better understanding of data.

This package contains 3 different widgets: DrillDown, CustomDrillDown and MultiView.

Installation

  1. Unzip drilldown.zip
  2. Copy drilldown folder into koolreport\packages
  3. All done! Now you can use the package in your report.

DrillDown Widget

DrillDown is an widget that allows you to setup an drill-down report in fastest and easiest way. All you need to do are:

  1. Setup SQL Statement to withdraw data
  2. Setup multi-levels of drill-down.
  3. On each level of drill-down, choose chart type to visualize data

Example

<?php 
DrillDown::create(array(
    "name"=>"saleReportByLocation",
    "title"=>"Sale By Location",
    //Define dataSource which is SQL statement to load all table
    "dataSource"=>(
        $this->src("mydata")->query("SELECT country, state, city, sale_amount FROM orders")
    ),
    //What we want to calculate
    "calculate"=>array(
        "sum"=>"sale_amount"
    ),
    "levels"=>array(
        //Level 1: Group all table by country, showing all countries with `sale_amount` by country.
        array(
            "groupBy"=>"country",
            "widget"=>array(ColumnChart::class,array(
                "columns"=>array("country","sale_amount")
            ))
            "title"=>"All countries",
        ),
        //Level 2: When user select a country, the widget shows `sale_amount` by state of that country
        array(
            "groupBy"=>"state",
            "widget"=>array(ColumnChart::class,array(
                "columns"=>array("state","sale_amount")
            )),
            "title"=>function($params)
            {
                return $params["country"];
            }
        ),
        //Level 3: When user continues to select a state, then widget will show  `sale_amount` by city of that state
        array(
            "groupBy"=>"city",
            "widget"=>array(Table::class,array(
                "columns"=>array("city","sale_amount")
            )),
            "title"=>function($params)
            {
                return $params["city"];
            }
        )
    )
));
?>

Properties

nametypedefaultdescription
namestring*Required Name of the drill down report
dataSourcemixed*Required DataSource accepts data in form of DataStore, array, or even process.
dataStoremixedThis can be used alternatively to dataSource
calculatearray*Required Define what we want to summarize in form of "{method}"=>"{columnName}". The method supports "sum", "avg", "min", "max"
levelsarray* Required List of all levels for drill down report. See the level properties for more detail of settings on each level.
showLevelTitlebooltrueWhether title of each level is shown
btnBackmixedtrueBy default, the button Back will shown, give it value false you will off the Back button. This property can receive array() to customize cssClass and text of button "btnBack"=>array("text"=>"Go Back","class"=>"btn btn-default btn-warning")
cssmixedDefined css for the most important elements in the widgets, see the $css properties for more details.
panelStylestring"default"Set the panel style, accept value "default", "danger", "success", "info"
titlestringTitle that is showed on top of drill-down report.
scopearray/functionSet the group of parameters which will be kept persistent during the ajax post back of drill-down.
clientEventsarrayRegister client-event, see client-event for more details.

Css Properties

nametypedefaultdescription
panelstringDefine css style for top panel
levelTitlestringDefine css style for section that holds titles of level
btnBackstringAdd css style for Back button
bodystringDefined css style for body

Examples

<?php
DrillDown::create(array(
    ...
    "css"=>array(
        "btnBack"=>"font-style:italic";
        "body"=>"height:300px;"
    )
));
?>

Level properties

As we have seen from example on the top of page, levels property is an array of each level. Before are the settings of a level

nametypedefaultdescription
groupBystring*Required contain the column name which drill down will group by on each level
titlestring, functionSet the title information for each level, it accepts static string or a function to generate dynamic title for level. The function will receive a parameter as array containing all previous selection of users
widgetarrayContain the class name of widget that you want to use and its settings. You may use virtually all kind of widgets here. It could be Table or GoogleChart or any kinds of widgets available for KoolReport. Please see example of top for more details.

Client events

DrillDown support following events:

namedescription
nextingFired when drill-down is preparing to go to next level. Return false to cancel action.
nextedFired when drill-down went to next level successfully.
backingFired when drill-down is going to go back to previous level. Return false to cancel action.
backedFired when drill-down went back to previous level
changedFired when drill-down changed level

Example

<?php
DrillDown::create(array(
    ...
    "clientEvents"=>array(
        "nexting"=>"function(params){
            return false;//Cancel action
        }",
        "nexted"=>"function(params){
            console.log('Nexted to'+params.level);
        }",
    );
));
?>

CustomDrillDown

CustomDrillDown extends the capability to the max. It does not limit the number of charts in a level, you can show any custom content inside a level of drilldown. Due to flexibility, CustomDrillDown is a little more complicated to handle than normal drilldown above. To summarize the comparison between CustomDrillDown and DrillDown

  1. CustomDrillDown is faster than DrillDown since CustomDrillDown use the SubReport technology.
  2. When callback, only subreport of CustomDrillDown runs so it is more efficient while DrillDown will run the whole main report so it is less efficient.
  3. In CustomDrillDown, you need to setup subreport and connecting them with parameters while in DrillDown, you do not need to, we handle all for you.
  4. In sum, DrillDown vs CustomDrillDown is the trade-off between the convenience and the powerfulness, the easiness and the complexity.

Below is a tutorial to setup a basic CustomDrillDown:

Step 1: Create CountrySale report

The CountrySale report is created in the same way you create a single report. In this CountrySale report, we summarize sale by country and display all countries. When user select a country, it called DrillDown next() function to pass the selection parameters to the next level.

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

    protected function setup()
    {
        $this->src("mydatabase")
        ->query("SELECT country, sum(amount) AS sale_amount FROM orders GROUP BY country")
        ->pipe($this-dataStore("sale_by_country"));
    }
}
<?php 
    //CountrySale.view.php
    use \koolreport\widgets\google\ColumnChart;
    $drilldown = $this->params["@drilldown"]
?>

<level-title>All Countries</level-title>

<?php
ColumnChart::create(array(
    "dataSource"=>$this->dataStore("sale_by_country"),
    "clientEvents"=>array(
        "itemSelect"=>"function(params){
            $drilldown.next({country:params.selectedRow[0]});
        }"
    )
))
?>

Step 2: Create StateSale report

The StateSale report is created in the same way that you create a single report. StateSale report will receive a parameter called "country" which is the selected country from user. It will summarize the sale_amount by state in that country.

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

    protected function setup()
    {
        $this->src("mydatabase")
        ->query("
            SELECT 
                state, sum(amount) AS sale_amount 
            FROM orders
            WHERE
                country=:country
            GROUP BY state
        ")
        ->params(array(
            ":country"=>$this->params["country"]
        ))
        ->pipe($this-dataStore("sale_by_state"));
    }
}
<?php 
    //CountrySale.view.php
    use \koolreport\widgets\google\ColumnChart;
    $drilldown = $this->params["@drilldown"]
?>

<level-title>States of <?php echo $this->params["country"]; ?></level-title>

<?php
ColumnChart::create(array(
    "dataSource"=>$this->dataStore("sale_by_state"),
))
?>

Step 3: Create MainReport

The MainReport defines the CountrySale and StateSale as subreports and create CustomDrillDown at the view.

<?php
class MainReport extends \koolreport\KoolReport
{
    use \koolreport\clients\Bootstrap;
    protected function settings()
    {
        return array(
            "subReports"=>array(
                "countrysale"=>CountrySale::class,
                "statesale"=>StateSale::class
            )
        );
    }

    protected function setup()
    {
    }
}
<?php 
    //MainReport.view.php
    use \koolreport\drilldown\CustomDrillDown;
?>

<html>
    <head>
        <title> Sale By Location</title>
    </head>
    <body>
        <?php
        CustomDrillDown::create(array(
            "name"=>"locationSale"
            "title"=>"Sale By Location",
            "subReports"=>array("countrysale","statesale"),
        ))
        ?>
        ```
    </body>
</html>

As result of above settings, now you have a drilldown report that will show all countries with the sales achived in each country. When user selects a particular country, the drilldown will show the sale_amount by each states in that country. Awesome, is it?

Each levels of drill-down report is handled by a sub report which virtually you can customize the interface in the way you want. All you need to do is to call next() function of drill down, sending all selected values then wait for drilldown to move to next level.

Properties

nametypedefaultdescription
namestring*Required Name of drill down
titlestringTitle that is showed on top of drill-down report.
subReportsarray*Required List of sub report name in level order
showLevelTitlebooltrueWhether title of each level is shown
btnBackmixedtrueBy default, the button Back will shown, give it value false you will off the Back button. This property can receive array() to customize cssClass and text of button "btnBack"=>array("text"=>"Go Back","class"=>"btn btn-default btn-warning")
cssmixedDefined css for the most important elements in the widgets, see the $css properties for more details.
panelStylestring"default"Set the panel style, accept value "default", "danger", "success", "info"
scopearray/functionAny params you want to included for sub report in form of associated array. You also may use a function and return associate array
clientEventsarrayRegister client-event, please see the Client Events for more details

Css Properties

nametypedefaultdescription
panelstringDefine css style for top panel
levelTitlestringDefine css style for section that holds titles of level
btnBackstringAdd css style for Back button
bodystringDefined css style for body

Client events

CustomDrillDown support following events:

namedescription
nextingFired when CustomDrillDown is preparing to go to next level. Return false to cancel action.
nextedFired when CustomDrillDown went to next level successfully.
backingFired when CustomDrillDown is going to go back to previous level. Return false to cancel action.
backedFired when CustomDrillDown went back to previous level
changedFired when CustomDrillDown changed level

Example

<?php
CustomDrillDown::create(array(
    ...
    "clientEvents"=>array(
        "nexting"=>"function(params){
            return false;//Cancel action
        }",
        "nexted"=>"function(params){
            console.log('Nexted to'+params.level);
        }",
    );
));
?>

MultiView

MultiView has another name called drill-through report. It let you see the same data but in different angles, different charts so that you can understand data better. In MultiView we setup several views, each view holds a different chart. All the views shares the same datasource.

Example

<?php
    MultiView::create(array(
        "name"=>"multiviewer",
        "dataSource"=>$this->dataStore("data"),
        "views"=>array(
            array(
                "handler"=>"<i class='fa fa-bar-chart'></i>",
                "widget"=>array(BarChart::class,array(
                    "name"=>"mychart",
                    "columns"=>array("year","amount")
                ))
            ),
            array(
                "handler"=>"<i class='fa fa-table'></i>",
                "widget"=>array(Table::class,array(
                    "columns"=>array("year","amount")
                ))
            ), 
        ),
    ));
    ?>           

As we can see in the example, we setup a MultiView called multiviewer with a datasource. We setup two views, one holds a BarChart and another one holds a Table. User can switch between the between views by using the handler.

Properties

nametypedefaultdescription
namestring*Required Name of the MultiView
titlestringThe title of MultiView that will appears on the top
templatestring"panel"Set the template of the MultiView, default value is "panel".
optionsarrayHolding the options available to specific template, please see more details at options properties.
viewsarrayAn array of views and its settings, each views will have handler and widget property. The handler will hold the text of handler and the widget define which widget to show in the view together with its settings. Please see above example.
clientEventsarrayRegister client events, please view Client Events for more details

Client events

DrillDown support following events:

namedescription
changingFired when multiview is going to change view. Return false to cancel action.
changedFired when multiview changed index

Example

<?php
MultiView::create(array(
    ...
    "clientEvents"=>array(
        "changing"=>"function(params){
            return false;//Cancel action
        }",
        "changed"=>"function(params){
            console.log('Change to'+params.index);
        }",
    );
));
?>

DrillThrough

DrillThrough is another name of MultiView widget. If you want to use the name DrillThrough for better understanding rather than MultiView, you can do so.

Showcases

  1. DrillDown
  2. CustomDrillDown
  3. MultiView/DrillThrough

Support

Please use our forum if you need support, by this way other people can benefit as well. If the support request need privacy, you may send email to us at support@koolreport.com.

User Reviews
(2)
Jhon Man
on Jan 31

This is recommended package to create drilldown dashboard, the setup is very intuitive.

Rajive P. Sood
on Feb 14

Amazing package! I purchase the KoolReport Pro because of this package.