We are trying to create a pivot matrix report with multiple columns that should be sortable but when we click on the sort asc/desc label of any list the page is going blank
-- code of report.php page is as below -- ...
$sql = "SELECT cfaname, productcode, productname,
ROUND(SUM(actualvalue)/100000,2) AS achievementvalue,
ROUND(SUM(targetvalue)/100000,2) AS targetvalue ,
SUM(actualvalue)/SUM(targetvalue) AS 'achievement%',
ROUND(SUM(lysmvalue)/100000,2) AS lysm,
SUM(actualvalue)/SUM(lysmvalue) AS 'growth%',
ROUND(SUM(ytd_actualvalue)/100000,2) AS Achievement_cumulative,
ROUND(SUM(ytd_targetvalue)/100000,2) AS Target_Cumulative,
SUM(ytd_actualvalue)/SUM(ytd_targetvalue) AS 'cumulative_achievement%',
ROUND(SUM(lysm_ytd_actualvalue)/100000,2) AS Lysm_Cumulative,
SUM(ytd_actualvalue)/SUM(lysm_ytd_actualvalue) AS 'Cumulative_Growth%'
FROM novokem_manual_data where 1=1
".(($this->params["divisionname"]!=array())?" and divisionname in (" . $strDivisionName . ")":"")."
".(($this->params["distributorname"]!=array())?"and distributorname in (" . $strdistributorname . ")":"")."
".(($this->params["productname"]!=array())?"and productname in (" . $strproductname . ")":"")."
".(($this->params["distributorcode"]!=array())?"and distributorcode in (" . $strdistributorcode . ")":"")."
".(($this->params["brandname"]!=array())?"and brandname in (" . $strbrandname . ")":"")."
".(($this->params["cfacode"]!=array())?"and cfacode in (" . $strcfacode . ")":"")."
".(($this->params["cfaname"]!=array())?"and cfaname in (" . $strcfaname . ")":"")."
".(($this->params["yearmonthid"]!= '')?"and yearmonthid in (" . $strYearMonthid . ")":"")."
GROUP BY cfaname, productcode, productname " . $strLimit;
$this->src('sakila_rental')
->query($sql)
->pipe(new ColumnMeta(array(
'achievementvalue'=>array(
'type' => 'number',
'decimals'=>2,
),
)))
->pipe(new ColumnMeta(array(
'targetvalue'=>array(
'type' => 'number',
'decimals'=>2,
),
)))
->pipe(new ColumnMeta(array(
'lysm'=>array(
'type' => 'number',
'decimals'=>2,
),
)))
->pipe(new ColumnMeta(array(
'Achievement_cumulative'=>array(
'type' => 'number',
'decimals'=>2,
),
)))
->pipe(new ColumnMeta(array(
'Target_Cumulative'=>array(
'type' => 'number',
'decimals'=>2,
),
)))
->pipe(new ColumnMeta(array(
'Lysm_Cumulative'=>array(
'type' => 'number',
'decimals'=>2,
),
)))
->pipe(new Pivot(array(
'dimensions' => array(
//'column' => 'achievementvalue',
'row' => 'cfaname, productname'
),
"aggregates" => array(
// "sum" => "suborder_qty, total_req", --> removed the total_req
'sum'=>'achievementvalue, achievement%, lysm,growth%, Achievement_cumulative, Target_Cumulative, cumulative_achievement%, Lysm_Cumulative, Cumulative_Growth%'
// 'sum'=>'achievementvalue'
),
//'partialProcessing' => true,
)))
->pipe($this->dataStore('sw_item_order_report'));
... ---- code end --
-- code of report.view.php page is as below --
`
<?php
//use \koolreport\pivot\widgets\PivotTable;
//use \koolreport\pivot\widgets\PivotMatrix;
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;
use \koolreport\inputs\Select2;
?> <?php
if (!empty($_POST)){
$strLimit = "";
$strdisplayForm = "";
}else{
$strLimit = " limit 0";
$strdisplayForm = "display: none;";
}
?> <div class='report-content'> <div class="">
<h1 class="title">Depot Sale Report</h1>
</div> <form method="post">
<div class="row">
<div class="col-md-6 col-sm-12">
<div class="form-group">
<b>Select Division</b>
<?php
Select2::create(array(
"multiple"=>true,
"name"=>"divisionname",
"dataSource"=>$this->src("sakila_rental")->query("
SELECT divisionname FROM novokem_manual_data GROUP BY divisionname
"),
"attributes"=>array(
"class"=>"form-control"
)
));
?>
</div>
</div>
<div class="col-md-6 col-sm-12">
<div class="form-group">
<b>Select Brandname</b>
<?php
Select2::create(array(
"multiple"=>true,
"name"=>"brandname",
"dataSource"=>$this->src("sakila_rental")->query("
select brandname
from novokem_manual_data
group by brandname
"),
"attributes"=>array(
"class"=>"form-control"
)
));
?>
</div>
</div>
<div class="col-md-6 col-sm-12">
<div class="form-group">
<b>Select Product</b>
<?php
Select2::create(array(
"multiple"=>true,
"name"=>"productname",
"dataSource"=>$this->src("sakila_rental")->query("
select productname
from novokem_manual_data
group by productname
"),
"attributes"=>array(
"class"=>"form-control"
)
));
?>
</div>
</div>
<div class="col-md-6 col-sm-12">
<div class="form-group">
<b>Select Distributor Code</b>
<?php
Select2::create(array(
"multiple"=>true,
"name"=>"distributorcode",
"dataSource"=>$this->src("sakila_rental")->query("
select distributorcode
from novokem_manual_data
group by distributorcode
"),
"attributes"=>array(
"class"=>"form-control"
)
));
?>
</div>
</div>
<div class="col-md-6 col-sm-12">
<div class="form-group">
<b>Select Distributor</b>
<?php
Select2::create(array(
"multiple"=>true,
"name"=>"distributorname",
"dataSource"=>$this->src("sakila_rental")->query("
select distributorname
from novokem_manual_data
group by distributorname
"),
"attributes"=>array(
"class"=>"form-control"
)
));
?>
</div>
</div>
<div class="col-md-6 col-sm-12">
<div class="form-group">
<b>Select CFA Code</b>
<?php
Select2::create(array(
"multiple"=>true,
"name"=>"cfacode",
"dataSource"=>$this->src("sakila_rental")->query("
select cfacode
from novokem_manual_data
group by cfacode
"),
"attributes"=>array(
"class"=>"form-control"
)
));
?>
</div>
</div>
<div class="col-md-6 col-sm-12">
<div class="form-group">
<b>Select CFA Name</b>
<?php
Select2::create(array(
"multiple"=>true,
"name"=>"cfaname",
"dataSource"=>$this->src("sakila_rental")->query("
select cfaname
from novokem_manual_data
group by cfaname
"),
"attributes"=>array(
"class"=>"form-control"
)
));
?>
</div>
</div>
<div class="col-md-6 col-sm-12">
<div class="form-group">
<b>Select Year Month</b>
<?php
Select2::create(array(
"name"=>"yearmonthid",
//"defaultOption"=>array(""=>""),
"dataSource"=>$this->src("sakila_rental")->query("
select yearmonthid
from novokem_manual_data
group by yearmonthid order by yearmonthid desc
"),
"attributes"=>array(
"class"=>"form-control"
)
));
?>
</div>
</div>
<div class="col-12">
<div class="form-group">
<button class="btn btn-primary">Submit</button>
</div>
</div>
<div class="col-12">
<div class="form-group">
<button type="submit" class="btn btn-primary" formaction="export.php">Download Excel</button>
</div>
</div>
</div>
</form>
</div>
<form id='form1' class="form-inline" method="post" style="<?php echo $strdisplayForm; ?>" >
<?php
$dataStore = $this->dataStore('sw_item_order_report');
PivotMatrix::create(array(
"id" => "pivotMatrix1",
'dataStore'=>$dataStore,
'width' => '100%', "rowDimension" => "row",
"columnDimension" => "column",
//'columnCollapseLevels' => array(0),
//'rowCollapseLevels' => array(0),
"measures"=>array(
"achievementvalue - sum",
"targetvalue - sum",
"achievement% - sum", "lysm - sum", "growth% - sum", "Achievement_cumulative - sum", "Target_Cumulative - sum", "cumulative_achievement% - sum", "Lysm_Cumulative - sum", "Cumulative_Growth% - sum"
// 'dollar_sales - avg',
),
"headerMap" => array( "cfaname" => "CFA Name", //"productcode" => "Product Code", "productname" => "Product Name", "achievementvalue - sum" => "Monthly Achievement", "targetvalue - sum" => "Monthly Target", "achievement% - sum" => "Monthly Achievement%", "lysm - sum" => "Monthly LYSM", "growth% - sum" => "Monthly Growth%", "Achievement_cumulative - sum" => "Cumulative Achievement", "Target_Cumulative - sum" => "Cumulative Target", "cumulative_achievement% - sum" => "Cumulative Achievement%", "Lysm_Cumulative - sum" => "Cumulative LYSM", "Cumulative_Growth% - sum" => "Cumulative Growth%",
), 'rowSort' => array(
'achievementvalue - sum' => 'desc',
'lysm - sum' => 'desc',
),
'paging' => array(
'size' => 20
),
'hideSubtotalRow' => true,
'hideSubtotalColumn' => true,
'showDataHeaders' => true,
));
?>
</form>
... --- code end --
can you please assist