KoolReport's Forum

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

Pivot Table - rowSort #194

Open Paulo Morais opened this topic on on Jan 16, 2018 - 9 comments

Paulo Morais commented on Jan 16, 2018

With:

'rowSort' => array(
    'view - count' => 'asc',
),

the PivotTable appears correctly.

With:

'rowSort' => array(
    'view - count' => 'desc',
),

the PivotTable is incorrectly displayed.

David Winterburn commented on Jan 17, 2018

Hi Paulo,

These two screenshots are unstructured. Would you please provide a link to your test page or clearer images of what is wrong? Thanks!

Paulo Morais commented on Jan 17, 2018

Hi David,

Sample data (CSV):

company,event,source,view Restaurante,"DESCONTO DE 20% NA REFEIÇÃO PARA CLIENTES",app,89 Restaurante,"DESCONTO DE 20% NA REFEIÇÃO PARA CLIENTES",website,141 Empresa,"Test Event",website,133 Empresa,"Test Event",website,134 Empresa,"Test Event",website,135 Empresa,"Test Event",website,136 Empresa,"Test Event",website,137 Empresa,"Test Event",website,138 Empresa,"Test Event",website,139 Loja,PROMOTION,website,4 Loja,FANTASTIC,website,177 Hotel,"some event 123",website,129 Hotel,"some event 123",website,130 Hotel,"some event 123",website,131 Hotel,"some event 123",website,132 Hotel,"some event 456",website,140

as can be seen in the image, PROMOTION and FANTASTIC are repeated.

David Winterburn commented on Jan 18, 2018

Hi Paulo,

Would you please send your php code as well? Thanks!

Paulo Morais commented on Jan 20, 2018
public function settings()
    {
        return array(
            "dataSources" => array(
                "report_datasource" => array(
                    "class"      => '\koolreport\datasources\ArrayDataSource',
                    "dataFormat" => "associate",
                    "data"       => $this->params["data"],
                ),
            ),
        );
    }

public function setup()
    {
        try
        {
            $this->src('report_datasource')
                ->pipe(new ColumnMeta(array(
                    "company" => array(
                        'label' => 'Company',
                        'type'  => 'string',
                    ),
                )))
                ->pipe(new ColumnMeta(array(
                    "event" => array(
                        'label' => 'Event',
                        'type'  => 'string',
                    ),
                )))
                ->pipe(new ColumnMeta(array(
                    "source" => array(
                        'label' => 'Source',
                        'type'  => 'string',
                    ),
                )))
                ->pipe(new ColumnMeta(array(
                    "view" => array(
                        'label'    => 'Count',
                        'type'     => 'number',
                        "prefix"   => "",
                        'decimals' => 0,
                    ),
                )))
                ->pipe(new Pivot(array(
                    "dimensions" => array(
                        "row" => "company, event, source"
                    ),
                    "aggregates" => array(
                        "count" => "view",
                    ),
                )))
                ->pipe($this->dataStore('datasrc'));
        } catch (\Exception $e)
        {
            error_log($e->getMessage(), $e->getCode());
        }
    }
PivotTable::create(array(
    "dataStore"    => $this->dataStore('datasrc'),
    'rowDimension' => 'row',
    'measures'     => array(
        'view - count',
    ),
    'rowSort' => array(
        'view - count' => 'asc',
    ),
    'headerMap'    => array(
        'view - count' => 'View',
    ),
));
Paulo Morais commented on Jan 22, 2018

Hi David,

Is the average calculation working properly?

public function setup()

{
    try
    {
        $this->src('report_datasource')
            ->pipe(new ColumnMeta(array(
                "producer" => array(
                    'label' => 'Producer',
                    'type'  => 'string',
                ),
            )))
            ->pipe(new ColumnMeta(array(
                "wine" => array(
                    'label' => 'Wine',
                    'type'  => 'string',
                ),
            )))
            ->pipe(new ColumnMeta(array(
                "source" => array(
                    'label' => 'Source',
                    'type'  => 'string',
                ),
            )))
            ->pipe(new ColumnMeta(array(
                "review_id" => array(
                    'label'    => 'Count',
                    'type'     => 'number',
                    "prefix"   => "",
                    'decimals' => 0,
                ),
            )))
            ->pipe(new ColumnMeta(array(
                "eval" => array(
                    'label'    => 'Eval',
                    'type'     => 'number',
                    "prefix"   => "",
                    'decimals' => 2,
                ),
            )))
            ->pipe(new Pivot(array(
                "dimensions" => array(
                    "row" => "producer, wine, source",
                ),
                "aggregates" => array(
                    "count"   => "review_id",
                    "avg" => "eval",
                ),
            )))
            ->pipe($this->dataStore('datasrc'));
    } catch (\Exception $e)
    {
        error_log($e->getMessage(), $e->getCode());
    }
}

In Pivot.php

private function aggValue($aggregate, $value1, $value2) {

switch ($aggregate) {
  case 'min':
    return min($value1, $value2);
  case 'max':
    return max($value1, $value2);
  case 'count':
    return $value1 + 1;
  case 'avg':
  case 'sum':
  default:
    return (float) $value1 + (float) $value2;
}

}

Paulo Morais commented on Jan 24, 2018

Hi David,

Please reply as soon as possible. The Pivot Table shows the value of the sum when is requested the average (avg).

Paulo Morais commented on Jan 30, 2018

Dear Support Team,

Please reply at your earliest convenience.

KoolReport commented on Jan 30, 2018

At finalize() of Pivot: there is calculation for avg:

    foreach ($this->dataFields as $dataField => $aggregate)
      if ($aggregate === 'avg') {
        foreach ($this->data as $key => $datum) 
          $this->data[$key][$dataField] = 
            $this->data[$key][$dataField] / $this->count[$key];
      }

Since to calculate average we need to sum first then finally divided by the count. I will ask David to do the test again, if there is a bug we will fix immediately. I will come back to you.

KoolReport commented on Jan 31, 2018

We confirm it is a bug and we have released Pivot package 3.0.1 to fix. This package is also available in new KoolReport Pro 2.33.8. Please upgrade.

We apologize and thank you for your patience.

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
help needed

Pivot