DrillDown KWidget Passing Parameter To Level Second Error

afieq commented on Oct 24, 2023

Hi,

I encounter a problem where i created a Drilldown on my Dashboard. The error occur where i created a filter on my dashboard and i want to pass the parameter chosen by the filter and pass into my second level of DrillDown report. I can only successfully passed the parameter only on the first level and it works just fine, but when enter the second level of DrillDown Report the paramater "$range" does not passed and when i try test to output its return null value. Also how do i assigned the second parameter to my second level mysql query of the report? Is there a solution for my problem?


$range = $this->sibling("AgencyNameLookup")->value();
return [
Level::create()
                ->title("Agency ".$range)
                ->widget(
                    KWidget::create()
                    ->use(\koolreport\widgets\google\ColumnChart::class)
                    ->dataSource(function($params, $scope) use ($range) {
                        return MyTable::rawSQL(
                            str_replace(":AgencyName",$range,"
                            ..//my mysql statement
                            where agency.name = :AgencyName
                            ..//my mysql statement
                    "//)
                    )->run();
                    })->columns([
                        "Year"=>["type"=>"string"],
                        "Amount"=>[
                            "type"=>"number",
                            "prefix"=>'$
                        ]
                    ])
                    ->settings([
                        "colorScheme"=>ColorList::random()
                    ])
                ),
Level::create()
                ->title(function($parameter){
                    return "Year ".$parameter["Year"];
                })
                ->widget(
                    KWidget::create()
                    ->use(\koolreport\widgets\google\ColumnChart::class)
                    ->dataSource(function($params, $scope) {
                        return JohorPay::rawSQL(
                            str_replace(":Year",$params["Year"],"
                            ..//my mysql statement
                            where agency.name = :AgencyName
                            and year(order.created_date) = :Year
                            ..//my mysql statement
                            ")
                    )
                    ->run();
                    })
                    ->columns([
                        "Month"=>[
                            "type"=>"string",
                            "formatValue"=>function($value) {
                                return date('M', mktime(0, 0, 0, $value, 10));
                            }
                        ],
                        "Amount"=>[
                            "type"=>"number",
                            "prefix"=>'$'
                        ]
                    ])
                    ->settings([
                        "colorScheme"=>ColorList::random()
                    ])
                )
];

Thanks in advance.

Regards, Afieq

afieq commented on Oct 25, 2023

Hi,

Any help i can get for this issue?

Thanks in advance.

Regards, Afieq

Sebastian Morales commented on Oct 25, 2023

How about adding " use ($range) " to the second anonymous function in the 2nd level as well?

afieq commented on Oct 25, 2023

Hi Sebastian

I have tried adding the "use $range" to the second level but it return null when i tried output it on the screen, it seems the $range value on passed on the first level but when entering the second level the value is null. I just tested using the second option you mention, but it return error on my chart.

Any other suggestion for this problem?

Regards, Afieq

Sebastian Morales commented on Oct 25, 2023

That's a bit strange. I have just tested the DrillDown example in Dashboard demo with this:

protected function levels()
    {
        $range = 2;
        return [
            Level::create()
                ->title("All Years")
                ->widget(
                    KWidget::create()
                    ->use(\koolreport\widgets\google\ColumnChart::class)
                    ->dataSource(function($params, $scope) use ($range) {
                        return AutoMaker::table("payments")
                                ->selectRaw("YEAR(paymentDate) as year")
                                ->sum("amount")->alias("saleAmount")
                                ->groupBy("year")
                                ->limit($range)
                                ->run();
                    })->columns([
                    ...
                ),

            Level::create()
                ->title(function($params){
                    return "Year ".$params["year"];
                })
                ->widget(
                    KWidget::create()
                    ->use(\koolreport\widgets\google\ColumnChart::class)
                    ->dataSource(function($params, $scope) use ($range) {
                        return AutoMaker::table("payments")
                                ->selectRaw("MONTH(paymentDate) as month")
                                ->sum("amount")->alias("saleAmount")
                                ->groupBy("month")
                                ->whereRaw("YEAR(paymentDate)=".$params["year"])
                                ->limit($range)
                                ->run();
                    })
                    ...
                ),
            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) use ($range) {
                        return AutoMaker::table("payments")
                                ->max("paymentDate")->alias("thePaymentDate")
                                ->selectRaw("DAY(paymentDate) as day")
                                ->sum("amount")->alias("saleAmount")
                                ->whereRaw("YEAR(paymentDate)=".$params["year"])
                                ->whereRaw("MONTH(paymentDate)=".$params["month"])
                                ->groupBy("day")
                                ->limit($range)
                                ->run();
                    })

and the result is as expected with 2 rows of data returned at each level. Pls check your sql query and $params value in the 2nd level for sure.

afieq commented on Oct 25, 2023

Hi Sebastian,

I have check it return the same as before. I also tried follow you query by changing my query from rawSQL to using the select statement like you using and added the 'use $range'. But now it does not return error, but it return this.

But if i manually assign a value to the where statement its return result just fine but instead it return empty like the picture above.

Did i miss something on my code?

Regards, Afieq

Sebastian Morales commented on Oct 25, 2023

Can you pls verify that $range and $params values are what is expected in the 2nd level? One way to check that is setting Dashboard's debug mode = true and var_dump() those variables in the 2nd level:

//App.php
class App extends \koolreport\dashboard\Application
{
    protected function onCreated()
    {
        $this->debugMode(true)

afieq commented on Oct 25, 2023

Hi Sebastian,

For the $range it suppose to return string as its for agency name filtering and the $params is string. I have insert the debugMode you suggest and found out the $range return null, for the $params is working fine.

Regards, Afieq

Sebastian Morales commented on Oct 25, 2023

Thanks for your feedback. I guess the issue is because widget "AgencyNameLookup" state is not kept across DrillDown ajax request, thus in the 2nd DrillDown level its value is returned null. Since the "scope" property doesn't work right now, we can use another one of DrillDown's called "global" like this:

class DrillDownDemo extends DrillDown
{
    protected function onInit()
    {
        $this->settings([
            "global" => [
                "rangeParam" => $this->sibling("AgencyNameLookup")->value()
            ]
        ]);
        // all "global" values will be attached to DrillDown ajax request
    }

    protected function levels()
    {
        $range = $this->sibling("AgencyNameLookup")->value();
        return [
            Level::create()
                ->widget(
                    KWidget::create()
                    ->use(\koolreport\widgets\google\ColumnChart::class)
                    ->dataSource(function($params, $scope) use ($range) { // for 1st level, apply "use $range"
                        // echo "range level 1: $range<br>";
                        return ...; // use $range here
                    })
                    ...
                ),

            Level::create()
                ->widget(
                    KWidget::create()
                    ->use(\koolreport\widgets\google\ColumnChart::class)
                    ->dataSource(function($params, $scope) 
                    {
                        $range = $_POST["rangeParam"] ?? null; // for 2nd level, catch $range from DrillDown ajax request
                        // echo "range level 2: $range<br>";
                        return ...; // use $range here
                    })

In the next version of Dashboard's DrillDown we will make "scope" works like the KoolReport Pro one's so that we could have a simpler solution than this.

afieq commented on Oct 26, 2023

Hi Sebastian,

Thanks for your reply, I have tried it and its work like a charm after added the global variable like you stated. Thank you for your help on solving my issues. Noted, will looks forward on the next version of Dashboard's DrillDown.

Thanks & Regards, Afieq

KoolReport's Forum

DrillDown KWidget Passing Parameter To Level Second Error #3180

Build Your Excellent Data Report