KoolReport's Forum

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

Calculate percentage according to next total value #1382

mysql pivot mysql pivot table php pivot table php pivot table mysql pivot table sql sql pivot
Open ankit opened this topic on on Apr 10, 2020 - 33 comments
ankit commented on Apr 10, 2020

Dear I want to calculate percentage value according to next total value not last total value please tell me what i missing. according to screen shot i want percent value of 13 not 26.

->pipe(new Pivot2D(array(

        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName,open_from",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c",
            'sum percent' => 'quantity_c'
        )
    )))

<?php PivotMatrix::create(array(

"id" => "pivotMatrix1",
//'hideSubtotalRow' => true,
//'hideSubtotalColumn' => true,
//'showDataHeaders' => true,
'dataSource' => $this->dataStore('tat1Field'),
"scope" => array(
    "reportType" => $this->params["reportType"],
    "zone" => $this->params["zone"],
    "state" => $this->params["state"],
    "csc" => $this->params["csc"],
    "sourceCall" => $this->params["sourceCall"],
    "brand" => $this->params["brand"],
    "fromDate" => $this->params["fromDate"],
    "toDate" => $this->params["toDate"],
    "year" => $this->params["year"],
    "month" => $this->params["month"],
    "quarter" => $this->params["quarter"],
    
),
"measures"=>array(
    "quantity_c - sum", 
    "quantity_c - sum percent", 
),
'rowSort' => array(
    'quantity_c - sum' => 'desc',
    'cateName' => 'desc',
),
'columnSort' => array(
    'datemonth' => function($a, $b) {
        return (int)$a < (int)$b;
    },
    'quarter' => function($a, $b) {
        return (int)$a < (int)$b;
    },
),
'columnCollapseLevels' => array(0,1,2,3),
'rowCollapseLevels' => array(0,1,2,3),
'width' => '100%',
'height' => '500px',

'headerMap' => function($v, $f) {
    switch ($v) {
        case 'quantity_c - sum': return 'Total Quantity';
        case 'quantity_c - sum percent': return 'Percent';
        case 'dateyear': return 'Year';
        case 'quarter': return 'Quarter';
        case 'datemonth': return 'Month';
        case 'daymonth': return 'DAY';
        case 'user_name': return 'User Name';
        case 'cateName': return 'Category';
    }
    $r = $v;
    if ($f === 'dateyear')
        $r = 'Year ' . $v;                                        
    $map = array(
        '1' => 'January',
        '2' => 'February',
        '3' => 'March',
        '4' => 'April',
        '5' => 'May',
        '6' => 'June',
        '7' => 'July',
        '8' => 'August',
        '9' => 'September',
        '10' => 'October',
        '11' => 'November',
        '12' => 'December',
    );

    $map_quart = array(
        '1' => 'First',
        '2' => 'Second',
        '3' => 'Third',
        '4' => 'Forth'
    );
    if ($f === 'quarter')
        $r = $map_quart[$v];
    if ($f === 'datemonth')
        $r = $map[$v];
    return $r;
},
'totalName' => 'All Jobsheet',
'waitingFields' => array(        
    'datemonth' => 'label',
    'quarter' => 'label'                                        
    /*'daymonth' => 'label'*/
),
'paging' => array(
    'size' => 100,
    'maxDisplayedPages' => 100,
    'sizeSelect' => array(5, 10, 20, 50, 100)
)

)); ?> (https://cdn.koolreport.com/assets/images/editor/c4/image5e90054d6c95c.png)

ankit commented on Apr 11, 2020

please reply me

David Winterburn commented on Apr 13, 2020

Hi Ankit,

Using Pivot2D process together with Map process is very useful here to help you customize pivot result. I would suggest the following approach:


...
->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName,open_from",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c"
        )
    ))) 
->pipe($this->dataStore('temp'))
->requestDataSending();

$pivotData = $this->dataStore('temp')->data();

->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName,open_from",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c"
        )
    ))) 
