KoolReport's Forum

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

Web page could not Rendering large pivot table data #2150

Open Rohit Savaliya opened this topic on on Jun 19, 2021 - 15 comments

Rohit Savaliya commented on Jun 19, 2021

I use 2D pivot table for rendering data from mysql databse table, it is work fine in 50 row of data but if i try query without limit (which has 40,000 rows) it is stuck to load page and show me,

Fatal error: Allowed memory size exhausted in vendor\koolreport\pivot\PivotUtil.php on line 360 and Fatal error: Allowed memory size exhausted in vendor\laravel\framework\src\Illuminate\Auth\SessionGuard.php on line 486

i just fetch one month data , if i fetch a year data then there will be more rows may be in lakes.

Please give us guidance on this issue.

Sebastian Morales commented on Jun 21, 2021
Rohit Savaliya commented on Jun 21, 2021

I try this in my code but it show me Class 'App\Reports\FirstTimeFix\PivotSQL' not found I use Laravel framwork for referance i post code so you can refer

FirstTimeFixReport.php


        $Query = "SELECT * FROM database_table"; // this return more then 40,000 records
        
        $node = $this->src("mysql")->query($Query)
        ->pipe(new ColumnMeta(array(            
            "engineer_rating_c"=>array(
                "type"=>"number",
                "decimals"=>2,
            ) 
        )))
        ->pipe(new PivotSQL(array(
            "dimensions"=>array(
                "column" => "dateyear",
                "row" => "users_zone_c",
            ),
            "aggregates"=>array(
                "sum" => "quantity_c",
                "count" => "quantity_c",
            ),
        )))
        ->pipe(new Pivot2D(array(
            "dimensions"=>array(
                "column" => "dateyear, product_name, cateName, brand, csctype, engname",
                "row" => "users_zone_c,address_state,source,CSCName",
            ),
            "aggregates"=>array(
                "sum" => "quantity_c,ftmfixed,ClosejsPercent,FirsttimefixPercent",
                "count" => "quantity_c",
            ),
			"fieldDelimiter" => $delimiter 
        )))
       ->pipe(new Map(array(
        '{value}' => function ($row) use ($delimiter) {
            foreach ($row as $colName => $colValue) {
                    $colFields = substr($colName, 0, strrpos($colName, " || "));
                    $customClosejsColName =  $colFields . " || ClosejsPercent - sum";
                    if($customClosejsColName!==' || ClosejsPercent - sum'){
                        $row[$customClosejsColName] = $row[$colFields . $delimiter . "quantity_c - sum"] != 0 ? $row[$colFields . $delimiter . "quantity_c - sum"]*100 / $row[$colFields . $delimiter . "quantity_c - sum"] : 0;
                    }
                    
                    $customFirstTimeFixColName =  $colFields . " || FirsttimefixPercent - sum";
                    if($customFirstTimeFixColName!==' || FirsttimefixPercent - sum'){
                        $row[$customFirstTimeFixColName] = $row[$colFields . $delimiter . "ftmfixed - sum"] != 0 ? $row[$colFields . $delimiter . "ftmfixed - sum"]*100 / $row[$colFields . $delimiter . "quantity_c - sum"] : 0;
                    }
            }
            return $row; 
        },
        '{meta}' => function($meta) {
            $meta['columns']['ClosejsPercent - sum'] = array(
                'type' => 'number',
                'decimals' => 2,
            );
            $meta['columns']['FirsttimefixPercent - sum'] = array(
                'type' => 'number',
                'decimals' => 2,
                'suffix' => '%',
            );
            return $meta;
        }
        ))) 
        ->pipe($this->dataStore('FirstTimeFix')); 
    } 
    function getAllIndex($rowLabel)
    {
        $labelParts = explode(" || ", $rowLabel);
        $allIndex = 0;
        foreach ($labelParts as $part) {
            if ($part === "{{all}}") break; // I made a mistake by using !== here in the previous code
            $allIndex++;
        }
        return $allIndex;
    } 
    function findNextTotalRow($rowLabel, $pivotData)
    {
        $rowAllIndex = $this->getAllIndex($rowLabel);
        $labelParts = explode(" || ", $rowLabel);
        foreach ($pivotData as $otherRow) {
            $otherRowLabel = $otherRow["label"];
            $otherRowAllIndex = $this->getAllIndex($otherRowLabel);
            $otheLabelParts = explode(" || ", $otherRowLabel);
            $isNextTotalRow = true;
            if ($rowAllIndex === 0) {
                if ($otherRowAllIndex !== 0) $isNextTotalRow = false;
            } else if ($otherRowAllIndex !== $rowAllIndex - 1) {
                $isNextTotalRow = false;
            } else { // $otherRowAllIndex === $rowAllIndex - 1
                for ($i = 0; $i < $rowAllIndex - 1; $i++) {
                    if ($labelParts[$i] !== $otheLabelParts[$i]) $isNextTotalRow = false;
                }
            }
            if ($isNextTotalRow) return $otherRow;
        }
    }

