Bug in PivotSQL - KoolReport

Christian Voß commented on May 11, 2021

Hi,

I believe I found a bug in the PivotSQL file.

When I try to create this pivot

->pipe(new PivotSQL([
                "column" => "date",
                "row" => "organisation_group, organisation",
                "aggregates"=>[
                    "avg"=>"value",
                ]

I always get an error message. It says that the index "avg(value)" is undefined.

I fixed it by changing the parts below in the PivotSQL file:

around line 190-200 foreach ($pivot->aggregates as $field => $aggs) {

            foreach ($aggs as $agg) {
                $measureColName = "{$colName}{$separator}{$field} - {$agg}";
                $dsData[$rowName][$measureColName] = $row[$field];
            }
        }

somewhere near line 460

$selectMeasureFields[] = "$agg($field) as {$field}";

Best regards

Christian

Sebastian Morales commented on May 11, 2021

Thank you, Christian. We could not replicate this issue in PHP 7.2 with MySQL. Would you pls let us know which version of PHP and which database type you were using when this error happened?

Sebastian Morales commented on May 11, 2021

We can confirm this error happens with some databases like SQL Server. We will fix it in the next version of Pivot. Meanwhile you could try this fix which is pretty similar to yours:

Open the file PivotSQL.php and replace the following string:

    $selectMeasureFields[] = "$agg($field)";

with:

    $selectMeasureFields[] = "$agg($field) as '{$agg}({$field})'";

Tks,

Christian Voß commented on May 11, 2021

Thank you very much, that fixed it.

KoolReport's Forum

Bug in PivotSQL #2068

Build Your Excellent Data Report