->pipe(new Map(array(
    "{value}" => function($row) use ($pivotData) {
        foreach ($row as $colName => $colValue) {
            $rowLabel = $row["label"];
            if ($colName !== "label") { //$colName is like "2020 || quantity_c - sum"
                $nextTotalRow = findNextTotalRow($rowLabel, $pivotData);
                $customSumPercentColName = substr($colName, 0, strrpos($colName, " || ") //remove  "|| quantity_c - sum"
                    . " || " . quantity_c - custom sum percent"; //add  "|| quantity_c - custom sum percent"
                $row[$customSumPercentColName] = $row[$colName] / $nextTotalRow[$colName];            
            }
        }
        return $row;
    } 
)))
...
function getAllIndex($rowLabel) {
    $labelParts = explode(" || ", $rowLabel);
    $allIndex = -1;
    foreach ($labelParts as $i => $part) {
        $allIndex = $i;
        if ($part === "{{all}}") break;
    }
    return $allIndex;
}
function findNextTotalRow($rowLabel, $pivotData) {
    $rowAllIndex = getAllIndex($rowLabel);
    $labelParts = explode(" || ", $rowLabel);
    foreach ($pivotData as $otherRow) {
        $otherRowLabel = $otherRow["label"];
        $otherRowAllIndex = 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; $i++) {
                if ($labelParts[$i] !== $otheLabelParts[$i]) $isNextTotalRow = false;
            }
        }
        if ($isNextTotalRow) return $otherRow;
    }    
}

This gives the general idea to compute your custom sum percent. There could be some grammar bugs i nthe code. Please try this method and let us know the result.

ankit commented on May 1, 2020

Hi i am newbie not understand this solution can you please provide sample code thanks for responce percent value not coming when i use above code :

->pipe(new Pivot2D(array(

			"dimensions"=>array(
				"column" => "dateyear",
				"row" => "users_zone_c, address_state, CSCName, cateName,open_from",
			),
			"aggregates"=>array(
				"sum" => "quantity_c"
			)
		))) 
	->pipe($this->dataStore('tat1Field'))
	->requestDataSending();

	$pivotData = $this->dataStore('tat1Field')->data();
	$node->pipe(new Pivot2D(array(
		"dimensions"=>array(
			"column" => "dateyear",
			"row" => "users_zone_c, address_state, CSCName, cateName,open_from",
		),
		"aggregates"=>array(
			"sum" => "quantity_c",
		)
	))) 
	->pipe(new Map(array(
		"{value}" => function($row) use ($pivotData) {
			foreach ($row as $colName => $colValue) {
				$rowLabel = $row["label"];
				if ($colName !== "label") { //$colName is like "2020 || quantity_c - sum"
					$nextTotalRow = findNextTotalRow($rowLabel, $pivotData);
					$customSumPercentColName = substr($colName, 0, strrpos($colName, " || ")); 
					//remove  "|| quantity_c - sum" . " || " . quantity_c - custom sum percent"; //add  "|| quantity_c - custom sum percent"
					$row[$customSumPercentColName] = $row[$colName] / $nextTotalRow[$colName];            
				}
			}
			return $row;
		} 
	)))
	->pipe($this->dataStore('tat1Field'));
David Winterburn commented on May 4, 2020

Did you add function getAllIndex and findNextTotalRow in your report's setup? If you did and it still didn't work try putting those functions inside the Map process. And by the way, please turn on all errors/notices when developing with this command to see if there's anything wrong:

error_reporting(E_ALL);
ankit commented on May 5, 2020

Can you provide me sample code with inside function ? I am confuse with $row what is this and where is define what value is store because when i print $row nothing is print or if you have documantation of 2D pivot and example link

David Winterburn commented on May 5, 2020

We provided the functions for clearer purpose in the previous post. Try to put these functions inside the Map process:

function getAllIndex($rowLabel) {
    $labelParts = explode(" || ", $rowLabel);
    $allIndex = -1;
    foreach ($labelParts as $i => $part) {
        $allIndex = $i;
        if ($part === "{{all}}") break;
    }
    return $allIndex;
}
function findNextTotalRow($rowLabel, $pivotData) {
    $rowAllIndex = getAllIndex($rowLabel);
    $labelParts = explode(" || ", $rowLabel);
    foreach ($pivotData as $otherRow) {
        $otherRowLabel = $otherRow["label"];
        $otherRowAllIndex = 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; $i++) {
                if ($labelParts[$i] !== $otheLabelParts[$i]) $isNextTotalRow = false;
            }
        }
        if ($isNextTotalRow) return $otherRow;
    }    
}

Regarding your question, $row refers to the current processing row of data flow which passes through the Map process. To understand the Pivot2D process better, try to use a Table or DataTables widget to show its data instead of the PivotTable.

