KoolReport's Forum

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

Pivot matrix column format #2705

Closed Edp Ferrino opened this topic on on Jun 5, 2022 - 23 comments
Edp Ferrino commented on Jun 5, 2022

Hi everyone, I have a pivot matrix with a column that contains a numeric value and I would like to make sure that the decimal separator was the comma and the thousands separator was the period. How can I do? Also how can I center the value in the cell with the column titles?

A thousand thanks

Sebastian Morales commented on Jun 6, 2022

For number format you could set your pivot aggregated column meta like this:

->pipe(new ColumnMeta(array(
    "salesNum" => array(
        "type" => "number",
        "decimalPoint" => ",",
        "thousandSeparator" => "."
    )
)))
->pipe(new Pivot(array(
    "aggregates"=>array(
        "sum"=>"salesNum"
    )
)))

For centering column cells pls inspecting those cells, see their CSS classes and use CSS rule to center their text (such as text-align: center)

Edp Ferrino commented on Jun 6, 2022

This is my code. But it does not work. The data is correct, but the formatting remains the standard one.

	protected function setup()
	{
    	$sql      	= "";
    	$sql_core 	= $this->GetSQLFatturato(); 
    	$parametri 	= array(":data_inizio" => $this->data_inizio, ':data_fine' => $this->data_fine);
    	$sql = "select a.NAZIONE, a.ANNO, a.PEZZI, a.FATTURATO, a.GRUPPOFAM, a.FAMIGLIA from (" . $sql_core . ") a";
    
        $this->src('dati_produzione')
    		->query($sql)
    		->params($parametri)
        	->pipe(new ColumnMeta(array(
				"FATTURATO" => array(
    				"type" => "number",
    				"decimalPoint" => ",",
    				"thousandSeparator" => "."
				)
			)))
			->pipe(new Pivot(array(
    			"dimensions" => array(
    			"row" => "NAZIONE, GRUPPOFAM, FAMIGLIA",
        		"column" => "ANNO"
    		),
    		"aggregates"=>array(
				"sum" => "PEZZI, FATTURATO", 
    		)
    	)))     
      	->pipe($this->dataStore('my_pivot'));  
	}
Sebastian Morales commented on Jun 6, 2022

Can you pls print out your pivot datastore's meta in report view and let us know the result:

//MyReport.view.php
print_r($this->dataStore('my_pivot')->meta()["columns"]);
...
Edp Ferrino commented on Jun 6, 2022

Array ( [row] => Array ( [type] => dimension [index] => Array ( [0] => Array ( [NAZIONE] => {{all}} [GRUPPOFAM] => {{all}} [FAMIGLIA] => {{all}} ) [1] => Array ( [NAZIONE] => ITALIA [GRUPPOFAM] => {{all}} [FAMIGLIA] => {{all}} ) [2] => Array ( [NAZIONE] => ITALIA [GRUPPOFAM] => 01 - ACCESSORI [FAMIGLIA] => {{all}} ) [3] => Array ( [NAZIONE] => ITALIA [GRUPPOFAM] => 01 - ACCESSORI [FAMIGLIA] => ARW - ACCESSORI RAINWEAR ) [4] => Array ( [NAZIONE] => ITALIA [GRUPPOFAM] => 01 - ACCESSORI [FAMIGLIA] => AN - ACCESSORI NOTTE ) [5] => Array ( [NAZIONE] => ITALIA [GRUPPOFAM] => 01 - ACCESSORI [FAMIGLIA] => AI - ACCESSORI MOUNTAINEERING ) [6] => Array ( [NAZIONE] => ITALIA [GRUPPOFAM] => 01 - ACCESSORI [FAMIGLIA] => BO - BORRACCE ) [7] => Array ( [NAZIONE] => ITALIA [GRUPPOFAM] => 12 - ABBIGLIAMENTO [FAMIGLIA] => {{all}} ) [8] => Array ( [NAZIONE] => ITALIA [GRUPPOFAM] => 12 - ABBIGLIAMENTO [FAMIGLIA] => ABH - ABBIGLIAMENTO HIGH LAB ) [9] => Array ( [NAZIONE] => ITALIA [GRUPPOFAM] => 01 - ACCESSORI [FAMIGLIA] => AT - ACCESSORI TREKKING ) ) ) [column] => Array ( [type] => dimension [index] => Array ( [0] => Array ( [ANNO] => {{all}} ) [1] => Array ( [ANNO] => 2022 ) ) ) )

