KoolReport's Forum

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

Combine Input datepicker with pivot table data can't be displayed #1464

Open Ravi Chandran R opened this topic on on May 30, 2020 - 11 comments

Ravi Chandran R commented on May 30, 2020

Dear Sir,

how to implement INPUT DATE RANGE FILTER WITH PIVOT TABLE VIEW

kindly advice the below code...

ExReport.php

<?php

require_once "koolreport/core/autoload.php";

use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\TimeBucket;
use \koolreport\processes\CalculatedColumn;

class ExReport extends \koolreport\KoolReport
{
    
    use \koolreport\bootstrap4\Theme;


    protected function defaultParamValues()
    {
        return array(
            //"dateRange"=>array(date("Y-m-d"),date("Y-m-d")),
	"dateRange"=>array(date("Y-m-d 00:00:00"),date("Y-m-d 23:59:59")),      
            //),
        );
    }
    protected function bindParamsToInputs()
    {
        return array(
           
		"dateRange"=>"dateRange",
		            //"dateRange",
        );
    }


    public function settings()
    {
        return array(
            "dataSources"=>array(
                "Report"=>array(
                    "connectionString"=>"mysql:host=localhost;dbname=str_mgm",
                    "username"=>"root",
                    "password"=>"Tenda@24",
                    "charset"=>"utf8"
                ),
            )
        );
    }
//rtaken from no pivotMatrix1

protected function setup()
{
        $this->src("Report")
        ->query(" SELECT YEAR(Issue_Date) as orderYear, MONTH(Issue_Date) as OrderMonth, DepName, Product_Name, Total_Cost FROM Stores_Expenses_table WHERE Issue_Date BETWEEN :start AND :end ")
	
	->params(array(
            ":start"=>$this->params["dateRange"][0],
            ":end"=>$this->params["dateRange"][1],
            
         ))

         ->pipe(new ColumnMeta(array(
          "Total_Cost"=>array(
            'type' => 'number',
            "prefix" => ""
          ),
        )))
        ->pipe(new Pivot(array(
            "dimensions"=>array(
              "column" => "orderYear, OrderMonth",
              "row" => "L1, L2_NAME, L3_NAME, L4, L5",
            ),
            "aggregates"=>array(
              "sum"=>"Total_Cost",
            )
          )))
        ->pipe($this->dataStore('ExReport'));
    }
}

ExReport.view.php

<?php 
    //use \koolreport\widgets\koolphp\Table;
    use \koolreport\inputs\DateRangePicker;
    //use \koolreport\inputs\MultiSelect;
	use \koolreport\pivot\widgets\PivotMatrix;
        //use \koolreport\pivot\processes\Pivot;
	use \koolreport\pivot\widgets\PivotTable;

?>
<div class="report-content">
    <div class="text-center">
        <h1>List of order</h1>
        <p class="lead">Choose date ranges and customer to view orders</p>
    </div>
    <form method="post">
        <div class="row">
            <div class="col-md-8 offset-md-2">
               <div class="col-md-4 form-group text-center">
                <?php
                DateRangePicker::create(array(
		    "name"=>"dateRange",
		    //"format"=>"MMM DD, YYYY", //Jul 3rd, 2017
		    "ranges"=>array(
			"Today"=>DateRangePicker::today(),
			"Yesterday"=>DateRangePicker::yesterday(),
			"Last 7 days"=>DateRangePicker::last7days(),
			"Last 30 days"=>DateRangePicker::last30days(),
			"This month"=>DateRangePicker::thisMonth(),
			"Last month"=>DateRangePicker::lastMonth(),
		    )
		));
                ?>
                </div>

                <div class="form-group text-center">
                    <button class="btn btn-success"><i class="glyphicon glyphicon-refresh"></i> Load</button>
                </div>
            </div>
        </div>
    </form>


                <?php
	
                   // $dataStore = $this->dataStore('ExReport');
        PivotTable::create(array(
            "name" => "PivotTable2",
            "dataStore" => $this->dataStore("ExReport"),
            "rowDimension" => "row",
            "columnDimension" => "column",
            "measures"=>array(
                "Total_Cost - sum",
                'Total_Cost - count',
                'Total_Cost - avg',
            ),
            'rowSort' => array(
                'Total_Cost - sum' => 'desc',
            ),
            'columnSort' => array(
                'orderMonth' => function ($a, $b) {
                    return (int) $a < (int) $b;
                },
            ),
            'rowCollapseLevels' => array(0, 1),
            'columnCollapseLevels' => array(0),
            'width' => '100%',
            'map' => array(
                'rowHeader' => function($rowHeader, $headerInfo) {
                    $v = $rowHeader;
                    if (isset($headerInfo['childOrder']))
                        $v = $headerInfo['childOrder'] . ". " . $v;
                    return $v;
                },
                'columnHeader' => function($colHeader, $headerInfo) {
                    $v = $colHeader;
                    if ($headerInfo['fieldName'] === 'orderYear')
                        $v = 'Year-' . $v;
                    else if ($headerInfo['fieldName'] === 'orderQuarter')
                        $v = 'Quarter-' . $v;
                    if (isset($headerInfo['childOrder']))
                        $v = $headerInfo['childOrder'] . ". " . $v;
                    //return $v;
                },
            ),
            // 'showDataHeaders' => true,
        ));
                ?>
        </div>
Ravi Chandran R commented on May 30, 2020

Is same Date range picker view is not properly

David Winterburn commented on Jun 1, 2020

Please var_dump your params $this->params["dateRange"] to screen and replace your sql query with the param's values to see if it returns any data. Thanks!

Ravi Chandran R commented on Jun 1, 2020

Dear David,

below my code using var_dump param value

protected function setup() {

    $this->src("Report")
    ->query("SELECT YEAR(Issue_Date) as year, MONTH(Issue_Date) as month, DepName, Product_Name, Total_Cost FROM Stores_Expenses_table
        WHERE Issue_Date BETWEEN :start AND :end ")


->params(array(
        ":start"=>var_dump($this->params["dateRange"][0]),
        ":end"=>var_dump($this->params["dateRange"][1]),
        
     ))

     ->pipe(new ColumnMeta(array(
      "Total_Cost"=>array(
        'type' => 'number',
        "prefix" => ""
      ),
    )))
    ->pipe(new Pivot(array(
        "dimensions"=>array(
          "column" => "year, month",
          "row" => "L1, L2_NAME, L3_NAME, L4, L5",
        ),
        "aggregates"=>array(
          "sum"=>"Total_Cost",
        )
      )))
    ->pipe($this->dataStore('ExReport'));
}

kindly advice this...

David Winterburn commented on Jun 1, 2020

I meant to output the parameter "dateRange" to see its values, not to replace its values in function with var_dump.

var_dump($this->params['dateRange']);

$this->src("Report")
    ->query("SELECT YEAR(Issue_Date) as year, MONTH(Issue_Date) as month, DepName, Product_Name, Total_Cost FROM Stores_Expenses_table
        WHERE Issue_Date BETWEEN :start AND :end ")


->params(array(
        ":start"=>$this->params["dateRange"][0],
        ":end"=>$this->params["dateRange"][1],        
     )) 

Then you could see the parameter's values, copy and use them in your sql query in your database admin interface (such as phpmyadmin) to see if the query returns any data.

In your case, please try to see if this query returns any data:

SELECT YEAR(Issue_Date) as year, MONTH(Issue_Date) as month, DepName, Product_Name, Total_Cost FROM Stores_Expenses_table
        WHERE Issue_Date BETWEEN '2020-05-31 00:00:00' AND '2020-05-31 23:59:59' 

If the query returns no rows, don't expect the PivotTable to show any result.

Ravi Chandran R commented on Jun 1, 2020

Hi David,

Thanks for your valuable response now we have try above steps is not working can,t be show the data.But database phpmyadmin interface query display the data.

kindly advice the display param values in setup function.

David Winterburn commented on Jun 2, 2020

Hi Ravi,

Where are the fields L1, L2_NAME, L3_NAME, L4, L5 in your query? If you don't have them then set:

    ->pipe(new Pivot(array(
        "dimensions"=>array(
            ...
          "row" => "L1, L2_NAME, L3_NAME, L4, L5",
        ),

will result in no pivot result as well. Please try this:

->pipe(new Pivot(array(
        "dimensions"=>array(
          "column" => "year, month"
        ),
        "aggregates"=>array(
          "sum"=>"Total_Cost",
        )
      )))

to see if it returns any result. Thanks!

Ravi Chandran R commented on Jun 3, 2020

Dear David,

I'll try above method is not working only page refreshing pivot Table and pivot Matrix can't be display data.

David Winterburn commented on Jun 4, 2020

Hi Ravi,

Please try the following code and let us know the result:

//MyReport.php
$this->src("Report")
    ->query("SELECT YEAR(Issue_Date) as year, MONTH(Issue_Date) as month, DepName, Product_Name, Total_Cost FROM Stores_Expenses_table
        WHERE Issue_Date BETWEEN '2020-05-01' AND '2020-06-05' ")
     ->pipe(new ColumnMeta(array(
      "Total_Cost"=>array(
        'type' => 'number',
        "prefix" => ""
      ),
    )))
    ->pipe(new Pivot(array(
        "dimensions"=>array(
          "column" => "year, month"
        ),
        "aggregates"=>array(
          "sum"=>"Total_Cost",
        )
      )))
    ->pipe($this->dataStore('ExReport'));
} 
 
//MyReport.view.php
    PivotTable::create(array(
            "name" => "PivotTable2",
            "dataStore" => $this->dataStore("ExReport")
    );
Ravi Chandran R commented on Jun 4, 2020

Dear David,

Thanks for your quick response and above method is working i have get the data pivot table format. Thanks!

David Winterburn commented on Jun 4, 2020

Another point, if you want to use and bind inputs for a report, please add the following lines to the report:

//MyReport.php
class MyReport exports \koolreport\KoolReport
{
    use \koolreport\inputs\Bindable;
    use \koolreport\inputs\POSTBinding; //use POSTBinding if your form's method=post or GETBinding if your form's method=get
    ...
Ravi Chandran R commented on Jun 5, 2020

Dear David,

Noted Thanks.

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
solved

Pivot