ankit commented on May 6, 2020

Hi Devid Pease looks once in my code and tell me what i missing I have successfully as function in code and also call successfully no issues with map function. when i do according to your solution its add a new column with year name please check once what I missing I hope Mr. Karl remind you for this problem

LOGIC PART

$node = $this->src("mysql")->query($Query)

    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName,open_from",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c",
        )
    )));
	$node->pipe($this->dataStore('temp'))->requestDataSending();

	$pivotData = $this->dataStore('temp')->data();
	
	$node2 = $this->src("mysql")->query($Query)
	->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName,open_from",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c",
        )
    )))->pipe(new Map(array(
        '{value}' => function($row) use ($pivotData) {
			foreach ($row as $colName => $colValue) {
				$rowLabel = $row["label"];
				if ($colName !== "label") {
					$nextTotalRow = $this->findNextTotalRow($rowLabel, $pivotData);
					$customSumPercentColName = substr($colName, 0, strrpos($colName, " || ")); 
					$row[$customSumPercentColName] = $row[$colName] / $nextTotalRow[$colName];
				}
			}
			echo '<pre>';print_r($row);
			return $row;
        },
    )))
    ->pipe($this->dataStore('tat1Field'));
}

function getAllIndex($rowLabel) {
	$labelParts = explode(" || ", $rowLabel);
	$allIndex = -1;
	foreach ($labelParts as $i => $part) {
		$allIndex = $i;
		if ($part === "{{all}}") break;
	}
	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; $i++) {
				if ($labelParts[$i] !== $otheLabelParts[$i]) $isNextTotalRow = false;
			}
		}
		if ($isNextTotalRow) return $otherRow;
	}    
}

VIEW PART <?php PivotMatrix::create(array(

"id" => "pivotMatrix1",
//'hideSubtotalRow' => true,
//'hideSubtotalColumn' => true,
'showDataHeaders' => true,
'dataSource' => $this->dataStore('tat1Field'),
"scope" => array(
    "reportType" => $this->params["reportType"],
    "zone" => $this->params["zone"],
    "state" => $this->params["state"],
    "csc" => $this->params["csc"],
    "sourceCall" => $this->params["sourceCall"],
    "brand" => $this->params["brand"],
    "fromDate" => $this->params["fromDate"],
    "toDate" => $this->params["toDate"],
    "year" => $this->params["year"],
    "month" => $this->params["month"],
    "quarter" => $this->params["quarter"],
    
),
"measures"=>array(
    "quantity_c - sum", 
    "quantity_c - sum percent", 
),
'rowSort' => array(
    'quantity_c - sum' => 'desc',
    'cateName' => 'desc',
),
'columnSort' => array(
    'datemonth' => function($a, $b) {
        return (int)$a < (int)$b;
    },
    'quarter' => function($a, $b) {
        return (int)$a < (int)$b;
    },
),
'columnCollapseLevels' => array(0,1,2,3),
'rowCollapseLevels' => array(0,1,2,3),
'width' => '100%',
'height' => '500px',

'headerMap' => function($v, $f) {
    switch ($v) {
        case 'quantity_c - sum': return 'Total Quantity';
        case 'quantity_c - sum percent': return 'Percent';
        case 'dateyear': return 'Year';
        case 'quarter': return 'Quarter';
        case 'datemonth': return 'Month';
        case 'daymonth': return 'DAY';
        case 'user_name': return 'User Name';
        case 'cateName': return 'Category';
    }
    $r = $v;
    if ($f === 'dateyear')
        $r = 'Year ' . $v;                                        
    $map = array(
        '1' => 'January',
        '2' => 'February',
        '3' => 'March',
        '4' => 'April',
        '5' => 'May',
        '6' => 'June',
        '7' => 'July',
        '8' => 'August',
        '9' => 'September',
        '10' => 'October',
        '11' => 'November',
        '12' => 'December',
    );

    $map_quart = array(
        '1' => 'First',
        '2' => 'Second',
        '3' => 'Third',
        '4' => 'Forth'
    );
    if ($f === 'quarter')
        $r = $map_quart[$v];
    if ($f === 'datemonth')
        $r = $map[$v];
    return $r;
},
'totalName' => 'All Jobsheet',
'waitingFields' => array(        
    'datemonth' => 'label',
    'quarter' => 'label' ,
    'product_name' => 'label',  
    'source' => 'label',  
    'brand' => 'label'  
	
    /*'daymonth' => 'label'*/
),
'paging' => array(
    'size' => 100,
    'maxDisplayedPages' => 100,
    'sizeSelect' => array(5, 10, 20, 50, 100)
)

)); ?>

