Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
Hi, I would need to export a PivotMatrix in excel I have read and followed the official documentation, but there are not enough examples to develop the export of the pivot matrix. Can you give me a guide or an answer on how to do it as soon as possible, given the urgency I have! Thank you
Pls follow this example:
https://www.koolreport.com/examples/reports/excel/pivottable/
You could copy codes below the example and modify them. Let us know if you have any question. Tks,
Oh, I see. It's a pity that pivot excel export doesn't work with PivotSQL process, only with Pivot or Pivot2D one. If your data is not too big (says millions of rows), pls try either Pivot or Pivot2D and pipe the data to a datastore for exporting to excel. You can still use PivotSQL for showing PivotMatrix on browser. Rgds,
Report.php function setup() {
$this->src('oracle_mydbpdo')
->query("select * from ***
")
->pipe(new \koolreport\pivot\processes\PivotSQL(array(
"column" => "MONTH_LONG_NAME, WEEKDAY_NAME",
"row" => "NOME_GRUPPO",
"aggregates"=>array(
"SUM" => "EURO_TXN_VALUE",
"COUNT" => "EURO_TXN_VALUE",
)
)))
->pipe(new ColumnMeta(array(
"EURO_TXN_VALUE - SUM"=>array(
'type' => 'number',
"prefix" => "€ ",
),
"EURO_TXN_VALUE - COUNT"=>array(
'type' => 'number',
"prefix" => "Tot: ",
),
)))
// ->pipe(new Limit( // array(100) // ))
->pipe($this->dataStore("pivotData2"));
}
ReportExcel.view.php
<?php
use \koolreport\excel\Table;
use \koolreport\excel\PivotTable;
use \koolreport\excel\BarChart;
use \koolreport\excel\LineChart;
$sheet1 = "Sales by Customer";
$allStylesArray = [
'font' => [
'name' => 'Calibri', //'Verdana', 'Arial'
'size' => 30,
'bold' => false,
'italic' => FALSE,
'underline' => 'none', //'double', 'doubleAcCOUNTing', 'single', 'singleAcCOUNTing'
'strikethrough' => FALSE,
'superscript' => false,
'subscript' => false,
'color' => [
'rgb' => '000000',
'argb' => 'FF000000',
]
],
'alignment' => [
'horizontal' => 'general',//left, right, center, centerContinuous, justify, fill, distributed
'vertical' => 'bottom',//top, center, justify, distributed
'textRotation' => 0,
'wrapText' => false,
'shrinkToFit' => false,
'indent' => 0,
'readOrder' => 0,
],
'borders' => [
'top' => [
'borderStyle' => 'none', //dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
'color' => [
'rgb' => '808080',
'argb' => 'FF808080',
]
],
//left, right, bottom, diagonal, allBorders, outline, inside, vertical, horizontal
],
'fill' => [
'fillType' => 'none', //'solid', 'linear', 'path', 'darkDown', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkTrellis', 'darkUp', 'darkVertical', 'gray0625', 'gray125', 'lightDown', 'lightGray', 'lightGrid', 'lightHorizontal', 'lightTrellis', 'lightUp', 'lightVertical', 'mediumGray'
'rotation' => 90,
'color' => [
'rgb' => 'A0A0A0',
'argb' => 'FFA0A0A0',
],
'startColor' => [
'rgb' => 'A0A0A0',
'argb' => 'FFA0A0A0',
],
'endColor' => [
'argb' => 'FFFFFF',
'argb' => 'FFFFFFFF',
],
],
];
$styleArray = [
'font' => [
'italic' => true,
'color' => [
'rgb' => '808080',
]
],
];
PivotTable::create(array(
"dataSource" => $this->dataStore('pivotData2'),
'hideSubTotalRows' => true,
'hideSubTotalColumns' => true,
'hideGrandTotalRow' => true,
'hideGrandTotalColumn' => true,
"showDataHeaders" => true,
'map' => array(
'rowField' => function($rowField, $fieldInfo) {
return $rowField;
},
'columnField' => function($colField, $fieldInfo) {
return $colField;
},
'dataField' => function($dataField, $fieldInfo) {
//Util::prettyPrint($fieldInfo);
$v = $dataField;
if ($v === 'EURO_TXN_VALUE - SUM')
$v = 'Somma vendite';
else if ($v === 'EURO_TXN_VALUE - COUNT')
$v = 'Totale vendite';
return $v;
},
'dataHeader' => function($dataField, $fieldInfo) {
//Util::prettyPrint($fieldInfo);
$v = $dataField;
if ($v === 'EURO_TXN_VALUE - SUM')
$v = 'Somma vendite';
else if ($v === 'EURO_TXN_VALUE - COUNT')
$v = 'Totale vendite';
return $v;
},
'waitingField' => function($waitingField, $fieldInfo) {
return $waitingField;
},
'rowHeader' => function($rowHeader, $headerInfo) {
//Util::prettyPrint($headerInfo);
$v = $rowHeader;
if (isset($headerInfo['childOrder']))
$v = $headerInfo['childOrder'] . ". " . $v;
return $v;
},
'columnHeader' => function($colHeader, $headerInfo) {
$v = $colHeader;
if ($headerInfo['fieldName'] === 'orderYear')
$v = 'Year-' . $v;
else if ($headerInfo['fieldName'] === 'orderQuarter')
$v = 'Quarter-' . $v;
if (isset($headerInfo['childOrder']))
$v = $headerInfo['childOrder'] . ". " . $v;
return $v;
},
'dataCell' => function($value, $cellInfo) {
return $value;
//Util::prettyPrint($cellInfo);
$rfOrder = $cellInfo['row']['fieldOrder'];
$cfOrder = $cellInfo['column']['fieldOrder'];
$df = $cellInfo['fieldName'];
$dfOrder = $cellInfo['fieldOrder'];
// return "$rfOrder:$cfOrder:$df. $value";
return $cellInfo['formattedValue'];
},
),
'excelStyle' => array(
'dataField' => function($dataFields) use ($styleArray) {
return $styleArray;
},
'dataHeader' => function($dataFields, $fieldInfo) use ($styleArray) {
return $styleArray;
},
'columnHeader' => function($header, $headerInfo) use ($styleArray) {
return $styleArray;
},
'rowHeader' => function($header, $headerInfo) use ($styleArray) {
return $styleArray;
},
'dataCell' => function($value, $cellInfo) use ($styleArray) {
return $styleArray;
},
)
));
?>
it makes me the correct export starting from the pivotSQL, but only if there is a single field on the rows and columns. If already from the creation of the datastore I add a field on the columns, it fails to do the correct export but exports only the rows where there is a single field!
Forgive me but the problem is not in the PivotSql that creates the datastore, but on the PivotMatrix, which I use precisely to be able to change the groupings of the tables. The same problem occurs when using search filters. In practice, generating the datastore by filtering the results, then when you change the grouping you lose the initial filters. Regarding the excel export I have seen the dependencies and there is no library that allows the export of the PivotMatrix, but there is only that of the PivotTable
I tried with normal pivots, but we have big performance problems because we have a lot of data. We absolutely need to work with PivotSQL and PivotMatrix. And also to solve the problem, that if I start with a query with where conditions and use the pivotmatrix, the references to the where are perfected!
Hi, I've just tested exporting to Excel with PivotSQL process and PivotMatrix widget and it seems to work well. Pls put this form, export button, and hidden input in your report view:
<form method="post">
<button formaction="exportExcel.php" class="btn btn-primary">Download Excel</button>
<input type="hidden" name="koolPivotUpdate" value="1" />
...
//PivotMatrix here
</form>
Pls try it and let us know it works for you. Tks,
I had already tried this procedure, but when I add input <input type = 'hidden' name = 'koolPivotUpdate' value = 1 />, the export fails. Without this parameter the export works, but exports only the starting matrix and with a single row and a single column. Already with two he goes wrong.
I'll give you the code
<?php
use \koolreport\pivot\widgets\PivotMatrix;
<form method="post">
<button class="btn btn-primary" formaction='/report/availability?csv=1'>Export Excel</button>
<input type='hidden' name='koolPivotUpdate' value=1 />
<div class="clearb"></div>
PivotMatrix::create(array(
"id" => "pivotMatrix2",
'dataSource' => $this->dataStore('pivotData2'),
'waitingFields' => array(
'WEEKDAY_NAME' => 'label',
'WEEK_OF_MONTH_NUM' => 'label',
'NUM_ZONA' => 'label',
'TICKET_DESCRIPTION' => 'label',
),
'headerMap' => array(
'WEEKDAY_NAME' => 'Giorno settimana',
'MONTH_LONG_NAME' => 'Mese',
'EURO_TXN_VALUE - SUM' => 'Somma Vendite',
'EURO_TXN_VALUE - COUNT' => 'Totale Vendite',
'WEEK_OF_MONTH_NUM' => 'Settimana',
'NUM_ZONA' => 'Zone',
'TICKET_DESCRIPTION' => 'Tipo di prodotto',
),
'paging' => array(
'size' => 100,
'maxDisplayedPages' => 5,
'sizeSelect' => array(5, 10, 20, 50, 100)
)
));
?>
</form>
<?php Page action form if( !empty( $_GET['csv'] ) ) {
$report->run();
$report->exportToExcel('MyKReportAvailabilityExcel')->toBrowser("reportAvailability.xlsx");
exit;
}
?>
File MyKReportAvailabilityExcel.view.php <?php
use \koolreport\excel\Table;
use \koolreport\excel\PivotTable;
use \koolreport\excel\BarChart;
use \koolreport\excel\LineChart;
$allStylesArray = [
'font' => [
'name' => 'Calibri', //'Verdana', 'Arial'
'size' => 30,
'bold' => false,
'italic' => FALSE,
'underline' => 'none', //'double', 'doubleAcCOUNTing', 'single', 'singleAcCOUNTing'
'strikethrough' => FALSE,
'superscript' => false,
'subscript' => false,
'color' => [
'rgb' => '000000',
'argb' => 'FF000000',
]
],
'alignment' => [
'horizontal' => 'general',//left, right, center, centerContinuous, justify, fill, distributed
'vertical' => 'bottom',//top, center, justify, distributed
'textRotation' => 0,
'wrapText' => false,
'shrinkToFit' => false,
'indent' => 0,
'readOrder' => 0,
],
'borders' => [
'top' => [
'borderStyle' => 'none', //dashDot, dashDotDot, dashed, dotted, double, hair, medium, mediumDashDot, mediumDashDotDot, mediumDashed, slantDashDot, thick, thin
'color' => [
'rgb' => '808080',
'argb' => 'FF808080',
]
],
//left, right, bottom, diagonal, allBorders, outline, inside, vertical, horizontal
],
'fill' => [
'fillType' => 'none', //'solid', 'linear', 'path', 'darkDown', 'darkGray', 'darkGrid', 'darkHorizontal', 'darkTrellis', 'darkUp', 'darkVertical', 'gray0625', 'gray125', 'lightDown', 'lightGray', 'lightGrid', 'lightHorizontal', 'lightTrellis', 'lightUp', 'lightVertical', 'mediumGray'
'rotation' => 90,
'color' => [
'rgb' => 'A0A0A0',
'argb' => 'FFA0A0A0',
],
'startColor' => [
'rgb' => 'A0A0A0',
'argb' => 'FFA0A0A0',
],
'endColor' => [
'argb' => 'FFFFFF',
'argb' => 'FFFFFFFF',
],
],
];
$styleArray = [
'font' => [
'italic' => true,
'color' => [
'rgb' => '808080',
]
],
];
?>
<div>Totale vendite per periodo</div>
<div>
PivotTable::create(array(
"dataSource" => $this->dataStore('pivotData2'),
'hideSubTotalRows' => true,
'hideSubTotalColumns' => true,
'hideGrandTotalRow' => true,
'hideGrandTotalColumn' => true,
"showDataHeaders" => true,
'map' => array(
'rowField' => function($rowField, $fieldInfo) {
return $rowField;
},
'columnField' => function($colField, $fieldInfo) {
return $colField;
},
'dataField' => function($dataField, $fieldInfo) {
//Util::prettyPrint($fieldInfo);
$v = $dataField;
if ($v === 'EURO_TXN_VALUE - SUM')
$v = 'Somma vendite';
else if ($v === 'EURO_TXN_VALUE - COUNT')
$v = 'Totale vendite';
return $v;
},
'dataHeader' => function($dataField, $fieldInfo) {
//Util::prettyPrint($fieldInfo);
$v = $dataField;
if ($v === 'EURO_TXN_VALUE - SUM')
$v = 'Somma vendite';
else if ($v === 'EURO_TXN_VALUE - COUNT')
$v = 'Totale vendite';
return $v;
},
'waitingField' => function($waitingField, $fieldInfo) {
return $waitingField;
},
'rowHeader' => function($rowHeader, $headerInfo) {
//Util::prettyPrint($headerInfo);
$v = $rowHeader;
if (isset($headerInfo['childOrder']))
$v = $headerInfo['childOrder'] . ". " . $v;
return $v;
},
'columnHeader' => function($colHeader, $headerInfo) {
$v = $colHeader;
if ($headerInfo['fieldName'] === 'orderYear')
$v = 'Year-' . $v;
else if ($headerInfo['fieldName'] === 'orderQuarter')
$v = 'Quarter-' . $v;
if (isset($headerInfo['childOrder']))
$v = $headerInfo['childOrder'] . ". " . $v;
return $v;
},
'dataCell' => function($value, $cellInfo) {
return $value;
//Util::prettyPrint($cellInfo);
$rfOrder = $cellInfo['row']['fieldOrder'];
$cfOrder = $cellInfo['column']['fieldOrder'];
$df = $cellInfo['fieldName'];
$dfOrder = $cellInfo['fieldOrder'];
// return "$rfOrder:$cfOrder:$df. $value";
return $cellInfo['formattedValue'];
},
),
'excelStyle' => array(
'dataField' => function($dataFields) use ($styleArray) {
return $styleArray;
},
'dataHeader' => function($dataFields, $fieldInfo) use ($styleArray) {
return $styleArray;
},
'columnHeader' => function($header, $headerInfo) use ($styleArray) {
return $styleArray;
},
'rowHeader' => function($header, $headerInfo) use ($styleArray) {
return $styleArray;
},
'dataCell' => function($value, $cellInfo) use ($styleArray) {
return $styleArray;
},
)
));
?>
</div>
</div>
Cosa c'è di sbagliato?
It's strange. Pls keep the hidden input and replace the following lines in your exportExcel file:
$report->run();
$report->exportToExcel('MyKReportAvailabilityExcel')->toBrowser("reportAvailability.xlsx");
exit;
with these ones:
$report->run();
\koolreport\core\Utility::prettyPrint($report->dataStore("MyKReportAvailabilityExcel")->data());
exit;
Then run your PivotMatrix, expand some nodes and click export to excel to see if there's more detail output or only the first level data.
Ok, ho fatto.
[
{
"label": "{{all}} || {{all}}",
"Mercoledi || {{all}} || EURO_TXN_VALUE - SUM": "25950.5",
"Mercoledi || {{all}} || EURO_TXN_VALUE - COUNT": "2896",
"{{all}} || {{all}} || EURO_TXN_VALUE - SUM": "25950.5",
"{{all}} || {{all}} || EURO_TXN_VALUE - COUNT": "2896"
},
{
"label": "ALBANO LAZIALE || {{all}}",
"Mercoledi || {{all}} || EURO_TXN_VALUE - SUM": "0",
"Mercoledi || {{all}} || EURO_TXN_VALUE - COUNT": "2",
"{{all}} || {{all}} || EURO_TXN_VALUE - SUM": "0",
"{{all}} || {{all}} || EURO_TXN_VALUE - COUNT": "2"
},
ecc...
In the Matrix I had 2 rows 2 columns, and it only returns me the first level data
Ok, I remember one detail: session must be started for PivotSQL to work. Pls put the following in your export excel code:
<?php
print_r($_POST);
session_start();
require_once "MyReport.php"; //change this to your report
$report = new MyReport();
$report->run();
\koolreport\core\Utility::prettyPrint($report->dataStore("pivotData2")->data());
exit;
Then open your PivotMatrix page, expand some nodes and click export to excel. Let us know the result. Tks,
PivotMatrix has a property called "scope" which allows users to attach parameters when expanding, dragging and dropping it:
PivotMatrix::create(array(
...
"scope" => array(
"mySelect" => $this->params["mySelect"], //assuming this report uses a select2 input named "mySelect"
)
));
Pls try it and let us know how it works for you. Tks,
Yes I had already resolved with the purpose. I have only one problem left. Now the parameters are passed, I compose the query query correctly, but then it is as if the sql pivot ignores those parameters or returns the basic query to me.
When a grouping changes from the PivotMatrix table, only at that point does the query with the filters change
public function setup() {
$query_params = [];
if( !empty( $this->params["gruppi"] ) ) {
$gruppi = $this->params["gruppi"];
$query_params[":gruppi"] = is_array( $gruppi ) ? $gruppi : [$gruppi];
}
if( !empty( $this->params["weekday_num"] ) ) {
$weekdayNum = $this->params["weekday_num"];
$query_params[":weekday_num"] = is_array( $weekdayNum ) ? $weekdayNum : [$weekdayNum];
}
if( !empty( $this->params["punti_vendita"] ) ) {
$puntivendita = $this->params["punti_vendita"];
$query_params[":punti_vendita"] = is_array( $puntivendita ) ? $puntivendita : [$puntivendita];
}
$conditions = $this->myBindParams( $query_params );
$this->src('oracle_mydbpdo')
->query("select * from FACTS_SALES
inner join DATE_DIMENSION on FACTS_SALES.date_id = DATE_DIMENSION.date_id
where 1=1
".(($this->params["gruppi"]!=array())?" and FACTS_SALES.group_id in (:gruppi)":"")."
".(($this->params["weekday_num"]!=array())?" and DATE_DIMENSION.WEEKDAY_NUM in (:weekday_num)":"")."
")->params($query_params)
->pipe(new \koolreport\pivot\processes\PivotSQL(array(
"column" => "WEEKDAY_NAME",
"row" => "NOME_GRUPPO",
"aggregates"=>array(
"SUM" => "EURO_TXN_VALUE",
"COUNT" => "EURO_TXN_VALUE",
)
)))
->pipe(new ColumnMeta(array(
"EURO_TXN_VALUE - SUM"=>array(
'type' => 'number',
"prefix" => "€ ",
),
"EURO_TXN_VALUE - COUNT"=>array(
'type' => 'number',
"prefix" => "Tot: ",
),
)))
->pipe(new Limit(
array(10)
))
->pipe($this->dataStore("pivotReportSales"));
$this->src('oracle_mydbpdo')->query("select * from GRUPPI")->pipe($this->dataStore("groups"));
}
Print query: select * from FACTS_SALES inner join DATE_DIMENSION on FACTS_SALES.date_id = DATE_DIMENSION.date_id where 1=1 and FACTS_SALES.group_id in (:gruppi) ( CORRECT )
If I move a calmpo of the pivotMatrix, only then it updates the query, otherwise I can also add new filters, the query I see in print is changed correctly, but the data shown in pivotmatrix remains the same as before, until some grouping is changed
I see, this could be because PivotQL isn't compatible with query using params yet. Pls replace these lines:
->query("select * from FACTS_SALES
inner join DATE_DIMENSION on FACTS_SALES.date_id = DATE_DIMENSION.date_id
where 1=1
".(($this->params["gruppi"]!=array())?" and FACTS_SALES.group_id in (:gruppi)":"")."
".(($this->params["weekday_num"]!=array())?" and DATE_DIMENSION.WEEKDAY_NUM in (:weekday_num)":"")."
")->params($query_params)
with these ones:
->query("select * from FACTS_SALES
inner join DATE_DIMENSION on FACTS_SALES.date_id = DATE_DIMENSION.date_id
where 1=1
".(($this->params["gruppi"]!=array())?" and FACTS_SALES.group_id in ('" . implode("','", $query_params["gruppi"]) . "')":"")."
".(($this->params["weekday_num"]!=array())?" and DATE_DIMENSION.WEEKDAY_NUM in ('" . implode("','", $query_params[":weekday_num"]) . "')":"")."
")
Then run PivotMatrix with parameters again and let us know the result. Rgds,
sorry, i have already done this test, not using> params but printing directly in the query.
But the behavior is the same. I repeat it seems to me more linked to the cache or to the session, the previous query is kept in memory, and it is necessary to change a grouping to make the query change
PivotSQL always uses the latest built query. It doesn't save a query to session. Pls try these steps:
1 . Add your report's params to the "scope" property of PivotMatrix.
2 . At first load, change some param input values, then click a Submit button for PivotMatrix to catch those values. If you only change input values on client side without clicking Submit to submit form, PivotMatrix has no idea which params to use.
3 . After submitting expanding/collapsing, dragging, dropping field in PivotMatrix to see if it remembers the updated query.
Ok, I get the idea and can replicate this issue now. This is indeed a case we didn't think of when users change the sql query and PivotSQL still uses the cached data. We will make a fix for the next released version. Meanwhile, you could apply this fix yourself:
Open the file koolreport/pivot/processes/PivotSQL.php and replace the following lines:
$this->pivotSign = md5(
$this->pivotId
. "row" . implode("", $this->rowFields)
. "column" . implode("", $this->columnFields)
. $selectMeasureFieldsStr
);
with these ones:
$this->pivotSign = md5(
$this->pivotId
. $this->viewQuery
. "row" . implode("", $this->rowFields)
. "column" . implode("", $this->columnFields)
. $selectMeasureFieldsStr
);
Let us know how it works for you. Tks,
With PivotSQL process, it's not possible. You would have to manually expand the nodes yourself (though in PivotMatrix there's a menu to expand all nodes of a field to make it faster).
Another option is to use the normal Pivot process when exporting (add an if in your report's setup, if it's exporting use Pivot instead of PivotSQL), which by default expands all data. Depending on the number of rows of your data source this could take some time to finish.
On a further note, we are going to release a new product in a few months time which helps showing/exporting pivot results in a near instant time no matter how many rows there are in the data source.
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo