KoolReport's Forum

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

Pivot table report rendering no data #1535

Open Anil Vithani opened this topic on on Jul 17, 2020 - 14 comments
Anil Vithani commented on Jul 17, 2020

Following is output

Following is dataset: around 204759 records total

-----NotesReport.php

<?php
namespace App\Reports;

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;


class NotesReport extends \koolreport\KoolReport
{
    use \koolreport\laravel\Friendship;
	
    // By adding above statement, you have claim the friendship between two frameworks
    // As a result, this report will be able to accessed all databases of Laravel
    // There are no need to define the settings() function anymore
    // while you can do so if you have other datasources rather than those
    // defined in Laravel.
    
    function setup()
    {

		$this->src('mysql')
			->query("SELECT exo.name, ct.team_name, nr.user_name, date(nr.created_date) as created_date,
					YEAR(nr.created_date)as year, MONTH(nr.created_date) as month, 
					DAY(nr.created_date) as day, QUARTER(nr.created_date)as quarter,
					SUM(IF(nr.notes_type = 126, 1, 0)) AS candidate_added
					
					FROM `notes_report`  nr
					LEFT JOIN company_teams ct ON nr.team_id = ct.id
					LEFT JOIN es_xero_organisation exo ON exo.id = nr.organisation_id
					GROUP BY exo.name, ct.team_name, nr.user_name, nr.created_date
                       ORDER BY exo.name, ct.team_name, nr.user_name
            ")
        ->pipe(new Filter(array(
            array('name', '<', 'Am'),
            array('year', '>', 2003),
        )))
        ->pipe(new ColumnMeta(array(
            "candidate_added"=>array(
                'type' => 'number',
                //"prefix" => "$",
            ),
        )))
        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "row" => "name, team_name, user_name",
            ),
            "aggregates"=>array(
                "sum"=>"candidate_added",
                "count"=>"candidate_added"
            )
        )))
        ->pipe($this->dataStore('pivot'));
    }
}

---- NotesReport.view.php

<?php
use \koolreport\pivot\widgets\PivotTable;
?>
<div class='report-content'>
  <div class="text-center">
    <h1>Sale Report</h1>
    <p class="lead">
      Summarize amount of sales and number of sales by three dimensions: customers, categories and products
    </p>
  </div>
  <div>
    <?php
      $dataStore = $this->dataStore('pivot');
      PivotTable::create(array(
        'dataStore'=>$dataStore,
        'rowDimension'=>'row',
        'measures'=>array(
          'candidate_added - sum', 
          'candidate_added - count',
        ),
        'rowSort' => array(
          'candidate_added - sum' => 'desc',
        ),
        'rowCollapseLevels' => array(1),
        'totalName' => 'All',
        'width' => '100%',
        'nameMap' => array(
          'candidate_added - sum' => 'Total Candidates Added',
          'candidate_added - count' => 'Number of Candidates',
        ),
      ));
    ?>
  </div>
  
</div>
pargibay commented on Jul 18, 2020

Hi Anil,

I think your filter is wrong, you are trying to filter the text with "<".

You should use "like" and "%". Try this:

<?php
namespace App\Reports;

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;


class NotesReport extends \koolreport\KoolReport
{
    use \koolreport\laravel\Friendship;
	
    // By adding above statement, you have claim the friendship between two frameworks
    // As a result, this report will be able to accessed all databases of Laravel
    // There are no need to define the settings() function anymore
    // while you can do so if you have other datasources rather than those
    // defined in Laravel.
    
    function setup()
    {

	$this->src('mysql')
	    ->query("SELECT exo.name, ct.team_name, nr.user_name, date(nr.created_date) as created_date,
			YEAR(nr.created_date) as year, MONTH(nr.created_date) as month, 
			DAY(nr.created_date) as day, QUARTER(nr.created_date)as quarter,
		        SUM(IF(nr.notes_type = 126, 1, 0)) AS candidate_added	
			FROM `notes_report`  nr
			LEFT JOIN company_teams ct ON nr.team_id = ct.id
			LEFT JOIN es_xero_organisation exo ON exo.id = nr.organisation_id
                        WHERE exo.name LIKE :name AND YEAR(nr.created_date) > :year
			GROUP BY exo.name, ct.team_name, nr.user_name, nr.created_date
                       ORDER BY exo.name, ct.team_name, nr.user_name
            ")
        ->params(array(
            ":name"=>"%Am%",
            ":year"=>"2003"
        ))
        ->pipe(new ColumnMeta(array(
            "candidate_added"=>array(
                'type' => 'number',
                //"prefix" => "$",
            ),
        )))
        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "row" => "name, team_name, user_name",
            ),
            "aggregates"=>array(
                "sum"=>"candidate_added",
                "count"=>"candidate_added"
            )
        )))
        ->pipe($this->dataStore('pivot'));
    }
}
David Winterburn commented on Jul 20, 2020

Hi Anil,

Please use the following command in report view to see if the pivot datastore has any data:

echo "pivot data = "; print_r($this->dataStore('pivot')->data());

Let us know the result. Thanks!

Anil Vithani commented on Jul 20, 2020

When I do exactly same as you suggested I get following error

PDOException SQLSTATE[HY000]: General error: 2031

and when I remove "WHERE exo.name LIKE :name AND YEAR(nr.created_date) > :year" from query then I get results displayed.

David Winterburn commented on Jul 20, 2020

Hi Anil,

Please copy your select query to phpmyadmin interface and replace the parameters with real values and see that it runs correctly. After that please copy the query back to the report and replace the real values with parameters use the real values in params() method.

If there's still any problem, please post your report's setup code and the error message. Thanks!

Anil Vithani commented on Jul 20, 2020

This query returns many results when running directly

SELECT exo.name, ct.team_name, nr.user_name, date(nr.created_date) as created_date,

		YEAR(nr.created_date) as year, MONTH(nr.created_date) as month, 
		DAY(nr.created_date) as day, QUARTER(nr.created_date)as quarter,
	        SUM(IF(nr.notes_type = 126, 1, 0)) AS candidate_added	
		FROM `notes_report`  nr
		LEFT JOIN company_teams ct ON nr.team_id = ct.id
		LEFT JOIN es_xero_organisation exo ON exo.id = nr.organisation_id
        WHERE exo.name LIKE "%Th%" AND YEAR(nr.created_date) > 2003
		GROUP BY exo.name, ct.team_name, nr.user_name, nr.created_date

But error when running from code

PDOException SQLSTATE[HY000]: General error: 2031

Cdoe is

<?php
namespace App\Reports;

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;


class NotesReport extends \koolreport\KoolReport
{
    use \koolreport\laravel\Friendship;
	
    // By adding above statement, you have claim the friendship between two frameworks
    // As a result, this report will be able to accessed all databases of Laravel
    // There are no need to define the settings() function anymore
    // while you can do so if you have other datasources rather than those
    // defined in Laravel.
    
    function setup()
    {

		$this->src('mysql')
			->query("SELECT exo.name, ct.team_name, nr.user_name, date(nr.created_date) as created_date,
			YEAR(nr.created_date) as year, MONTH(nr.created_date) as month, 
			DAY(nr.created_date) as day, QUARTER(nr.created_date)as quarter,
		        SUM(IF(nr.notes_type = 126, 1, 0)) AS candidate_added	
			FROM `notes_report`  nr
			LEFT JOIN company_teams ct ON nr.team_id = ct.id
			LEFT JOIN es_xero_organisation exo ON exo.id = nr.organisation_id
            WHERE exo.name LIKE :name AND YEAR(nr.created_date) > :year
			GROUP BY exo.name, ct.team_name, nr.user_name, nr.created_date
            ")
        ->pipe(new Filter(array(
            ":name"=>"%Th%",
            ":year"=>"2003"
        )))
        ->pipe(new ColumnMeta(array(
            "candidate_added"=>array(
                'type' => 'number',
                //"prefix" => "$",
            ),
        )))
        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "row" => "name, team_name, user_name",
            ),
            "aggregates"=>array(
                "sum"=>"candidate_added",
                "count"=>"candidate_added"
            )
        )))
        ->pipe($this->dataStore('pivot'));
    }
}
David Winterburn commented on Jul 20, 2020

Please replace these lines

->pipe(new Filter(array(
            ":name"=>"%Th%",
            ":year"=>"2003"
        )))

with these

->params(array(
            ":name"=>"%Th%",
            ":year"=>"2003"
        ))
Anil Vithani commented on Jul 20, 2020

another error

ErrorException
uksort() expects parameter 1 to be array, object given
David Winterburn commented on Jul 21, 2020

Please post your setup php code and full error message as well. Thanks!

Anil Vithani commented on Jul 21, 2020