David Winterburn commented on May 6, 2020

Turn on all your error/notice message with this command at the beginning your report:

error_reporting(E_ALL);

Run the report again to see if there's any error/notice message.

ankit commented on May 6, 2020

I have add error reporting what not display any error.

This is sample data in mysql

and what I want

code shared already please look once where i am wrong

David Winterburn commented on May 7, 2020

Ok, in the Map process function you missed the " || custom sum percent" suffix. Please use this exactly:

        ->pipe(new Map(array(
            '{value}' => function ($row) use ($pivotData) {
                foreach ($row as $colName => $colValue) {
                    $rowLabel = $row["label"];
                    if ($colName !== "label") {
                        $nextTotalRow = $this->findNextTotalRow($rowLabel, $pivotData);
                        $customSumPercentColName = substr($colName, 0, strrpos($colName, " || ")) . " || quantity_c - custom sum percent";
                        $row[$customSumPercentColName] = $nextTotalRow[$colName] != 0 ? $row[$colName] / $nextTotalRow[$colName] : 0;
                    }
                }
                return $row;
            },
        )))

And use the "getAllIndex" and "findNextTotalRow" functions as methods of the report like this:

class MyReport extends koolreport\KoolReport
{
    ...
    public function setup()
    {
        //pivot, map processes are here
    }

    function getAllIndex($rowLabel)
    {
        $labelParts = explode(" || ", $rowLabel);
        $allIndex = -1;
        foreach ($labelParts as $i => $part) {
            $allIndex = $i;
            if ($part === "{{all}}") break;
        }
        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;
        }
    }

Finally in the report view file, specify the custom sum percent measure like this:

"measures"=>array(
    "quantity_c - sum", 
    "quantity_c - custom sum percent", 
),

Let us know the result.

ankit commented on May 7, 2020

Hi Devid I have add code as per your instruction but still blank data see :

David Winterburn commented on May 8, 2020

Please post your full php code here.

ankit commented on May 8, 2020

Logic part : function setup () {

    $Query   = "";

    $node = $this->src("mysql")->query($Query)
    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName,open_from",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c",
        )
    )));
	$node->pipe($this->dataStore('temp'))->requestDataSending();

	$pivotData = $this->dataStore('temp')->data();
	
	$node2 = $this->src("mysql")->query($Query)
	->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName,open_from",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c",
        )
    )))->pipe(new Map(array(
        '{value}' => function ($row) use ($pivotData) {
            foreach ($row as $colName => $colValue) {
                $rowLabel = $row["label"];
                if ($colName !== "label") {
                    $nextTotalRow = $this->findNextTotalRow($rowLabel, $pivotData);
                    $customSumPercentColName = substr($colName, 0, strrpos($colName, " || ")) . " || quantity_c - custom sum percent";
                    $row[$customSumPercentColName] = $nextTotalRow[$colName] != 0 ? $row[$colName] / $nextTotalRow[$colName] : 0;
                }
            }
            return $row;
        },
    ))) 
    ->pipe($this->dataStore('tat1Field'));
}

function getAllIndex($rowLabel)
{
    $labelParts = explode(" || ", $rowLabel);
    $allIndex = -1;
    foreach ($labelParts as $i => $part) {
        $allIndex = $i;
        if ($part === "{{all}}") break;
    }
    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;
    }
}

View Part : <?php PivotMatrix::create(array(

"id" => "pivotMatrix1",
'dataSource' => $this->dataStore('tat1Field'),
"measures"=>array(
    "quantity_c - sum", 
    "quantity_c - custom sum percent", 
),
'rowSort' => array(
    'quantity_c - sum' => 'desc',
    'cateName' => 'desc',
),
'columnSort' => array(
    'datemonth' => function($a, $b) {
        return (int)$a < (int)$b;
    },
    'quarter' => function($a, $b) {
        return (int)$a < (int)$b;
    },
),
'columnCollapseLevels' => array(0,1,2,3),
'rowCollapseLevels' => array(0,1,2,3),
'width' => '100%',
'height' => '500px',

'headerMap' => function($v, $f) {
    switch ($v) {
        case 'quantity_c - sum': return 'Total Quantity';
        case 'quantity_c - sum percent': return 'Percent';
        case 'dateyear': return 'Year';
        case 'quarter': return 'Quarter';
        case 'datemonth': return 'Month';
        case 'daymonth': return 'DAY';
        case 'user_name': return 'User Name';
        case 'cateName': return 'Category';
    }
    $r = $v;
    if ($f === 'dateyear')
        $r = 'Year ' . $v;                                        
    $map = array(
        '1' => 'January',
        '2' => 'February',
        '3' => 'March',
        '4' => 'April',
        '5' => 'May',
        '6' => 'June',
        '7' => 'July',
        '8' => 'August',
        '9' => 'September',
        '10' => 'October',
        '11' => 'November',
        '12' => 'December',
    );

    $map_quart = array(
        '1' => 'First',
        '2' => 'Second',
        '3' => 'Third',
        '4' => 'Forth'
    );
    if ($f === 'quarter')
        $r = $map_quart[$v];
    if ($f === 'datemonth')
        $r = $map[$v];
    return $r;
},
'totalName' => 'All Jobsheet',
'waitingFields' => array(        
    'datemonth' => 'label',
    'quarter' => 'label' ,
    'product_name' => 'label',  
    'source' => 'label',  
    'brand' => 'label'  
	
    /*'daymonth' => 'label'*/
),
'paging' => array(
    'size' => 100,
    'maxDisplayedPages' => 100,
    'sizeSelect' => array(5, 10, 20, 50, 100)
)

)); ?>

please tell me if any other things is required.

David Winterburn commented on May 8, 2020

Please try to add this field delimiter to Pivot2D like beblow and let us know the result:

    $delimiter = " || "; //add this
    $node = $this->src("mysql")->query($Query)
    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName,open_from",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c",
        ),
        "fieldDelimiter" => $delimiter //add this
    )));
	$node->pipe($this->dataStore('temp'))->requestDataSending();

	$pivotData = $this->dataStore('temp')->data();
	
	$node2 = $this->src("mysql")->query($Query)
	->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName,open_from",
        ),
        "aggregates"=>array(
            "sum" => "quantity_c",
        ),
        "fieldDelimiter" => $delimiter //add this
    )))->pipe(new Map(array(
        '{value}' => function ($row) use ($pivotData) {
            foreach ($row as $colName => $colValue) {
                $rowLabel = $row["label"];
                if ($colName !== "label") {
                    $nextTotalRow = $this->findNextTotalRow($rowLabel, $pivotData);
                    $customSumPercentColName = substr($colName, 0, strrpos($colName, " || ")) . " || quantity_c - custom sum percent";
                    $row[$customSumPercentColName] = $nextTotalRow[$colName] != 0 ? $row[$colName] / $nextTotalRow[$colName] : 0;
                }
            }
            return $row;
        },
    ))) 
    ->pipe($this->dataStore('tat1Field'));
ankit commented on May 8, 2020

You are genius david 

its working fine please tell me one more things please i want modify column format like add % suffix and get only value with only two decimal after point please see attached images.

I really thanks to you again David

David Winterburn commented on May 8, 2020

That's great, Ankit. To make custom percent column look like that, try this:

        ->pipe(new Map(array(
            '{value}' => function ($row) use ($pivotData) {
                $rowLabel = $row["label"];
                $nextTotalRow = $this->findNextTotalRow($rowLabel, $pivotData);
                foreach ($row as $colName => $colValue) {
                    if ($colName !== "label") {
                        $customSumPercentColName = substr($colName, 0, strrpos($colName, " || ")) . " || quantity_c - custom sum percent";
                        $row[$customSumPercentColName] = Util::get($nextTotalRow, $colName, 0) != 0 ? 100 * $row[$colName] / Util::get($nextTotalRow, $colName, 0) : 0; //Multiply custom sum percent by 100 here
                    }
                }
                
                return $row;
            },
            '{meta}' => function($meta) {
                $meta['columns']['quantity_c - custom sum percent'] = [
                    'type' => 'number',
                    'decimals' => 2,
                    'suffix' => '%',
                ]; //set custom sum percent meta here
                return $meta;
            }
        )))
