Drill Down Clustered Column Chart

shafiqah commented on Sep 22

Hi, I intend to create Clustered Column Chart that is able to Drill Down. How do I drill down to the specific bar (year & selected quarter)?

The code is as below

   Level::create()
       ->widget(
          KWidget::create()
          ->use(\koolreport\chartjs\ColumnChart::class)
          ->dataSource(
             function($params, $scope) use ($year){
               return ePNJ::rawSQL( "
                 SELECT 
                    YEAR(register_date) AS tahun,
                    SUM(CASE WHEN QUARTER(register_date) = 1 THEN 1 ELSE 0 END) AS 'quarter1',
                    SUM(CASE WHEN QUARTER(register_date) = 2 THEN 1 ELSE 0 END) AS 'quarter2',
                    SUM(CASE WHEN QUARTER(register_date) = 3 THEN 1 ELSE 0 END) AS 'quarter3',
                    SUM(CASE WHEN QUARTER(register_date) = 4 THEN 1 ELSE 0 END) AS 'quarter4'
                 FROM student
              ")->run();
            }
          )
          ->columns([
               "year"=>["label"=>"Year", "type"=>"string"],
               "quarter1"=>array("label"=>"First Quarter", "type"=>"number"),
               "quarter2"=>array("label"=>"Second Quarter", "type"=>"number"),
               "quarter3"=>array("label"=>"Third Quarter", "type"=>"number"),
               "quarter4"=>array("label"=>"Fourth Quarter", "type"=>"number")
           ])
           ->settings([
               "tooltip"=>array("use"=>"value")
           ]),
  Level::create()
    ->widget(
       KWidget::create()
       ->use(\koolreport\chartjs\ColumnChart::class)
       ->dataSource(
            function($params, $scope) use ($year){
                return ePNJ::rawSQL(
                "
                  SELECT 
                     class,
                     COUNT(student_id) AS total
                  FROM student
                  WHERE YEAR(register_date) = '".$params["year"]."'
                  AND QUARTER(register_date) = ??
                  GROUP BY class
              ")->run();
           }
        )
      ->columns([
          "class"=>["label"=>"Year", "type"=>"string"],
          "total"=>array("label"=>"Total", "type"=>"number")
      ])

Sebastian Morales commented on Sep 23

The current version of Drilldown package has not included column index parameter in a multi series chart yet. Pls send us an email to support@kooreport.com, we will send you a development version of Drilldown package for you to know which quarter was clicked when drilling down.

shafiqah commented on Sep 25

In order to drill down the selected column I need to include $params["itemParams"]["selectedColumnIndex"] .

However, I got error while I trying this,


   Level::create()
       ->widget(
          KWidget::create()
          ->use(\koolreport\chartjs\ColumnChart::class)
          ->dataSource(
             function($params, $scope) use ($year){
               return ePNJ::rawSQL( "
                 SELECT 
                    YEAR(register_date) AS year,
                    SUM(CASE WHEN QUARTER(register_date) = 1 THEN 1 ELSE 0 END) AS 'quarter1',
                    SUM(CASE WHEN QUARTER(register_date) = 2 THEN 1 ELSE 0 END) AS 'quarter2',
                    SUM(CASE WHEN QUARTER(register_date) = 3 THEN 1 ELSE 0 END) AS 'quarter3',
                    SUM(CASE WHEN QUARTER(register_date) = 4 THEN 1 ELSE 0 END) AS 'quarter4'
                 FROM student
                 GROUP BY YEAR(register_date)
                 ORDER BY YEAR(register_date)
              ")->run();
            }
          )
          ->columns([
               "year"=>["label"=>"Year", "type"=>"string"],
               "quarter1"=>array("label"=>"First Quarter", "type"=>"number"),
               "quarter2"=>array("label"=>"Second Quarter", "type"=>"number"),
               "quarter3"=>array("label"=>"Third Quarter", "type"=>"number"),
               "quarter4"=>array("label"=>"Fourth Quarter", "type"=>"number")
           ])
           ->settings([
               "tooltip"=>array("use"=>"value")
           ]),
  Level::create()
    ->widget(
       KWidget::create()
       ->use(\koolreport\chartjs\ColumnChart::class)
       ->dataSource(
            function($params, $scope) use ($year){
                return ePNJ::rawSQL(
                "
                  SELECT 
                     class,
                     COUNT(student_id) AS total
                  FROM student
                  WHERE YEAR(register_date) = '".$params["itemParams"]."'
                  AND QUARTER(register_date) = '".$params["itemParams"]["selectedColumnIndex"]."'
                  GROUP BY class
              ")->run();
           }
        )
      ->columns([
          "class"=>["label"=>"Year", "type"=>"string"],
          "total"=>array("label"=>"Total", "type"=>"number")
      ])

Hope you can assist me. Thank you

Sebastian Morales commented on Sep 25

Pls describe the error in detail and capture screenshots if possible.

shafiqah commented on Sep 26

It just pop out this message when I include the params

WHERE YEAR(register_date) = '".$params["itemParams"]."'
AND QUARTER(register_date) = '".$params["itemParams"]["selectedColumnIndex"]."'

Sebastian Morales commented on Sep 26

I think the problem is in this line:

    WHERE YEAR(register_date) = '".$params["itemParams"]."'

$params["itemParams"] is an array which can not be concatenated to a string, only is $params["itemParams"]["selectedColumnIndex"] a number. You should use $params["year"] here instead.

Here's an example value of the updated Drilldown's $params:

{
    "0":"2003",
    "1":"3250217",
    "2":"3250217",
    "year":"2003",
    "saleAmount":"3250217",
    "saleCount":"3250217",
    "itemParams":{"selectedRowIndex":"0","selectedColumnIndex":"2","selectedValue":"3250217","selectedRow":["2003","3250217","3250217"],"columnName":"saleCount"}
}

KoolReport's Forum

Drill Down Clustered Column Chart #3341

Build Your Excellent Data Report