KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.

Partial Processing in Pivot Table Not Working #1616

Open David Vanacore opened this topic on on Sep 11 - 5 comments

David Vanacore commented on Sep 11

I'm having a issue with "partialProcessing" => true with a large pivot table and input filters. The initial search shows up properly, but when I try to expand it, it freezes. When I turn it off, everything works correctly, although very slowly.

Here's the .php <?php namespace App\Reports; use \koolreport\pivot\processes\Pivot; use \koolreport\datagrid\DataTables;

class Examples extends \koolreport\KoolReport {

use \koolreport\laravel\Friendship;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
use \koolreport\cache\FileCache;

function cacheSettings()
{
    return array(
        "ttl"=>60,
    );
}   

protected function defaultParamValues()
{
    return array(
        "startDate"=>NULL,
        "endDate"=>NULL,
        "Names"=>NULL,
        "countries"=>NULL,

    );
}

protected function bindParamsToInputs()
{
    return array(
        "startDate",
        "endDate",
        "Names",
        "countries",

    );
}

protected function setup()
{
    if(isset($this->params["startDate"]) && $this->params["startDate"]=="")
        {$this->params["startDate"] = null;}
    $startDate[":startDate"] = $this->params["startDate"];
    $this->src("mysql2")
    ->query("SET @startDate = :startDate")
    ->params([":startDate" => $startDate]);
    
    if(isset($this->params["endDate"]) && $this->params["endDate"]=="")
        {$this->params["endDate"] = null;}
    $endDate[":endDate"] = $this->params["endDate"];
    $this->src("mysql2")
    ->query("SET @endDate = :endDate")
    ->params([":endDate" => $endDate]);

    if(isset($this->params["Names"]) && $this->params["Names"]=="")
        {$this->params["Names"] = null;}
    $Names[":Names"] = $this->params["Names"];
    $this->src("mysql2")
    ->query("SET @Names = :Names")
    ->params([":Names" => $Names]);
    

    $this->src('mysql2')->query("SELECT
        YearQuarter,
        Name.Title AS Title,
        Country.Name AS Country,
        Income
        FROM Table1
        LEFT JOIN Name ON Name.__kp_Name_ID = Table1._kf_Name_ID
        LEFT JOIN Country ON Country.__kp_Country_ID = Table1._kf_Country_ID
        WHERE 1=1
            AND YearQuarter BETWEEN @startDate AND @endDate
            AND (@Names IS NULL OR _kf_Name_ID = @Names)

     ")
     ->pipe(new Pivot(array(
        "dimensions" => array(
            "column" => "YearQuarter",
            "row" => "Name"
        ),
        "aggregates"=>array(
            "sum" => "Income"
        ),
        "partialProcessing" => true    
        )))
    ->pipe($this->dataStore("examples"));
}

}

Here's the view.php file <?php

use \koolreport\pivot\widgets\PivotMatrix;
use \koolreport\inputs\Select2;

?> <div class="report-content">

<div class="text-center">
    <h1>Multiple Data Filters</h1>
    <p class="lead">
        The example demonstrate how to build dynamic reports with multiple data filters
    </p>
</div>
<form method="post">
<?php echo csrf_field() ?>
    <div class="row">
        <div class="col-md-6">
            <div class="form-group">
                <b>Select Year Quarters</b>
                <?php 
                Select2::create(array(
                    "multiple"=>false,
                    "name"=>"startDate",
                    "defaultOption"=>array("--"=>""),
                    "dataSource"=>$this->src("mysql2")->query("
                        SELECT YearQtr
                        FROM YearQtr
                        ORDER BY YearQtr DESC
                    "),
                    "attributes"=>array(
                        "class"=>"form-control"
                    )
                ));
                Select2::create(array(
                    "multiple"=>false,
                    "name"=>"endDate",
                    "defaultOption"=>array("--"=>""),
                    "dataSource"=>$this->src("mysql2")->query("
                        SELECT YearQtr
                        FROM YearQtr
                        ORDER BY YearQtr DESC
                    "),
                    "attributes"=>array(
                        "class"=>"form-control"
                    )
                ));
                ?>
                            <div class="form-group">
                <b>Select Names</b>
                <?php 
                Select2::create(array(
                    "multiple"=>false,
                    "name"=>"Names",
                    "defaultOption"=>array("--"=>""),
                    "dataSource"=>$this->src("mysql2")->query("
                        SELECT __kp_Name_ID,Title
                        FROM Name
                    "),
                    "dataBind"=>array(
                        "text"=>"Title",
                        "value"=>"__kp_Name_ID"),
                    "attributes"=>array(
                        "class"=>"form-control"
                    )
                ));
                ?>                
            </div>
            </div>
            <pre><code><?php echo json_encode($this->params,JSON_PRETTY_PRINT) ?></code></pre>
            </div>  
            <div class="form-group">
                <button class="btn btn-primary">Submit</button>
            </div>    
        </div>
    </div>
    
</form>
<?php

PivotMatrix::create(array(
    "dataStore"=>$this->dataStore('examples'),
    'rowCollapseLevels' => array(1),
    'paging' => array(
        'size' => 50,
        'maxDisplayedPages' => 10,
        'sizeSelect' => array(5, 10, 20, 50, 100)
    )
));
?>

</div>

David Winterburn commented on Sep 11

Do you happen to use PivotMatrix's partial processing inside a PHP framework like Laravel, Symphony or anything? Thanks!

David Vanacore commented on Sep 12

Yes, it's inside Laravel.

David Winterburn commented on Sep 14

When PivotMatrix is used with Pivot process' partial processing, it needs to send "post" request to get updated data if you expand a row/column header. Please make sure you add a "post" route for your page beside a "get" one. Otherwise, Laravel prevents your page from make post request to itself.

Let us know if this solves the issue or not. thanks!

David Vanacore commented on Sep 15

That makes sense. Is there an example of that process? I wasn't able to find one on your website. Thank you.

David Winterburn commented on Sep 16

Another thing to remember is to add Laravel's csrf token to PivotMatrix like described in this topic:

https://www.koolreport.com/forum/topics/456

or add the token to ajax request in general:

https://stackoverflow.com/questions/32738763/laravel-csrf-token-mismatch-for-ajax-post-request

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

None