ankit commented on May 8, 2020

That good David its working fine thanks a lot for the support I'll remember you always I hope you help me as same in future. please tell me one thing can you share me any example or documentation where we can check how to use data from mutiple query I mean how I can use multiple query for getting data because many time its not possible to get data from single query. So i want to know how to manage and merge custom data or result of more than on query

KoolReport commented on May 8, 2020

In the setup() method, you can define as many queries as you want.

$this->src("mysql")->query("query1")
->pipe($this->dataStore("store1");
...

$this->src("mysql")->query("query2")
->pipe($this->dataStore("store2");
...

One thing, the setup() function is where you put query and define the datapipe line, at that time data will not be available. The data is started to flow when we call run() method. At that time query will be executed and data started to flow through all the processed until it reaches the end at dataStore.

We will try to keep a forum topic for single question/problem/issue. You may create many forum topics, each for single question. It is great that this above problem has been solved :) so we will close this topic. For sure that we will continue to support, please do not hesitate to create new topic when you have question.

ankit commented on May 19, 2020

Hi David sorry i again disturb you please help me in same topic now i want calculate percentage basis on custom column please see attached images In this i have calculate some custom column and i want percentage of that custom column values :

My Logic part code : $delimiter = " || ";

	$node = $this->src("mysql")->query($Query)
	->pipe(new CalculatedColumn(array(
        "total"=>"{bf}+{quantity_c}",
    )))
	->pipe(new CalculatedColumn(array(
        "total_closed"=>"{SameDay}+{A (0-24hrs)}+{B (24-48hrs)}+{C (48-72hrs)}+{D (72-120hrs)}+{E (120-240hrs)}",
    )))
	->pipe(new CalculatedColumn(array(
        "total_CF"=>"{total}-{total_closed}",
    )))
	
    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName",
        ),
        "aggregates"=>array(
            "sum" => "bf, quantity_c, total, total_closed, total_CF, SameDay, A (0-24hrs), B (24-48hrs), C (48-72hrs), D (72-120hrs), E (120-240hrs)",
        ),
		 "fieldDelimiter" => $delimiter
    )));
	$node->pipe($this->dataStore('temp'))->requestDataSending();

	$pivotData = $this->dataStore('temp')->data();

    $node2 = $this->src("mysql")->query($Query)
	->pipe(new CalculatedColumn(array(
        "total"=>"{bf}+{quantity_c}",
    )))
	->pipe(new CalculatedColumn(array(
        "total_closed"=>"{SameDay}+{A (0-24hrs)}+{B (24-48hrs)}+{C (48-72hrs)}+{D (72-120hrs)}+{E (120-240hrs)}",
    )))
	->pipe(new CalculatedColumn(array(
        "total_CF"=>"{total}-{total_closed}",
    )))
	
    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName",
        ),
        "aggregates"=>array(
            "sum" => "bf, quantity_c, total, total_closed, total_CF, SameDay, A (0-24hrs), B (24-48hrs), C (48-72hrs), D (72-120hrs), E (120-240hrs)",
        ),
		 "fieldDelimiter" => $delimiter
    )))
	->pipe(new Map(array(
    '{value}' => function ($row) use ($pivotData) {
        foreach ($row as $colName => $colValue) {
            $rowLabel = $row["label"];
			//echo $colName;
            if ($colName !== "label") {
                $nextTotalRow = $this->findNextTotalRow($rowLabel, $pivotData);
                $customSumPercentColName = substr($colName, 0, strrpos($colName, " || ")) . " || total_closed - custom sum percent";
                $row[$customSumPercentColName] = $nextTotalRow[$colName] != 0 ? $row[$colName]*100 / $nextTotalRow[$colName] : 0;
            }
        }
        return $row;
    },
	'{meta}' => function($meta) {
            $meta['columns']['total_closed - custom sum percent'] = [
                'type' => 'number',
                'decimals' => 2,
                'suffix' => '%',
            ]; //set custom sum percent meta here
            return $meta;
        }
	)))
    ->pipe($this->dataStore('tat1Field'));

function getAllIndex($rowLabel)

{
    $labelParts = explode(" || ", $rowLabel);
    $allIndex = -1;
    foreach ($labelParts as $i => $part) {
        $allIndex = $i;
        if ($part === "{{all}}") break;
    }
    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;
    }
}

