KoolReport
  1. Home
  2. DrillDown
  3. How to create Custom Drill Down in PHP
`

Sale By Location

This example shows how to setup a CustomDrillDown report to see sale report by location.
Please click on the column of chart to go further down on details.

Sale By Location
    All countries

    Drill down is another most used type of report in which data is summarized in a overall level and only going to detail when needed. For example in above example, we summarize data by country then click into country, we will get detail sale information on states in that country.

    The example uses CustomDrillDown widget. Compared to DrillDown widget, the CustomDrillDown is more complexed and more difficulty to use. However, it is very powerful which allows you to customize everything. If in the DrillDown, we have to stick with a common SQL statement then in the CustomDrillDown we can have custom SQL statement per level. This also make drill-down report run faster in CustomDrillDown. Furthermore, in DrillDown widget there is only one visualization widget per levels. Not happened in CustomDrillDown since we can define different view with more than one widgets in each level. Basically, each level of CustomDrillDown is a full functional report defned as a subreport within a main one.

    More information of DrillDown package you may find here.

    <?php

require_once "../../../load.koolreport.php";
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();
    <?php

require_once "CountrySale.php";
require_once "CitySale.php";

class MyReport extends \koolreport\KoolReport
{
    use \koolreport\amazing\Theme;
    use \koolreport\core\SubReport;
    function settings()
    {
        return array(
            "subReports"=>array(
                "countrySale"=>CountrySale::class,
                "citySale"=>CitySale::class,
            )
        );
    }
}
    <div class="report-content">
    <script async defer 
        src="https://maps.googleapis.com/maps/api/js?key=AIzaSyCj8ahVQXoy8wHCAwoRWsUjPVmR5N3Qgko" 
        type="text/javascript">
    </script>
    <div class="text-center">
        <h1>Sale By Location</h1>
        <p class="lead">
        This example shows how to setup a <code>CustomDrillDown</code> report to see sale report
        by location.
        <br/>
        Please click on the column of chart to go further down on details.
        </p>
    </div>

    <?php
        \koolreport\drilldown\CustomDrillDown::create(array(
            "name"=>"saleLocation",
            "title"=>"Sale By Location",
            "subReports"=>array("countrySale","citySale"),
        ));
    ?> 
</div>

    <?php

class CountrySale 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 country, sum(amount) as sale_amount
            FROM
                payments
            JOIN
                customers
            ON
                customers.customerNumber = payments.customerNumber
            GROUP BY country
        ")
        ->pipe($this->dataStore("country_sale"));
    }
}
    <?php
    $drilldown = $this->params["@drilldown"];
?>
<level-title>All countries</level-title>
<?php
    \koolreport\widgets\google\GeoChart::create(array(
        "dataSource"=>$this->dataStore("country_sale"),
        "columns"=>array("country","sale_amount"=>array(
            "label"=>"Sales(USD)",
            "prefix"=>'$',
        )),
        "clientEvents"=>array(
            "rowSelect"=>"function(params){
                $drilldown.next({country:params.selectedRow[0]});
            }",
        ),
        "width"=>"100%",
    ));
?>
    <?php

class CitySale 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 city, sum(amount) as sale_amount
            FROM
                payments
            JOIN
                customers
            ON
                customers.customerNumber = payments.customerNumber
                AND
                country=:country
            GROUP BY 
                city
        ")
        ->params(array(
            ":country"=>$this->params["country"]
        ))
        ->pipe($this->dataStore("city_sale"));
    }
}
    <level-title><?php echo $this->params["country"]; ?></level-title>
<?php
\koolreport\widgets\google\BarChart::create(array(
    "dataSource"=>$this->dataStore("city_sale"),
    "columns"=>array(
        "city","sale_amount"=>array(
            "label"=>"Sales(USD)",
            "prefix"=>'$',
        )
    ),
    "width"=>"100%",
));
?>

    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