This is View file FirstTimeFixReport.view.php

PivotMatrix::create(array(
    "id" => "pivotMatrix1",
    'dataSource' => $this->dataStore('FirstTimeFix'),
    'showDataHeaders' => true,
    "scope" => array(
        "reportType" => $this->params["reportType"],
        "zone" => $this->params["zone"],  // and so on....
    ),
    "measures"=>array(
        "quantity_c - sum",        
        "ftmfixed - sum",
		"ClosejsPercent - sum",
		"FirsttimefixPercent - sum",
    ),
    'rowSort' => array(
        'quantity_c - sum' => 'desc',
        'cateName' => 'desc',
    ),
    'columnSort' => array(
        'datemonth' => function($a, $b) {
            return (int)$a < (int)$b;
        },
        'quarter_col' => function($a, $b) {
            return (int)$a < (int)$b;
        },
    ),
    'columnCollapseLevels' => array(0),
    'rowCollapseLevels' => array(0),
    'width' => '100%',
    'height' => '500px',
    'headerMap' => function($v, $f) {
        switch ($v) {
            case 'quantity_c - sum': return 'Closed JS';
            case 'ftmfixed - sum' : return 'First Time Fixed';
			case 'ClosejsPercent - sum' : return 'Close Js%'; // and so on....
        }
        $r = $v;
        if ($f === 'dateyear')
            $r = 'Year ' . $v;                                        
        $map = array(
            '1' => 'January',
            '2' => 'February',
            // and so on....
        );

        $map_quart = array(
            '1' => 'First',
            '2' => 'Second',
            // and so on....
        );
        if ($f === 'quarter_col')
            $r = $map_quart[$v];
        if ($f === 'datemonth')
            $r = $map[$v];
        return $r;
    },
    'totalName' => 'All',
    'waitingFields' => array(
        'datemonth' => 'label',
        'quarter_col' => 'label', 
    ),
    'paging' => array(
        'size' => 5,
        'maxDisplayedPages' => 5,
        'sizeSelect' => array(5, 10, 20, 50, 100)
    )
));

this is for excel file FirstTimeFixReportExcel.view.php

PivotTable::create(array(
				"dataSource" => 'FirstTimeFix',
			)
            ); 

hope you can help us

Sebastian Morales commented on Jun 22, 2021

The class path for PivotSQL should be \koolreport\pivot\processes\PivotSQL. Rgds,

Rohit Savaliya commented on Jun 23, 2021

I install kool-report using composer and there is no file named PivotSQL.php in vendor\koolreport\pivot\processes\ Directory. Please tell me where can i download this file, i also try download tar.zip file and try to fond this file but cant. Please guide us.

Sebastian Morales commented on Jun 24, 2021

Which version of Pivot you are using? Tks,

Rohit Savaliya commented on Jun 24, 2021

It is version : 6.2.1

Rohit Savaliya commented on Jun 24, 2021

Please guide me, any help regarding this will be appropriate.

Rohit Savaliya commented on Jun 26, 2021

any information will help us lot. please help us in this issue.

Sebastian Morales commented on Jun 28, 2021

I think that the PivotSQL process was added in Pivot package version 7.0.0. Is there any chance you can update the package to version 7.0.0? Tks,

Rohit Savaliya commented on Jun 28, 2021

while update using composer its showing

Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Root composer.json requires koolreport/pro, it could not be found in any version, there may be a typo in the package name.
  Problem 2
    - Root composer.json requires koolreport/export, it could not be found in any version, there may be a typo in the package name.

Potential causes:
 - A typo in the package name
 - The package is not available in a stable-enough version according to your minimum-stability setting
   see <https://getcomposer.org/doc/04-schema.md#minimum-stability> for more details.
 - It's a private package and you forgot to add a custom repository to find it

what should i need to do ?

I follow these steps

Sebastian Morales commented on Jun 28, 2021

Pls check your license's valid time for free upgrade. It's normally one year after purchasing. You can not update to newer versions released after that time unless you renew the license. If you need more detail pls email to sales@koolreport.com. Tks,

Rohit Savaliya commented on Jun 28, 2021

I update the version and now it run, but its show me an error bellow

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where 1=0' at line 1

On File

vendor\koolreport\core\src\datasources\PdoDataSource.php:423

after seeing this error i echo the query which is fire in same line is bellow

pdodatasource start query=select 0 where 1=0

Please help me to fix this. My query is absolutely fine and work in Mysql with result

Sebastian Morales commented on Jun 29, 2021

Pls open the file koolreport/pivot/processes/PivotSQL.php and replace the following line:

$finalSource->query('select 0 where 1=0');

with this one:

$finalSource->query('select 0');

Then try it again and let us know the result. Tks,

Rohit Savaliya commented on Jun 29, 2021

Ok now error is not show but there is a problem in to get fields in PivotSql rather then Pivot2D. when i use Pivot2d it work but not with PivotSql i show you the code for reference.

Worked in Pivot2D

 
$node = $this->src("mysql")->query($Query)
->pipe(new ColumnMeta(array(            
    "engineer_rating_c"=>array(
        "type"=>"number",
        "decimals"=>2,
    ) 
)))   
->pipe(new Pivot2D(array(
    "dimensions"=>array(
        "column" => "dateyear, product_name, cateName, brand, csctype, engname",
        "row" => "users_zone_c,address_state,source,CSCName",
    ),
    "aggregates"=>array(
        "sum" => "quantity_c,ftmfixed,ClosejsPercent,FirsttimefixPercent",
        "count" => "quantity_c",
    ),
    "fieldDelimiter" => $delimiter 
)))
->pipe(new Map(array(
'{value}' => function ($row) use ($delimiter) {
    foreach ($row as $colName => $colValue) {
            $colFields = substr($colName, 0, strrpos($colName, " || "));
            $customClosejsColName =  $colFields . " || ClosejsPercent - sum";
            if($customClosejsColName!==' || ClosejsPercent - sum'){
                $row[$customClosejsColName] = $row[$colFields . $delimiter . "quantity_c - sum"] != 0 ? $row[$colFields . $delimiter . "quantity_c - sum"]*100 / $row[$colFields . $delimiter . "quantity_c - sum"] : 0;
            }
            
            $customFirstTimeFixColName =  $colFields . " || FirsttimefixPercent - sum";
            if($customFirstTimeFixColName!==' || FirsttimefixPercent - sum'){
                $row[$customFirstTimeFixColName] = $row[$colFields . $delimiter . "ftmfixed - sum"] != 0 ? $row[$colFields . $delimiter . "ftmfixed - sum"]*100 / $row[$colFields . $delimiter . "quantity_c - sum"] : 0;
            }
    }
    
    return $row; 
},
'{meta}' => function($meta) {
    $meta['columns']['ClosejsPercent - sum'] = array(
        'type' => 'number',
        'decimals' => 2,
    );
    $meta['columns']['FirsttimefixPercent - sum'] = array(
        'type' => 'number',
        'decimals' => 2,
        'suffix' => '%',
    );
    return $meta;
}
)))
->pipe($this->dataStore('FirstTimeFix')); 

Not Worked using PivotSql after open filter

 
$node = $this->src("mysql")->query($Query)
->pipe(new ColumnMeta(array(            
    "engineer_rating_c"=>array(
        "type"=>"number",
        "decimals"=>2,
    ) 
)))
 ->pipe(new PivotSQL(array(
    "dimensions"=>array(
        "column" => "dateyear, product_name, cateName, brand, csctype, engname",
        "row" => "users_zone_c,address_state,source,CSCName",
    ),
    "aggregates"=>array(
        "sum" => "quantity_c,ftmfixed",
        "count" => "quantity_c",
    ),
    "fieldDelimiter" => $delimiter
)))       
->pipe(new Map(array(
'{value}' => function ($row) use ($delimiter) {
    foreach ($row as $colName => $colValue) {
            $colFields = substr($colName, 0, strrpos($colName, " || "));
            $customClosejsColName =  $colFields . " || ClosejsPercent - sum";
            if($customClosejsColName!==' || ClosejsPercent - sum'){
                $row[$customClosejsColName] = $row[$colFields . $delimiter . "quantity_c - sum"] != 0 ? $row[$colFields . $delimiter . "quantity_c - sum"]*100 / $row[$colFields . $delimiter . "quantity_c - sum"] : 0;
            }
            
            $customFirstTimeFixColName =  $colFields . " || FirsttimefixPercent - sum";
            if($customFirstTimeFixColName!==' || FirsttimefixPercent - sum'){
                $row[$customFirstTimeFixColName] = $row[$colFields . $delimiter . "ftmfixed - sum"] != 0 ? $row[$colFields . $delimiter . "ftmfixed - sum"]*100 / $row[$colFields . $delimiter . "quantity_c - sum"] : 0;
            }
    }
    return $row; 
},
'{meta}' => function($meta) {
    $meta['columns']['ClosejsPercent - sum'] = array(
        'type' => 'number',
        'decimals' => 2,
    );
    $meta['columns']['FirsttimefixPercent - sum'] = array(
        'type' => 'number',
        'decimals' => 2,
        'suffix' => '%',
    );
    return $meta;
}
)))
->pipe($this->dataStore('FirstTimeFix')); 

the error is

Column not found: 1054 Unknown column 'ClosejsPercent' in 'field list' in file \vendor\koolreport\core\src\datasources\PdoDataSource.php on line 590

even i not mention this field in PivotSql

Please help me in this issue.

Sebastian Morales commented on Jun 29, 2021

This is because your "ClosejsPercent" is a created column instead of a real column in sql. We will find way for PivotSQL to omit such created columns and get back to you. Tks,

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
None yet

None