and View part : "measures"=>array(

	"bf - sum",  
	"quantity_c - sum",
	"total - sum",
	"total_closed - sum",
	"total_closed - custom sum percent",
	"total_CF - sum",
	"SameDay - sum",  
    "A (0-24hrs) - sum",  
   "B (24-48hrs) - sum",  
   "C (48-72hrs) - sum",  
   "D (72-120hrs) - sum",  
   "E (120-240hrs) - sum",  
   
),

please tell me what i missing

David Winterburn commented on May 20, 2020

Please remove all other fields' sum other than total_closed's to see if the result is correct or not.

ankit commented on May 20, 2020

Yes its working when i remove other but i need others column value also for display

David Winterburn commented on May 20, 2020

Why do I still see other sum fields in your second screenshot? What did you change because this time the number of custom sum percent looks correct?

ankit commented on May 20, 2020

according to your suggestion i remove other field in sum like NOW : 

->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName",
        ),
        "aggregates"=>array(
            "sum" => "total_closed",
        ),
		 "fieldDelimiter" => $delimiter
    )))

Before : ->pipe(new Pivot2D(array(

    "dimensions"=>array(
        "column" => "dateyear",
        "row" => "users_zone_c, address_state, CSCName, cateName",
    ),
    "aggregates"=>array(
        "sum" => "bf, quantity_c, total, total_closed, total_CF, SameDay, A (0-24hrs), B (24-48hrs), C (48-72hrs), D (72-120hrs), E (120-240hrs)",
    ),
	 "fieldDelimiter" => $delimiter
)));
David Winterburn commented on May 21, 2020

Hi,

It looks like our last solution only works when there's one aggregated field. To make this work with multiple aggregated fields, please use the following Map process after Pivot2D:

->pipe(new Map(array(
    '{value}' => function ($row) use ($pivotData) {
        foreach ($row as $colName => $colValue) {
            $rowLabel = $row["label"];
            $isTotalClosed = stripos($colName, "total_closed") !== false;
            if ($isTotalClosed) {
                $nextTotalRow = $this->findNextTotalRow($rowLabel, $pivotData);
                $colFields = substr($colName, 0, strrpos($colName, " || "));
                $customSumPercentColName =  $colFields . " || total_closed - custom sum percent";
                $row[$customSumPercentColName] = $nextTotalRow[$colName] != 0 ? $row[$colName]*100 / $nextTotalRow[$colName] : 0;
            }
        }
        return $row; 
...

Let us the result. Thanks!

ankit commented on May 21, 2020

Hi David may be still some missing result still incorrect please look once :

Screen shot after change :

->pipe(new Pivot2D(array(

        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName",
        ),
        "aggregates"=>array(
            "sum" => "bf, quantity_c, total, total_closed, total_CF, SameDay, A (0-24hrs), B (24-48hrs), C (48-72hrs), D (72-120hrs), E (120-240hrs)",
        ),
		 "fieldDelimiter" => $delimiter
    )))
	->pipe(new Map(array(
	'{value}' => function ($row) use ($pivotData) {
		foreach ($row as $colName => $colValue) {
			$rowLabel = $row["label"];
			$isTotalClosed = stripos($colName, "total_closed") !== false;
			if ($isTotalClosed) {
				$nextTotalRow = $this->findNextTotalRow($rowLabel, $pivotData);
				$colFields = substr($colName, 0, strrpos($colName, " || "));
				$customSumPercentColName =  $colFields . " || total_closed - custom sum percent";
				$row[$customSumPercentColName] = $nextTotalRow[$colName] != 0 ? $row[$colName]*100 / $nextTotalRow[$colName] : 0;
			}
		}
		return $row; 
		},
		'{meta}' => function($meta) {
            $meta['columns']['total_closed - custom sum percent'] = [
                'type' => 'number',
                'decimals' => 2,
                'suffix' => '%',
            ]; //set custom sum percent meta here
            return $meta;
        }
	)))
    ->pipe($this->dataStore('tat1Field'));
David Winterburn commented on May 21, 2020

Please make sure the two Pivot2D in your setup are the same and use this updated getAllIndex() method:

    function getAllIndex($rowLabel)
    {
        $labelParts = explode(" || ", $rowLabel);
        $allIndex = 0;
        foreach ($labelParts as $part) {
            if ($part !== "{{all}}") break;
            $allIndex++;
        }
        return $allIndex;
    }
ankit commented on May 21, 2020

yes i using two pivot 2D but still wrong value after update getAllIndex also:

$delimiter = " || ";

	$node = $this->src("mysql")->query($Query)
	->pipe(new CalculatedColumn(array(
        "total"=>"{bf}+{quantity_c}",
    )))
	->pipe(new CalculatedColumn(array(
        "total_closed"=>"{SameDay}+{A (0-24hrs)}+{B (24-48hrs)}+{C (48-72hrs)}+{D (72-120hrs)}+{E (120-240hrs)}",
    )))
	->pipe(new CalculatedColumn(array(
        "total_CF"=>"{total}-{total_closed}",
    )))
	
    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName",
        ),
        "aggregates"=>array(
            "sum" => "bf, quantity_c, total, total_closed, total_CF, SameDay, A (0-24hrs), B (24-48hrs), C (48-72hrs), D (72-120hrs), E (120-240hrs)",
        ),
		 "fieldDelimiter" => $delimiter
    )));
	$node->pipe($this->dataStore('temp'))->requestDataSending();

	$pivotData = $this->dataStore('temp')->data();

    $node2 = $this->src("mysql")->query($Query)
	->pipe(new CalculatedColumn(array(
        "total"=>"{bf}+{quantity_c}",
    )))
	->pipe(new CalculatedColumn(array(
        "total_closed"=>"{SameDay}+{A (0-24hrs)}+{B (24-48hrs)}+{C (48-72hrs)}+{D (72-120hrs)}+{E (120-240hrs)}",
    )))
	->pipe(new CalculatedColumn(array(
        "total_CF"=>"{total}-{total_closed}",
    )))
	
    ->pipe(new Pivot2D(array(
        "dimensions"=>array(
            "column" => "dateyear",
            "row" => "users_zone_c, address_state, CSCName, cateName",
        ),
        "aggregates"=>array(
            "sum" => "bf, quantity_c, total, total_closed, total_CF, SameDay, A (0-24hrs), B (24-48hrs), C (48-72hrs), D (72-120hrs), E (120-240hrs)",
        ),
		 "fieldDelimiter" => $delimiter
    )))
	->pipe(new Map(array(
	'{value}' => function ($row) use ($pivotData) {
		foreach ($row as $colName => $colValue) {
			$rowLabel = $row["label"];
			$isTotalClosed = stripos($colName, "total_closed") !== false;
			if ($isTotalClosed) {
				$nextTotalRow = $this->findNextTotalRow($rowLabel, $pivotData);
				$colFields = substr($colName, 0, strrpos($colName, " || "));
				$customSumPercentColName =  $colFields . " || total_closed - custom sum percent";
				$row[$customSumPercentColName] = $nextTotalRow[$colName] != 0 ? $row[$colName]*100 / $nextTotalRow[$colName] : 0;
			}
		}
		return $row; 
		},
		'{meta}' => function($meta) {
            $meta['columns']['total_closed - custom sum percent'] = [
                'type' => 'number',
                'decimals' => 2,
                'suffix' => '%',
            ]; //set custom sum percent meta here
            return $meta;
        }
	)))
    ->pipe($this->dataStore('tat1Field'));

function getAllIndex($rowLabel)

{
    $labelParts = explode(" || ", $rowLabel);
    $allIndex = 0;
    foreach ($labelParts as $part) {
        if ($part !== "{{all}}") break;
        $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;
    }
}
David Winterburn commented on May 21, 2020

Please post the result screenshot.

ankit commented on May 22, 2020

screen shot code already sent

David Winterburn commented on May 22, 2020

I've made a silly mistake with getAllIndex function. Please use this new one:

    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;
    }

Let us know the result. Thanks!

ankit commented on May 22, 2020

great david its working but need one more things is that sum to total percent should be display also as 100% please see image for more clarification

David Winterburn commented on May 22, 2020

This is really tricky because the cell you want to show 100% when expanding need to show 59.02% when the row is collapsed as well. It would confuse users because a cell has different values between expanding and collapsing.

ankit commented on May 22, 2020

ok if its possibl please tell me please do some RnD

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
solved
Pivot

Designed and built with by KoolPHP Inc.

Code licensed MIT.