Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
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
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!
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?
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!
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?
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"/> 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" => "∑ Budget akt. Monat",
"ErgebnisAktuellerMonat - sum" => "∑ Aktueller Monat",
"BereitsFakturiertAktuellerMonat - sum" => "∑ Fakturiert akt. Monat",
"BereitsFakturiertAktuelleWoche - 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>
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!
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!
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo