Drill down report - KoolReport

michele commented on Mar 22, 2018

i'm using https://www.koolreport.com/examples/reports/drilldown/drilldown/index.php Setup with 2 fields as for the example, but i have this error:

Fatal error: Uncaught Error: Call to a member function format() on boolean in /var/www/vhosts/xxxxxxxxx/vendor/koolphp/koolreport/koolreport/processes/DateTimeFormat.php:71 Stack trace: #0 
/var/www/vhosts/xxxxxxxxx/vendor/koolphp/koolreport/koolreport/core/Node.php(92): koolreport\processes\DateTimeFormat->onInput(Array) #1 /var/www/vhosts/xxxxxxxxx/vendor/koolphp/koolreport/koolreport/core/Node.php(68): koolreport\core\Node->input(Array, Object(koolreport\processes\CopyColumn)) #2 /var/www/vhosts/xxxxxxxxx/vendor/koolphp/koolreport/koolreport/processes/CopyColumn.php(41): koolreport\core\Node->next(Array) #3 /var/www/vhosts/xxxxxxxxx/vendor/koolphp/koolreport/koolreport/core/Node.php(92): koolreport\processes\CopyColumn->onInput(Array) #4 /var/www/vhosts/xxxxxxxxx/vendor/koolphp/koolreport/koolreport/core/Node.php(68): koolreport\core\Node->input(Array, Object(koolreport\da in /var/www/vhosts/xxxxxxxxx/vendor/koolphp/koolreport/koolreport/processes/DateTimeFormat.php on line 71

KoolReport commented on Mar 22, 2018

Could you please check your datetime column if it contains null value or something which is not in datetime format.

If possible, please post your sample data and your code here.

michele commented on Mar 22, 2018

Great! NULL values was the problem. I noticed drilldown is not working on Safari: clicking on column does not change view. Last thing, i want to duplicate this report in order to display not the sales but the customers served per day, month, year. Is it possible?

small update: using chartJs also produce same error as per safari (not clickable chart)

KoolReport commented on Mar 22, 2018

Thank for letting us know. I will check Safari issue. And ofcourse, you can duplicate the report and change to number of customers. In the end, both are just number.

michele commented on Mar 22, 2018

trying to duplicate, for sales report query is: SELECT quantity_total_value, created_date FROM income_orders while to count customers is SELECT DISTINCT(customer_id) as customers, created_date FROM income_orders but this produce wrongs datas because customers are summed for every period

KoolReport commented on Mar 22, 2018

Could you please paste your code here

michele commented on Mar 22, 2018

<?php
DrillDown::create(array(
    "name"=>"saleDrillDown",
    "title"=>"Report Clienti",
    "btnBack"=>true,
    "dataSource"=>(
    $this->src('sales')
        ->query("SELECT customer_id, created_date FROM income_orders WHERE company_id=".CURRENT_LOGIN_COMPANY_ID)
        ->pipe(new CopyColumn(array(
            "year"=>"created_date",
            "month"=>"created_date",
            "day"=>"created_date",
        )))
        ->pipe(new Group(array(
            "by"=>"customer_id",
            "count"=>"customer_id",
        )))
        ->pipe(new DateTimeFormat(array(
            "year"=>"Y",
            "month"=>"m",
            "day"=>"d",
        )))
    ),
    "calculate"=>array(
        "sum"=>"customers"
    ),
    "levels"=>array(
        array(
            "groupBy"=>"year",
            "widget"=>array(ColumnChart::class,array(
                "columns"=>array("year","customer_id"=>array(
                    "label"=>"Incassi",
                    "prefix"=>'€'
                )),
                "colorScheme"=>array("#3b9b00"),
            )),
            "title"=>"All Years",
        ),
        array(
            "groupBy"=>"month",
            "widget"=>array(ColumnChart::class,array(
                "columns"=>array("month","customer_id"=>array(
                    "label"=>"Incassi",
                    "prefix"=>'€'
                )),
                "colorScheme"=>array("#af17b5"),
            )),
            "title"=>function($params)
            {
                return "Year ".$params["year"];
            },
        ),
        array(
            "groupBy"=>"day",
            "widget"=>array(ColumnChart::class,array(
                "columns"=>array("day","customer_id"=>array(
                    "label"=>"Incassi",
                    "prefix"=>'€'
                )),
                "colorScheme"=>array("#e0dc00"),
            )),
            "title"=>function($params)
            {
                return date('F', mktime(0, 0, 0, $params["month"], 10));
            },
        ),
    ),
));
?>

KoolReport commented on Mar 22, 2018

Sure, I will come back after few hours with solution.

michele commented on Mar 23, 2018

Hi, any update? Thanks

KoolReport commented on Mar 24, 2018

Sorry for my late reply. Your problem can be solved by double grouping. First we will group by created_date and customer_id to get distinct customer_id in distinct date. Then later in the process of KoolReport, we will group by created_date again and count the customer_id.

$this->src('sales')
->query("
    SELECT 
        customer_id, 
        created_date 
    FROM 
        income_orders
    WHERE
        company_id = :company_id
    GROUP BY
        created_date,
        customer_id
")
->params(array(
    ":company_id"=>CURRENT_LOGIN_COMPANY_ID
))
->pipe(new Group(array(
    "by"=>"created_date",
    "count"=>"customer_id"
)))
->pipe(new CopyColumn(array(
    "year"=>"created_date",
    "month"=>"created_date",
    "day"=>"created_date",
)))
->pipe(new DateTimeFormat(array(
    "year"=>"Y",
    "month"=>"m",
    "day"=>"d",
)))

So at the of above process, you will have "customer_id","Y","m","d" columns. The customer_id contains the number of distinct customers for Y-m-d date.

michele commented on Mar 24, 2018

Great, thank you very much!!!

KoolReport's Forum

Drill down report #233

Build Your Excellent Data Report