KoolReport's Forum

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

PivotTable: custom collapsed grouping #105

Closed bysystem opened this topic on on Sep 7, 2017 - 12 comments

bysystem commented on Sep 7, 2017

Dear support team,

let's say I have the following pivot table with column1, column2, column3, column4...:

What I would like to do is to group column1 and column2 and collapse them to an individual collapse name "collapseName1" and the same with column3, column4. The collapseNames are not dimension fields or metrics in my database just simple individual collapsed groups like in excel to hide some columns:

How can I do that?

Kind regards

David Winterburn commented on Sep 7, 2017

Hi bysystem,

It's possible to do that with a fake field. Says your column1, column2, etc belongs to a field called Customer. What you want to do is use the process CalculatedColumn to create a field called CustomerGroup like this:

->pipe(new CalculatedColumn(array(
 		"customerGroup"=>function($row){
      if ($row['customer'] === 'column1' || $row['customer'] === 'column2')
        return 'customerGroup1';
      else if ($row['customer'] === 'column3' || $row['customer'] === 'column4')
        return 'customerGroup2';
    },
 )))

Finally, add the field CustomerGroup to your column dimension, before the field Customer. Please try this and let us know if you have any problem. Thanks!

bysystem commented on Sep 8, 2017

Dear David,

I follwoed your hint but however I'm stucking on $row['customer'] what does this means in my case!? Here is my code: Please consider: column1 = BudgetAktuellerMonat column2 = ErgebnisAktuellerMonat column3 = BereitsFakturiertAktuellerMonat column4 = BereitsFakturiertAktuelleWoche And I would like to group column1+column2 with the column title "customerGroup1" and column3+column4 with the column title "customerGroup2"

	->pipe(new CalculatedColumn(array(
			"customerGroup"=>function($row){
		  if ($row['customer'] === 'BudgetAktuellerMonat' || $row['customer'] === 'ErgebnisAktuellerMonat')
			return 'customerGroup1';
		  else if ($row['customer'] === 'BereitsFakturiertAktuellerMonat' || $row['customer'] === 'BereitsFakturiertAktuelleWoche')
			return 'customerGroup2';
		},
	 )))		

	->pipe(new Pivot(array(
		"dimensions" => array(
			"column" => "customerGroup",
			"row" => "ProductHierarchie, LocalMaterialDescription"
		),
		"aggregates" => array(
			"sum" => "BudgetAktuellerMonat, ErgebnisAktuellerMonat, BereitsFakturiertAktuellerMonat, BereitsFakturiertAktuelleWoche, ",
		)
	)))

What is wrong in my code?

bysystem commented on Sep 11, 2017

Any hint for me?

David Winterburn commented on Sep 11, 2017

Hi bysystem,

Which field of your data contains the value "BudgetAktuellerMonat", "ErgebnisAktuellerMonat", etc? You will need to change my calculated column's code from

"customerGroup" to "thatFieldGroup",

$row["customer"] to $row["thatField"]

and create a pivot with

"dimensions" => array("column" => "thatFieldGroup, thatField", "row"=>...)

Thanks!

bysystem commented on Sep 11, 2017

Dear David,

thx for your explanation. But however I'm still stucking:

>> Which field of your data contains the value "BudgetAktuellerMonat", "ErgebnisAktuellerMonat", etc?

In my case: 1. Value 'BudgetAktuellerMonat' >> $row['BudgetAktuellerMonat'] 2. Value 'ErgebnisAktuellerMonat' >> $row['ErgebnisAktuellerMonat'] 3. Value 'BereitsFakturiertAktuellerMonat' >> $row['BereitsFakturiertAktuellerMonat'] 4. Value 'BereitsFakturiertAktuelleWoche' >> $row['BereitsFakturiertAktuelleWoche']

So I adjust my code to:

	->pipe(new CalculatedColumn(array(
			"customerGroup"=>function($row){
		  if ($row['BudgetAktuellerMonat'] === 'BudgetAktuellerMonat' || $row['ErgebnisAktuellerMonat'] === 'ErgebnisAktuellerMonat')
			return 'customerGroup1';
		  else if ($row['BereitsFakturiertAktuellerMonat'] === 'BereitsFakturiertAktuellerMonat' || $row['BereitsFakturiertAktuelleWoche'] === 'BereitsFakturiertAktuelleWoche')
			return 'customerGroup2';
		},
	 )))		
	
	->pipe(new Pivot(array(
		"dimensions" => array(
			"column" => "customerGroup1, BudgetAktuellerMonat",
			"row" => "ProductHierarchie, LocalMaterialDescription"
		),
		"aggregates" => array(
			"sum" => "BudgetAktuellerMonat, ErgebnisAktuellerMonat, BereitsFakturiertAktuellerMonat, BereitsFakturiertAktuelleWoche, ",
		)

But it seems to be still something wrong what I'm doing here because the result is a blank page!

Any idea what could be wrong?

David Winterburn commented on Sep 11, 2017

Hi bysystem,

Please send us some sample of your data before piping to the pivot process. We would have a better view of your requirement. Thanks!

bysystem commented on Sep 11, 2017

Dear David,

here is my controller php:

<?php

require_once "../koolreport/autoload.php"; use \koolreport\processes\Filter; use \koolreport\processes\ColumnMeta; use \koolreport\pivot\processes\Pivot; use \koolreport\processes\Group; use \koolreport\processes\Sort; use \koolreport\processes\Limit;

class abs extends koolreport\KoolReport {

use \koolreport\clients\FontAwesome;
use \koolreport\clients\Bootstrap;
use \koolreport\inputs\Bindable;
use \koolreport\inputs\POSTBinding;
use \koolreport\export\Exportable;

function defaultParamValues()
{
    return array(
		"ProductNumber" => array(),
        "LocalMaterialDescription" => array(),
		"ProductHierarchie" => array(),
    );
}

function bindParamsToInputs()
{
    return array(
		"ProductNumber",
		"LocalMaterialDescription",
		"ProductHierarchie",
    );
}

function settings()
{
    return array(
		"dataSources"=>array(
			"abs"=>array(
			'connectionString' => 'sqlsrv:Server=tcp:12.234.45.67;Database=ABS',
			'username' => 'xyz',
			'password' => '*********',
			), 
		)
    );
}

function setup()
{
	// PRODUKT: MultiSelect-Optionswerte aus Reporting.Report_ABS holen
	$this->src('abs')
    ->query("
		SELECT DISTINCT LocalMaterialDescription
		FROM Reporting.Report_ABS 
		ORDER BY LocalMaterialDescription ASC
    ")
    ->pipe($this->dataStore("ABS.LocalMaterialDescription"));

	// PRODUKTHIERARCHIE: MultiSelect-Optionswerte aus Reporting.Report_ABS holen
	$this->src('abs')
    ->query("
		SELECT DISTINCT ProductHierarchie
		FROM Reporting.Report_ABS 
		ORDER BY ProductHierarchie ASC
    ")
    ->pipe($this->dataStore("ABS.ProductHierarchie"));		
	
	// ARTIKELNUMMER: MultiSelect-Optionswerte aus Reporting.Report_ABS holen
	$this->src('abs')
    ->query("
		SELECT DISTINCT ProductNumber
		FROM Reporting.Report_ABS 
		ORDER BY ProductNumber ASC
    ")
    ->pipe($this->dataStore("ABS.ProductNumber"));
	
	// PIVOTTABLE: Umsatz aggregieren	
	if(count($this->params["ProductNumber"])>0){$whereProductNumber="ProductNumber IN (:ProductNumber) ";}else{$whereProductNumber="ProductNumber IS NOT NULL ";}			
	if(count($this->params["LocalMaterialDescription"])>0){$whereLocalMaterialDescription="LocalMaterialDescription IN (:LocalMaterialDescription) ";}else{$whereLocalMaterialDescription="LocalMaterialDescription IS NOT NULL ";}		
	if(count($this->params["ProductHierarchie"])>0){$whereProductHierarchie="ProductHierarchie IN (:ProductHierarchie) ";}else{$whereProductHierarchie="ProductHierarchie IS NOT NULL ";}	

	$this->src('abs')
		->query("
		SELECT ProductNumber, LocalMaterialDescription, ProductHierarchie, BudgetAktuellerMonat, ErgebnisAktuellerMonat, BereitsFakturiertAktuellerMonat, BereitsFakturiertAktuelleWoche
		FROM Reporting.Report_ABS
		WHERE			
			$whereProductNumber AND
			$whereLocalMaterialDescription AND
			$whereProductHierarchie
			
	")->params(array(
		":ProductNumber"=>$this->params["ProductNumber"],
		":LocalMaterialDescription"=>$this->params["LocalMaterialDescription"],
		":ProductHierarchie"=>$this->params["ProductHierarchie"]
	))

	->pipe(new CalculatedColumn(array(
			"customerGroup"=>function($row){
		  if ($row['BudgetAktuellerMonat'] === 'BudgetAktuellerMonat' || $row['ErgebnisAktuellerMonat'] === 'ErgebnisAktuellerMonat')
			return 'customerGroup1';
		  else if ($row['BereitsFakturiertAktuellerMonat'] === 'BereitsFakturiertAktuellerMonat' || $row['BereitsFakturiertAktuelleWoche'] === 'BereitsFakturiertAktuelleWoche')
			return 'customerGroup2';
		},
	 )))		
	
	->pipe(new Pivot(array(
		"dimensions" => array(
			"column" => "customerGroup1, BudgetAktuellerMonat",
			"row" => "ProductHierarchie, LocalMaterialDescription"
		),
		"aggregates" => array(
			"sum" => "BudgetAktuellerMonat, ErgebnisAktuellerMonat, BereitsFakturiertAktuellerMonat, BereitsFakturiertAktuelleWoche, ",
		)
	)))
	->pipe($this->dataStore('Reporting.Report_ABS')); 
	//echo $this->params["ProductNumber"];
	
} 

}

And here is my view php:

<?php

use \koolreport\pivot\widgets\PivotTable;
use \koolreport\widgets\koolphp\Table;
use \koolreport\inputs\MultiSelect;
use \koolreport\inputs\Select2;
use \koolreport\inputs\CheckBoxList;
use \koolreport\inputs\RadioList;
use \koolreport\widgets\google\BarChart;
use \koolreport\widgets\google\PieChart;
use \koolreport\widgets\google;

?>

<div id="col2">	
	<div id="col2_content" class=""clearfix>
		<form method="post">								

			<div class="form-group" style="width:250px;margin-right:10px;">
			<h5>Produkt</h5>
				<?php
				Select2::create(array(
					"name"=>"LocalMaterialDescription",
					"dataStore"=>$this->dataStore("ABS.LocalMaterialDescription"),
					"dataBind"=>"LocalMaterialDescription",
					"multiple"=>true,
					"attributes"=>array(
						"class"=>"form-control",
					)
				));
				?>
			</div>

			<div class="form-group" style="width:250px;margin-right:10px;">
			<h5>Produkthierarchie</h5>
				<?php
				Select2::create(array(
					"name"=>"ProductHierarchie",
					"dataStore"=>$this->dataStore("ABS.ProductHierarchie"),
					"dataBind"=>"ProductHierarchie",
					"multiple"=>true,
					"attributes"=>array(
						"class"=>"form-control",
					)
				));
				?>
			</div>

			<div class="form-group" style="width:250px;margin-right:10px;">
			<h5>Artikelnummer</h5>
				<?php
				Select2::create(array(
					"name"=>"ProductNumber",
					"dataStore"=>$this->dataStore("ABS.ProductNumber"),
					"dataBind"=>"ProductNumber",
					"multiple"=>true,
					"attributes"=>array(
						"class"=>"form-control",
					)
				));
				?>
			</div>	
		
			<div class="form-group text-center">
				<button class="btn btn-success" name="btnSubmit"><i class="glyphicon glyphicon-refresh"></i> Finden</button>
			</div>	

		</form>	
	</div>
</div>

<div id="col1">
	<div id="col1_content" class="clearfix">
		<h3><img src="icon_grid.png"/>&nbsp;&nbsp;&nbsp;ABS (Außendienst Business Steering)</h3>
			<?php
				if((isset($_POST["btnSubmit"])) AND ($this->dataStore("Reporting.Report_ABS")->countData()>0))
				{
					PivotTable::create(array(
					  "dataStore"=>$this->dataStore('Reporting.Report_ABS'),

					  "measures" => array(
						"BudgetAktuellerMonat - sum",
						"ErgebnisAktuellerMonat - sum",
						"BereitsFakturiertAktuellerMonat - sum",
						"BereitsFakturiertAktuelleWoche - sum",							
					  ),
						  
					  "headerMap" => array(
						"BudgetAktuellerMonat - sum" => "&sum; Budget akt. Monat",
						"ErgebnisAktuellerMonat - sum" => "&sum; Aktueller Monat",
						"BereitsFakturiertAktuellerMonat - sum" => "&sum; Fakturiert akt. Monat",
						"BereitsFakturiertAktuelleWoche - sum" => "&sum; Fakturiert akt. Woche",
						),
					 
					  "rowCollapseLevels" => array(3), //0 = zu, 1 = auf
					  "columnCollapseLevels" => array(1,2),
					  
					  "totalName" => '<div style="display:none" align="right"><strong>TOTAL</strong></div>',
					  "hideTotalRow" => true,
					));
				}
			?>     
	</div>			
</div>

</body> </html>

David Winterburn commented on Sep 12, 2017

Hi bysystem,

Thanks for your feedback! What I would like to see is a sample of your data from database. Thanks!

bysystem commented on Sep 12, 2017

Dear David,

sorry for misunderstanding! Just sent you (support@koolphp.net) an email with a sample of my database and explanation what I would like to do.

Thx in advance.

David Winterburn commented on Sep 13, 2017

Hi bysystem,

We have received your data structure. You have a direct table from database that you want to collapse/expand several columns like in a pivot table widget.

This is not a simple problem. We will think of some way to alter your table data so that it can be input to a pivot process. The final result will be viewed by a pivot table widget like you wish.

Please wait, thanks!

David Winterburn commented on Sep 20, 2017

Hi bysystem,

After receiving your data sample, we think what you want is to make use of the collapse/expand function of a pivot table to a regular table's columns. To do this first you will have to modify your data table to add to a pivot process, after that you could use the pivot table widget to display the result (with collapse/expand function for the columns).

First step: change your sql query of the data table. Says you want to collapse the 3 columns "FreiFuerDistrictWoche1", "FreiFuerDistrictWoche2" and "FreiFuerDistrictWoche3" under a parent column called "Frei für Deutschland heute + 3 Wochen", use this sql query:

$node->src('sales')
->query(
'select productNumber, FreiFuerDistrictWoche1 as `dataField`, "FreiFuerDistrictWoche1" as `columnField`, "Frei für Deutschland heute + 3 Wochen" as `parentColumnField` from table1
union
select productNumber, FreiFuerDistrictWoche2 as `dataField`, "FreiFuerDistrictWoche2" as `columnField`, "Frei für Deutschland heute + 3 Wochen" as `parentColumnField` from table1 
union
select productNumber, FreiFuerDistrictWoche3 as `dataField`, "FreiFuerDistrictWoche3" as `columnField`, "Frei für Deutschland heute + 3 Wochen" as `parentColumnField` from table1'
)

Then apply this pivot process:

->pipe(new Pivot(array(
  "dimensions" => array(
    "column" => "parentColumnField, columnField",
    "row" => "productNumber"
  ),
  "aggregates" => array(
    "sum" => "dataField"
  )
)))
pipe($this->dataStore('salesPivot'));

Finally in your report's view just use the PivotTable widget to display from the datastore.

Please try this and let us know if you have any problem with it. Thanks!

bysystem commented on Sep 27, 2017

Great! This works fine for me!

Thx for your support!

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
solved

Pivot