A pivot table is a table that displays one metric value for two dimensions. The rows of
the table represent one dimension and the columns another.
This filter can pivot any report by any dimension as long as either:
- the pivot-by dimension is the dimension of the report's subtable
- or, the pivot-by dimension has an associated report, and the report to pivot has a dimension with
a segment
Reports are pivoted by iterating over the rows of the report, fetching the pivot-by report
for the current row, and setting the columns of row to the rows of the pivot-by report. For example:
to pivot Referrers.getKeywords by UserCountry.City, we first loop through the Referrers.getKeywords
report's rows. For each row, we take the label (which is the referrer keyword), and get the
UserCountry.getCity report using the referrerKeyword=... segment. If the row's label were 'abcdefg',
we would use the 'referrerKeyword==abcdefg' segment.
The UserCountry.getCity report we find is the report on visits by country, but only for the visits
for the specific row. We take this report's row labels and add them as columns for the Referrers.getKeywords
table.
Implementation details:
Fetching intersected table can be done by segment or subtable. If the requested pivot by
dimension is the report's subtable dimension, then the subtable is used regardless, since it
is much faster than fetching by segment.
Also, by default, fetching by segment is disabled in the config (see the
'[General] pivot_by_filter_enable_fetch_by_segment' option).
public function test_filter_CorrectlyLimitsTheColumnNumber_WhenColumnLimitProvided()
{
$this->loadPlugins('Referrers', 'UserCountry', 'CustomVariables');
$table = $this->getTableToFilter(true);
$pivotFilter = new PivotByDimension($table, "Referrers.getKeywords", "Referrers.SearchEngine", $column = 'nb_visits', $columnLimit = 3, $fetchBySegment = false);
$pivotFilter->filter($table);
$expectedRows = array(array('label' => 'row 1', 'col 2' => false, 'col 4' => false, 'General_Others' => 1), array('label' => 'row 2', 'col 2' => 5, 'col 4' => false, 'General_Others' => 3), array('label' => 'row 3', 'col 2' => 7, 'col 4' => 32, 'General_Others' => 9));
$this->assertTableRowsEquals($expectedRows, $table);
}
private function handleDataTable(DataTableInterface $datatable)
{
$label = $this->getLabelFromRequest($this->request);
// handle pivot by dimension filter
$pivotBy = Common::getRequestVar('pivotBy', false, 'string', $this->request);
if (!empty($pivotBy)) {
$reportId = $this->apiModule . '.' . $this->apiMethod;
$pivotByColumn = Common::getRequestVar('pivotByColumn', false, 'string', $this->request);
$pivotByColumnLimit = Common::getRequestVar('pivotByColumnLimit', false, 'int', $this->request);
$datatable->filter('PivotByDimension', array($reportId, $pivotBy, $pivotByColumn, $pivotByColumnLimit, PivotByDimension::isSegmentFetchingEnabledInConfig()));
}
// if requested, flatten nested tables
if (Common::getRequestVar('flat', '0', 'string', $this->request) == '1') {
$flattener = new Flattener($this->apiModule, $this->apiMethod, $this->request);
if (Common::getRequestVar('include_aggregate_rows', '0', 'string', $this->request) == '1') {
$flattener->includeAggregateRows();
}
$datatable = $flattener->flatten($datatable);
}
if (1 == Common::getRequestVar('totals', '1', 'integer', $this->request)) {
$genericFilter = new ReportTotalsCalculator($this->apiModule, $this->apiMethod, $this->request);
$datatable = $genericFilter->calculate($datatable);
}
// if the flag disable_generic_filters is defined we skip the generic filters
if (0 == Common::getRequestVar('disable_generic_filters', '0', 'string', $this->request)) {
$genericFilter = new DataTableGenericFilter($this->request);
if (!empty($label)) {
$genericFilter->disableFilters(array('Limit', 'Truncate'));
}
$genericFilter->filter($datatable);
}
// we automatically safe decode all datatable labels (against xss)
$datatable->queueFilter('SafeDecodeLabel');
// if the flag disable_queued_filters is defined we skip the filters that were queued
if (Common::getRequestVar('disable_queued_filters', 0, 'int', $this->request) == 0) {
$datatable->applyQueuedFilters();
}
// use the ColumnDelete filter if hideColumns/showColumns is provided (must be done
// after queued filters are run so processed metrics can be removed, too)
$hideColumns = Common::getRequestVar('hideColumns', '', 'string', $this->request);
$showColumns = Common::getRequestVar('showColumns', '', 'string', $this->request);
if ($hideColumns !== '' || $showColumns !== '') {
$datatable->filter('ColumnDelete', array($hideColumns, $showColumns));
}
// apply label filter: only return rows matching the label parameter (more than one if more than one label)
if (!empty($label)) {
$addLabelIndex = Common::getRequestVar('labelFilterAddLabelIndex', 0, 'int', $this->request) == 1;
$filter = new LabelFilter($this->apiModule, $this->apiMethod, $this->request);
$datatable = $filter->filter($label, $datatable, $addLabelIndex);
}
return $this->apiRenderer->renderDataTable($datatable);
}
private function setShouldShowPivotBySubtable()
{
$report = Report::factory($this->controllerName, $this->controllerAction);
if (empty($report)) {
$this->show_pivot_by_subtable = false;
$this->pivot_by_dimension = false;
} else {
$this->show_pivot_by_subtable = PivotByDimension::isPivotingReportBySubtableSupported($report);
$subtableDimension = $report->getSubtableDimension();
if (!empty($subtableDimension)) {
$this->pivot_by_dimension = $subtableDimension->getId();
$this->pivot_dimension_name = $subtableDimension->getName();
}
}
}
/**
* @param DataTableInterface $dataTable
* @return DataTableInterface
*/
public function applyPivotByFilter(DataTableInterface $dataTable)
{
$pivotBy = Common::getRequestVar('pivotBy', false, 'string', $this->request);
if (!empty($pivotBy)) {
$this->applyComputeProcessedMetrics($dataTable);
$reportId = $this->apiModule . '.' . $this->apiMethod;
$pivotByColumn = Common::getRequestVar('pivotByColumn', false, 'string', $this->request);
$pivotByColumnLimit = Common::getRequestVar('pivotByColumnLimit', false, 'int', $this->request);
$dataTable->filter('ColumnCallbackDeleteMetadata', array('segmentValue'));
$dataTable->filter('ColumnCallbackDeleteMetadata', array('segment'));
$dataTable->filter('PivotByDimension', array($reportId, $pivotBy, $pivotByColumn, $pivotByColumnLimit, PivotByDimension::isSegmentFetchingEnabledInConfig()));
}
return $dataTable;
}
