Pivot table report rendering no data

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

稳重的铁链 · Stata ...· 2 月前 ·

风度翩翩的西装 · 数电实验仿真-基于74LS00和74LS20 ...· 2 月前 ·

爱搭讪的桔子 · 网络空间安全学院· 2 月前 ·

跑龙套的小笼包 · Access denied ask for ...· 3 月前 ·

傲视众生的白开水 · 火焰纹章主角大联盟,火焰纹章 ...· 4 月前 ·

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

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 $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',

Hi Anil,

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

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

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'));

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!
                                    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.
                                    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!
                                    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
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'));
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
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'));
                                    Yes results are coming now.
But how can we put these params s electable on view? following is NotesRepot.view.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
      $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',
                                    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.
                                    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
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
      /* 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',
      )); */
  <form id='form1' class="form-inline" method="post">
        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>
My setup file
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'));
                                    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
            If you feel that the supporter has done a good work, 
            you may send him some credit for his effort. 
            Any amount put into the tips box below is very appreciated!
            We love you!