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%",
));
?>