Edp Ferrino commented on Jun 6, 2022

Sebastian Morales commented on Jun 6, 2022

I can't see any meta for column "FATTURATO". Pls try this:

//MyReport.view.php
\koolreport\core\Utility::prettyPrint($this->dataStore("my_pivot")->meta());
...
Edp Ferrino commented on Jun 6, 2022

{

"pivotId": 0,
"pivotRows": [
    "NAZIONE",
    "GRUPPOFAM",
    "FAMIGLIA"
],
"pivotColumns": [
    "ANNO"
],
"pivotAggregates": {
    "PEZZI": [
        "sum"
    ],
    "FATTURATO": [
        "sum"
    ]
},

...

}

Sebastian Morales commented on Jun 6, 2022

Pls post full result.

Edp Ferrino commented on Jun 6, 2022 
{
    "pivotId": 0,
    "pivotRows": [
        "NAZIONE",
        "GRUPPOFAM",
        "FAMIGLIA"
    ],
    "pivotColumns": [
        "ANNO"
    ],
    "pivotAggregates": {
        "PEZZI": [
            "sum"
        ],
        "FATTURATO": [
            "sum"
        ]
    },
   ....
Sebastian Morales commented on Jun 6, 2022

May I know which version of Pivot package you are using? Is it possible for you to update to a newer one?

Edp Ferrino commented on Jun 6, 2022

koolreport_pro_5.16.2

Sebastian Morales commented on Jun 6, 2022

Pls post your report's settings() method for us to check your datasource class.

Edp Ferrino commented on Jun 6, 2022

This is my code

<?php

if (basename(__FILE__)==basename($_SERVER['SCRIPT_FILENAME']))
{
	echo "Il file: " . __FILE__ . " deve essere inglobato in altri file.\n";
	die();
}
ini_set('memory_limit', '500M');

include_once("mylibrarydir/koolreport_qx_oracle.php");

use \koolreport\widgets\koolphp\Table;
use \koolreport\datagrid\DataTables;
use \koolreport\widgets\google\ColumnChart;
use \koolreport\widgets\google\DonutChart;
use \koolreport\widgets\google\GeoChart;

use \koolreport\cube\processes\Cube;
use \koolreport\processes\ColumnMeta;
use \koolreport\pivot\processes\Pivot;
use \koolreport\pivot\widgets\PivotMatrix;

class MyReport extends Report_db_connection
{
	use \koolreport\export\Exportable;
	use \koolreport\excel\ExcelExportable;

use \koolreport\excel\BigSpreadsheetExportable; 

	private $data_inizio = 0;
	private $data_fine 	 = 0;

	function __construct($da_data, $a_data) {
    	$this->data_inizio	= $da_data;
    	$this->data_fine 	= $a_data;    
    	parent::__construct();
	}    

protected function setup()
{
	$sql      	= "";
	$sql_core 	= $this->GetSQLFatturato(); 
	$parametri 	= array(":data_inizio" => $this->data_inizio, ':data_fine' => $this->data_fine);
	$sql = "select a.NAZIONE, a.ANNO, a.PEZZI, a.FATTURATO, a.GRUPPOFAM, a.FAMIGLIA from (" . $sql_core . ") a";

    $this->src('dati_produzione')
		->query($sql)
		->params($parametri)
    	->pipe(new ColumnMeta(array(
			"FATTURATO" => array(
				"type" => "number",
				"decimalPoint" => ",",
				"thousandSeparator" => "."
			)
		)))
		->pipe(new Pivot(array(
			"dimensions" => array(
			"row" => "NAZIONE, GRUPPOFAM, FAMIGLIA",
    		"column" => "ANNO"
		),
		"aggregates"=>array(
			"sum" => "PEZZI, FATTURATO", 
		)
	)))     
  	->pipe($this->dataStore('my_pivot'));  
}

	public function ShowTable()
    {
				PivotMatrix::create(array(
                	"name" => "MyPivotMatrix1",
    				"dataStore"=>$this->dataStore('my_pivot'),
                	"totalName" => 'Totale',
                	'showDataHeaders' => true,
        			'columnCollapseLevels' => array(0),
        			'rowCollapseLevels' => array(0),
    				"cssClass"=>array(
        				"table"=>"table table-hover table-bordered"
    				),
				));   
		               
    }

	public function ExportExcel()
    {
    	$this->exportToExcel("MyReportExcel")->toBrowser("DatiFatturatoPivot.xlsx");
    }

}

?>

Sebastian Morales commented on Jun 6, 2022

Where do you define your datasource "dati_produzione"? We need to see that datasource setting.

Edp Ferrino commented on Jun 6, 2022

function settings()

	{
    	return array(
        	"dataSources"=>array(
            	"dati_produzione"=>array(
                	"connectionString"=>"oci:dbname=" . DB_DATASOURCE,
        			"username"=>DB_USER,
        			"password"=>DB_PASSWORD,
                	"charset"=>"utf8"
            	),
        	)
    	);

}

Sebastian Morales commented on Jun 6, 2022

Pls try this and let us know the result:

//MyReport.php
protected function setup()
{
	$sql      	= "";
	$sql_core 	= $this->GetSQLFatturato(); 
	$parametri 	= array(":data_inizio" => $this->data_inizio, ':data_fine' => $this->data_fine);
	$sql = "select a.NAZIONE, a.ANNO, a.PEZZI, a.FATTURATO, a.GRUPPOFAM, a.FAMIGLIA from (" . $sql_core . ") a";

    $this->src('dati_produzione')
		->query($sql)
		->params($parametri) 
    ->pipe($this->dataStore("dati_produzione"));
...

//MyReport.view.php
\koolreport\core\Utility::prettyPrint($this->dataStore("dati_produzione")->meta());
...
Edp Ferrino commented on Jun 6, 2022

{

"columns": []

}

Notice: Undefined index: NAZIONE in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Trying to access array offset on value of type null in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Undefined index: NAZIONE in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 90

Notice: Undefined index: ANNO in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Trying to access array offset on value of type null in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Undefined index: ANNO in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 90

Notice: Undefined index: PEZZI in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Trying to access array offset on value of type null in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Undefined index: PEZZI in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 90

Notice: Undefined index: FATTURATO in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Trying to access array offset on value of type null in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Undefined index: FATTURATO in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 90

Notice: Undefined index: GRUPPOFAM in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Trying to access array offset on value of type null in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Undefined index: GRUPPOFAM in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 90

Notice: Undefined index: FAMIGLIA in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Trying to access array offset on value of type null in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 89

Notice: Undefined index: FAMIGLIA in koolreport_pro_5.16.2/koolreport/pivot/PivotUtil.php on line 90

Notice: Undefined index: pivotId in koolreport_pro_5.16.2/koolreport/pivot/widgets/PivotMatrix.php on line 355

Notice: Undefined index: pivotRows in koolreport_pro_5.16.2/koolreport/pivot/widgets/PivotMatrix.php on line 356

Notice: Undefined index: pivotColumns in koolreport_pro_5.16.2/koolreport/pivot/widgets/PivotMatrix.php on line 357

Sebastian Morales commented on Jun 6, 2022

Remove the Pivot process and PivotMatrix for now please. I just want to check the column meta of your datasource and sql query.

Sebastian Morales commented on Jun 6, 2022

Anyway, I think I found the reason for the missing column meta. Pls open the file koolreport/core/src/datasources/PdoDataSource.php and replace this line:

            $this->firstRow = $this->stm->fetch(PDO::FETCH_ASSOC);

with this one:

            $this->firstRow = $row = $this->stm->fetch(PDO::FETCH_ASSOC);

Then try your pivot report again and let us know the result. Tks,

Edp Ferrino commented on Jun 6, 2022

It exists in two different lines of the same file. Do I have to do it on both?

Sebastian Morales commented on Jun 6, 2022

Sorry for the unclear guide, you only need to replace the line in function buildMetaData(). Tks,

Edp Ferrino commented on Jun 6, 2022

Tried and graphically it works, but the result is wrong. Transform 45162.2 to 45.16

Edp Ferrino commented on Jun 6, 2022

If I specify the number of decimals, the result is correct.

"decimals" => 2,

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
None yet
None

Designed and built with by KoolPHP Inc.

Code licensed MIT.