KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Need help with filter in PivotMatrix or PivotTable with Input Filters... #2648

Open dimbax opened this topic on on Apr 24, 2022 - 1 comments

dimbax commented on Apr 24, 2022

Hi, I try to make PivotTable(Matrix) with Input Filters... I use default reports settings, but my own DB. I see a clear PivotTable and FormControl with Project Selecter (contains right projects). After POST will be nothing to change... What i doIng wrong ?

For Example...

YearsCustomersCategories.php in examples/Pivot

<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);

require_once "../../../load.koolreport.php";
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;

class YearsMonthsCustomersCategories extends koolreport\KoolReport
{
    function settings()
    {
          $config = include "../../../config.php";
      //    var_dump($config);
        return array(
            "dataSources" => array(
                "dollarsales"=>$config["pay_calendar"] 
                ), 
        );
    }


function defaultParamValues()
    {
        return array(
            "customerNumber"=>0,
        );
    }

    function bindParamsToInputs()
    {
        return array(
            "customerNumber",
        );
    }

__And in ,view.php
__
    function setup()
    {

        $this->src('dollarsales')
        ->query("
            SELECT id, fullname
            FROM Dict_Anl
            WHERE anl_group = 'Проект'
            ORDER BY fullname
        ")
        ->pipe($this->dataStore("customers"));

        
        $this->src('dollarsales')
               ->query("SELECT amount as `dollar_sales`, 
        `Group`, 
        year_date as `orderYear`, 
        month_date as `orderMonth`, 
        name as `customerName`, 
        DictName 
        FROM prj_amounts_by_IncExp_by_Dict
        JOIN Dict_Anl
        ON prj_amounts_by_IncExp_by_Dict.prj = Dict_Anl.id
        WHERE prj_amounts_by_IncExp_by_Dict.prj=:customerNumber")->params(array("customerNumber"=>$this->params['customerNumber']))

                ->pipe(
                    new ColumnMeta(
                        array(
                        "dollar_sales"=>array(
                            'type' => 'number',
                            "prefix" => "RUB",
                                            ),
                            )
                                )
                        )
                ->pipe(
                    new Pivot(
                        array(
                        "dimensions"=>
                            array(
                                "column"=>"orderYear, orderMonth",
                                "row"=>"customerName, Group, DictName"
                                ),
                                "aggregates"=>array(
                                "sum"=>"dollar_sales",
                                "count"=>"dollar_sales"
                                )
                            )
                            )
                        )
        ->pipe($this->dataStore('sales'));  
    //var_dump($this);

    }
}
<?php
use \koolreport\pivot\widgets\PivotTable;
 use \koolreport\inputs\Select;

?>
    <div class="report-content">
       <div class="text-center">
        <h1>Отчёт по прибыли по проектами</h1>
        <p class="lead">
      
        </p>
      </div> 

<?php 
    $customerName = "";
    $this->dataStore("customers")->popStart();
    while($row = $this->dataStore("customers")->pop())
    {
        if($row["id"]==$this->params["customerNumber"])
        {
            $customerName =$row["fullname"];
            $customerNumber=$row["id"];
       }
    }
?>
<div class="report-content">
    <form method="post">
        <div class="text-center">
            <h1>Customer Orders</h1>
            <div class="row form-group">
                <div class="col-md-6 offset-md-3">
                    <?php
                    Select::create(array(
                        "name"=>"customerNumber",
                        "dataStore"=>$this->dataStore("customers"),
                        "dataBind"=>array(
                            "text"=>"fullname",
                            "value"=>"id",
                        ),
                        "attributes"=>array(
                            "class"=>"form-control"
                        )
                    ));
                    ?>
                </div>
            </div>
            <div class="form-group">
                <button class="btn btn-primary">Look up</button>
            </div>
        </div>
    </form>


Sebastian Morales commented on Apr 25, 2022

I think you have a small typo problem with your param key:

$this->src('dollarsales')
    ->query("SELECT amount as `dollar_sales`, ...
        WHERE prj_amounts_by_IncExp_by_Dict.prj=:customerNumber")
    // ->params(array("customerNumber"=>$this->params['customerNumber']))
       ->params(array(":customerNumber"=>$this->params['customerNumber'])) // replace "customerNumber" with ":customerNumber" 
    ...

Let us know how this works for you. Tks,

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
help needed

Pivot