Pivot w/ Laravel. - KoolReport

Brian R. Parrish commented on Sep 21, 2018

I am able to do Table reports, but I can't get a pivot to work. The below query I am able to run & see all columns on a table view, however when I convert it to a PivotTable - I get errors

1). If I define an aggregates in the pivot with any column name, I get this error. I have tried every column & both count & sum. The columns are valid & display in a standard table.

->pipe(new Pivot(array(
                    	"dimensions" => array(
                    	    "row" => "ClientName, CourtName, DateStat",
						),
                    	"aggregates"=>array(
							"count" => "FileNo"
						)
					)))

local.ERROR: Invalid argument supplied for foreach()__ (View: *********/views/report.blade.php) {"userId":1,"email":"*****@**********","exception":"[object] (Er
rorException(code: 0): Invalid argument supplied for foreach() (View: *******/resources/views/report.blade.php) at ******/vendor/koolphp/koolreport/koolreport/
packages/pivot/processes/Pivot.php:424, ErrorException(code: 0): Invalid argument supplied for foreach() at ******/vendor/koolphp/koolreport/koolreport/packages/pivot/processes/Pivot.php:424)
[stacktrace]

2). If I don't define an aggregates, the page loads but appears blank. Looking at the source, I can see the table is not complete and "hidden".

3). I tried setting the aggregates to a "count" => 1 just to see if a constant would work, that gets a different error. Maybe related to the unintended use of a literal, but I was just trying to get around #1...

local.ERROR: Undefined index: columns   (/vendor/koolphp/koolreport/koolreport/packages/pivot/widgets/PivotTable.php:95)

---- Code ----

Report1.php

<?php
namespace App\Reports;

require_once dirname(__FILE__)."/../../vendor/koolreport/autoload.php";

use Auth;
use \koolreport\pivot\processes\Pivot;
use \koolreport\processes\ColumnMeta;
use App\User_ForClients;

class Report1 extends \koolreport\KoolReport
{
    use \koolreport\laravel\Friendship;
    
    function setup()
    {
	    $_StatusID = "3";	
	    $ListClientIDs = "45,2,32,41,67,21,14"
	    
		$sql  = "select F.FileNo, F.ClientID, U.CourtST, U.CourtName, U.CourtID, C.ClientST, C.ClientName, DateRecd, DateStat, DateDiff(now(),DateStat) as Age, CurrentStatus ";
		$sql .= "from tbl_File F   ";
		$sql .= "join tbl_Court U on U.CourtID=F.CourtID  ";
		$sql .= "join tbl_Client C on C.ClientID=F.ClientID ";
		$sql .= "where F.ClientID in (" . $ListClientIDs . ") ";
		$sql .= "having DateStat is not null and CurrentStatus=" . $_StatusID;
	    	
	    try
        {				
			$node = $this->src('mysql')
        			->query($sql)
        			->pipe(new ColumnMeta(array(
                    	"FileNo" => array(
                        	'label' => 'PaperID',
							'type'  => 'string',
						),
					)))        			
					->pipe(new Pivot(array(
                    	"dimensions" => array(
                    	    "row" => "ClientName, CourtName, DateStat",
						),
                    	"aggregates"=>array(
							"count" => 1
						)
					)))
					->pipe($this->dataStore('rpt'));
		}
		catch (\Exception $e)
        {
            error_log($e->getMessage(), $e->getCode());
        }
    }

}

Report1.view.php

<?php
use \koolreport\processes\DateTimeFormat;
use \koolreport\pivot\widgets\PivotTable;
    
?>
<html>
    <body>
        <h1>Out to Court</h1>
        <?php
	       
          PivotTable::create(array(
            'dataStore'=>$this->dataStore('rpt'),
            'rowDimension'=>'row',
            'rowCollapseLevels' => array(0),
            'rowSort' => array(
              'ClientName' => 'asc',
            ),
            'measures'=>array(
				'FileNo',
				'Age',
			),
            'width' => '100%',
            'hideTotalColumn' => true,
          ));
        ?>
    </body>
</html>

KoolReport commented on Sep 22, 2018

I have told dev.team to investigate the case. I will come back to you soon.

David Winterburn commented on Sep 24, 2018

Hi Brian,

Please change your PivotTable setup in the view file from:

          PivotTable::create(array(
            ...
            'measures'=>array(
				'FileNo',
				'Age',
			),
            ...
          ));

to:

          PivotTable::create(array(
            ...
            'measures'=>array(
				'FileNo - count',
			),
            ...
          ));

Because in the report's setup with Pivot process, a user could define both aggregate operators: "sum" => "FileNo" and "count" => "FileNo" so in the PivotTable widget's create, one must specify which aggregates to use: "FileNo - sum" or "FileNo - count" or both.

Also remember to set the report's setup's asset's path property to your Laravel public folder for PivotTable's js file to load and work correctly:

	function settings()
	{
		return array(
			'assets' => array(
				'path' => public_path(), //using public path or one of its subfolder
				'url' => '',
			),
		);
	}

Please let us know if there's still problem with using Pivot. Thanks!

KoolReport's Forum

Pivot w/ Laravel. #477

Build Your Excellent Data Report