• Structure of DrillDown
• Level
  • title()
  • widget()
• Working example

DrillDown

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.

Structure of DrillDown #

<?php

use \koolreport\dashboard\widgets\drillDown\DrillDown;
use \koolreport\dashboard\widgets\drilldown\Level;

class MyDrillDown extends DrillDown
{
    protected function levels()
    {
        return [
            Level::create()
                ->title(...)
                ->widget(...),

            Level::create()
                ->title(...)
                ->widget(...),

            Level::create()
                ->title(...)
                ->widget(...),
        ];
    }
}

Above it is the basic structure of a drilldown, the levels() method returns a list of Level object representing each level of drilldown from broad to details.

Each Level will have title and widget definition. The title will accept string or anonymous function to define title of each level. And the widget also receive KWidget as parameter or a anonymous function to return the widget to be shown.

Level #

title() #

title property accept string or anonymous function to set title for each level of drilldown. Normally the first level will be text and the rest is based on anonymous function to customize title.

//First level
Level::create()
    ->title("Total Sales")
    ...

//Others
Level::create()
    ->title(function($params){
        return "Year " +$params["year"];
    })
    ...

widget() #

widget property accept KWidget or an anonymous function as parameters. If you use anonymous function, it will return KWidget object.

//Directly insert KWidget into widget()
Level::create()
    ->widget(
        KWidget::create()
        ->use(\koolreport\widgets\google\ColumnChart::class)
        ->settings([
            ...
        ])
        ->columns([
            ...
        ])
    )

//Use anonymous function, this way you will have flexibility to change widget
Level::create()
    ->widget(function(){
        return KWidget::create()
                ->use(\koolreport\widgets\google\ColumnChart::class)
                ->settings([
                    ...
                ])
                ->columns([
                    ...
                ]);
    })

Working example #

Below is a Drilldown working example that you can find in our demo. The example created a drilldown with 3 levels. First level show overview cashflow in 3 years. If user select a year, drilldown will show details of cashflow of each months in that year. And if user continue to select a month, it will break down that month into days with cashflow.

<?php

namespace demo\drilldown;

use \koolreport\dashboard\widgets\drilldown\DrillDown;
use \koolreport\dashboard\widgets\drilldown\Level;
use \koolreport\dashboard\widgets\KWidget;
use \koolreport\dashboard\ColorList;

use \demo\AutoMaker;

class DrillDownDemo extends DrillDown
{
    protected function levels()
    {
        return [
            //First level: Showing data of All Years
            Level::create()
                ->title("All Years")
                ->widget(
                    KWidget::create()
                    ->use(\koolreport\widgets\google\ColumnChart::class)
                    ->dataSource(function($params, $scope){
                        return AutoMaker::table("payments")
                                ->selectRaw("YEAR(paymentDate) as year")
                                ->sum("amount")->alias("saleAmount")
                                ->groupBy("year")->run();
                    })->columns([
                        "year"=>["type"=>"string"],
                        "saleAmount"=>[
                            "type"=>"number",
                            "prefix"=>'$'
                        ]
                    ])
                    ->settings([
                        "colorScheme"=>ColorList::random()
                    ])
                ),


            //Second level: Show each month sale for selected year
            Level::create()
                ->title(function($params){
                    return "Year ".$params["year"];
                })
                ->widget(
                    KWidget::create()
                    ->use(\koolreport\widgets\google\ColumnChart::class)
                    ->dataSource(function($params, $scope) {
                        return AutoMaker::table("payments")
                                ->selectRaw("MONTH(paymentDate) as month")
                                ->sum("amount")->alias("saleAmount")
                                ->groupBy("month")
                                ->whereRaw("YEAR(paymentDate)=".$params["year"])
                                ->run(); // run() is important
                    })
                    ->columns([
                        "month"=>[
                            "type"=>"string",
                            "formatValue"=>function($value) {
                                return date('M', mktime(0, 0, 0, $value, 10));
                            }
                        ],
                        "saleAmount"=>[
                            "type"=>"number",
                            "prefix"=>'$'
                        ]
                    ])
                    ->settings([
                        "colorScheme"=>ColorList::random()
                    ])
                ),


            // Third level: Showing chart for selected month
            Level::create()
                ->title(function($params) {
                    return date('F', mktime(0, 0, 0, $params["month"], 10));
                })
                ->widget(
                    KWidget::create()
                    ->use(\koolreport\widgets\google\ColumnChart::class)
                    ->dataSource(function($params,$scope){
                        return AutoMaker::table("payments")
                                ->select("paymentDate")
                                ->selectRaw("DAY(paymentDate) as day")
                                ->sum("amount")->alias("saleAmount")
                                ->whereRaw("YEAR(paymentDate)=".$params["year"])
                                ->whereRaw("MONTH(paymentDate)=".$params["month"])
                                ->groupBy("day")
                                ->run(); //run() is important
                    })
                    ->columns([
                        "day"=>[
                            "formatValue"=>function($value,$row) {
                                return date("F jS, Y",strtotime($row["paymentDate"]));
                            }
                        ],
                        "saleAmount"=>[
                            "type"=>"number",
                            "prefix"=>'$'
                        ]
                    ])
                    ->settings([
                        "colorScheme"=>ColorList::random()
                    ])
                )
        ];
    }
}