ERROR: ` Illuminate\Foundation\Bootstrap\HandleExceptions::handleError vendor/koolreport/core/src/datasources/PdoDataSource.php:217

 *

 * @param string $query     Query need to bind params

 * @param array  $sqlParams The parameters will be bound to query

 *

 * @return string Procesed query

 */

protected function prepareParams($query, $sqlParams)

{

    if (empty($sqlParams)) {

        $sqlParams = [];

    }

    uksort(

        $sqlParams,

        function ($k1, $k2) {

            return strlen($k1) < strlen($k2);

        }

    );

    $resultQuery = $query;

    $paramNum = 0;

    foreach ($sqlParams as $paName => $paValue) {

        if (gettype($paValue)==="array") {

            $paramList = [];

            foreach ($paValue as $i=>$value) {

                // $paramList[] = $paName . "_param$i";

                $paramList[] = ":pdoParam$paramNum";

                $paramNum++;

            }

            $resultQuery = str_replace($paName, implode(",", $paramList), $resultQuery);

        }

    }

    return $resultQuery;

I am using it with laravel

NotesReport.php

<?php namespace App\Reports;

use \koolreport\KoolReport; use \koolreport\processes\Filter; use \koolreport\processes\ColumnMeta; use \koolreport\pivot\processes\Pivot;

class NotesReport extends \koolreport\KoolReport {

use \koolreport\laravel\Friendship;

// By adding above statement, you have claim the friendship between two frameworks
// As a result, this report will be able to accessed all databases of Laravel
// There are no need to define the settings() function anymore
// while you can do so if you have other datasources rather than those
// defined in Laravel.

function setup()
{

	$this->src('mysql')
		->query("SELECT exo.name, ct.team_name, nr.user_name, date(nr.created_date) as created_date,
		YEAR(nr.created_date) as year, MONTH(nr.created_date) as month, 
		DAY(nr.created_date) as day, QUARTER(nr.created_date)as quarter,
	        SUM(IF(nr.notes_type = 126, 1, 0)) AS candidate_added	
		FROM `notes_report`  nr
		LEFT JOIN company_teams ct ON nr.team_id = ct.id
		LEFT JOIN es_xero_organisation exo ON exo.id = nr.organisation_id
        WHERE exo.name LIKE :name AND YEAR(nr.created_date) > :year
		GROUP BY exo.name, ct.team_name, nr.user_name, nr.created_date
        ")
    ->params(new Filter(array(
        ":name"=>"%Th%",
        ":year"=>"2003"
    )))
    ->pipe(new ColumnMeta(array(
        "candidate_added"=>array(
            'type' => 'number',
            //"prefix" => "$",
        ),
    )))
    ->pipe(new Pivot(array(
        "dimensions"=>array(
            "row" => "name, team_name, user_name",
        ),
        "aggregates"=>array(
            "sum"=>"candidate_added",
            "count"=>"candidate_added"
        )
    )))
    ->pipe($this->dataStore('pivot'));
}

} `

David Winterburn commented on Jul 21, 2020

Didn't I ask you to replace these lines:

    ->params(new Filter(array(
        ":name"=>"%Th%",
        ":year"=>"2003"
    )))

with these:

        ->params(array(
            ":name"=>"%Th%",
            ":year"=>"2003"
        ))

Filter object is used in pipe() method while params() method uses array.

Anil Vithani commented on Jul 21, 2020

Yes results are coming now.

But how can we put these params s electable on view? following is NotesRepot.view.php

<?php
use \koolreport\pivot\widgets\PivotTable;
?>
<div class='report-content'>
  <div class="text-center">
    <h1>Sale Report</h1>
    <p class="lead">
      Summarize amount of sales and number of sales by three dimensions: customers, categories and products
    </p>
  </div>
  <div>
    <?php
    
    
    
      $dataStore = $this->dataStore('pivot');
      PivotTable::create(array(
        'dataStore'=>$dataStore,
        'rowDimension'=>'row',
        'measures'=>array(
          'candidate_added - sum', 
          'candidate_added - count',
        ),
        'rowSort' => array(
          'candidate_added - sum' => 'desc',
        ),
        'rowCollapseLevels' => array(1),
        'totalName' => 'All',
        'width' => '100%',
        'nameMap' => array(
          'candidate_added - sum' => 'Total Candidates Added',
          'candidate_added - count' => 'Number of Candidates',
        ),
      ));
    ?>
  </div>
  
</div>
David Winterburn commented on Jul 21, 2020

Check out our Input controls:

https://www.koolreport.com/examples/reports/inputs/intro/

https://www.koolreport.com/docs/inputs/first_things/

Add the input controls to the view page, catch them in the setup page and use them as parameters for your select query.

Anil Vithani commented on Jul 22, 2020

Tried to create PivotMatrix but year column is not coming through as shown in https://www.koolreport.com/examples/reports/pivot/pivotmatrix/#page-1

My view file

<?php
use \koolreport\pivot\widgets\PivotTable;
use \koolreport\pivot\widgets\PivotMatrix;
use \koolreport\core\Utility;
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\instant\Widget;
use \koolreport\datasources\CSVDataSource;
?>
<div class='report-content'>
  <div class="text-center">
    <h1>Sale Report</h1>
    <p class="lead">
      Summarize amount of sales and number of sales by three dimensions: customers, categories and products
    </p>
  </div>
  <div>
    <?php
    
      /* PivotTable::create(array(
          'dataStore'=>$this->dataStore('pivot'),
        'rowDimension'=>'row',
        'measures'=>array(
          'candidate_added - sum', 
          'candidate_added - count',
        ),
        'rowSort' => array(
          'candidate_added - sum' => 'desc',
        ),
        'rowCollapseLevels' => array(1),
        'totalName' => 'All',
        'width' => '100%',
        'nameMap' => array(
          'candidate_added - sum' => 'Total Candidates Added',
          'candidate_added - count' => 'Number of Candidates',
        ),
      )); */
    ?>
  </div>
  
  <form id='form1' class="form-inline" method="post">

        <?php

        PivotMatrix::create(array(
            "id" => "pivotMatrix1",
            'dataSource' => $this->dataStore('pivot'),
            "measures"=>array(
                "candidate_added - sum", 
            ),
            'rowSort' => array(
                'candidate_added - sum' => 'desc',
                'user_name' => 'desc',
            ),
            'columnSort' => array(
                'month' => function($a, $b) {
                    return (int)$a < (int)$b;
                },
            ),
            'columnCollapseLevels' => array(0),
            'rowCollapseLevels' => array(0),
            'width' => '100%',
            'height' => '500px',
            'headerMap' => function($v, $f) {
                switch ($v) {
                    case 'candidate_added - sum': return 'Total Candiates Added';
                    case 'candidate_added - count': return 'Number of Canidate Added';
                    case 'candidate_added - avg': return 'Average Candiate Added';
                    case 'year': return 'Year';
                    case 'month': return 'Month';
                    case 'day': return 'Day';
                    case 'name': return 'Org';
                    case 'team_name': return 'Team';
                    case 'user_name': return 'User';
                    
                }
                $r = $v;
                if ($f === 'year')
                    $r = 'Year ' . $v;
                $map = array(
                    '1' => 'January',
                    '2' => 'February',
                    '3' => 'March',
                    '4' => 'April',
                    '5' => 'May',
                    '6' => 'June',
                    '7' => 'July',
                    '8' => 'August',
                    '9' => 'September',
                    '10' => 'October',
                    '11' => 'November',
                    '12' => 'December',
                );
                if ($f === 'month')
                    $r = $map[$v];
                return $r;
            },
            'totalName' => 'All',
            'waitingFields' => array(
                'candidate_added - count' => 'data', 
                'month' => 'label',
                'day' => 'label',
            ),
            'paging' => array(
                'size' => 5,
                'maxDisplayedPages' => 5,
                'sizeSelect' => array(5, 10, 20, 50, 100)
            )
        ));
        ?>
    </form>
  
</div>

My setup file

<?php
namespace App\Reports;

use \koolreport\KoolReport;
use \koolreport\processes\Filter;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;


class NotesReport extends \koolreport\KoolReport
{
    use \koolreport\laravel\Friendship;
	
    // By adding above statement, you have claim the friendship between two frameworks
    // As a result, this report will be able to accessed all databases of Laravel
    // There are no need to define the settings() function anymore
    // while you can do so if you have other datasources rather than those
    // defined in Laravel.
    
    function setup()
    {

		$this->src('mysql')
			->query("SELECT exo.name, ct.team_name, nr.user_name, date(nr.created_date) as created_date,
			YEAR(nr.created_date) as year, MONTH(nr.created_date) as month, 
			DAY(nr.created_date) as day, QUARTER(nr.created_date)as quarter,
		        SUM(IF(nr.notes_type = 126, 1, 0)) AS candidate_added	
			FROM `notes_report`  nr
			LEFT JOIN company_teams ct ON nr.team_id = ct.id
			LEFT JOIN es_xero_organisation exo ON exo.id = nr.organisation_id
            
			GROUP BY exo.name, ct.team_name, nr.user_name, nr.created_date
            ")
            ->pipe(new Filter(array(
                //array('name', 'startsWith', 'a'),
                array('created_date', '>', 2003),
            )))
        /* ->params(array(
         * WHERE exo.name LIKE :name AND YEAR(nr.created_date) > :year
            ":name"=>"%Th%",
            ":year"=>"2003"
        )) */
        ->pipe(new ColumnMeta(array(
            "candidate_added"=>array(
                'type' => 'number',
                //"prefix" => "$",
            ),
        )))
        ->pipe(new Pivot(array(
            "dimensions"=>array(
                "row" => "name, team_name, user_name",
            ),
            "aggregates"=>array(
                "sum"=>"candidate_added",
                "count"=>"candidate_added"
            )
        )))
        ->pipe($this->dataStore('pivot'));
    }
}
David Winterburn commented on Jul 27, 2020

Please add the field "year" to the "column" dimension in Pivot process

->pipe(new Pivot(array(
            "dimensions"=>array(
                "row" => "name, team_name, user_name",
                //add "column" => "year" here
            ),

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

Designed and built with by KoolPHP Inc.

Code licensed MIT.