HI!
I'm having an issue with computation columns in a Pivot Matrix.
I use 2 computation columns evaluated from an aggregated column.
"aggregates"=>array(
"avg"=>"item_grade",
),
"computations" => array(
"item_grade_pos" => function($aggRow) {
return $aggRow["item_grade - avg"] <= 0.0 ? 0.0 : $aggRow["item_grade - avg"];
},
"DigCompEduLevel" => function($aggRow) {
return todigcompedulevel($aggRow["item_grade - avg"]);
}
),
I need to show the computations but not the aggregated, so it is not included in "measures".
When the Pivot is first loaded and presented everything is fine ...
... but if, for instance, I drag out one of the row fields ("Student" field or any further change), then computation columns are not evaluated:
I can see some PHP Notices that I think they come from the fact of not having the aggregated into "measures".
If I include the average column in "measures" everything goes fine even moving row fields ...
The original need is just about presentation: we need to show as 0 negative values of the aggregated column, but we need to keep it as is for the aggregation evaluation.
This is our datastore definition:
$this->src('report1')
->query("SELECT
s.id id,
s.digging_test_id,
s.digging_test_eqf_level,
s.digging_test_model,
CONCAT(u.lastname,', ',u.firstname,' (Student ID:',d.id,')') as student_name,
s.org_idnumber,
s.grade,
s.gender,
qa.q_area,
qa.q_topic,
qa.q_area_id,
qa.q_topic_id,
qa.q_grade as item_grade
FROM `".$CFG->prefix."digging_stats_raw` s
JOIN `".$CFG->prefix."digging_stats_qa` qa ON s.id = qa.test_response_id
JOIN `".$CFG->prefix."digging_students` d ON s.student_id = d.id
JOIN `".$CFG->prefix."digging_users` t ON d.user_id = t.id
JOIN `".$CFG->prefix."user` u ON t.moodle_userid = u.id
WHERE s.group_id = :group_id /*and s.digging_test_eqf_level = 1*/
ORDER BY s.student_id, s.digging_test_model, qa.q_area_id, qa.q_topic_id")
->params(array(
":group_id"=>$this->params["group"]
))
->pipe(new ValueMap(
array(
"q_area_id"=>get_strings_area(),
"q_topic_id"=>get_strings_topic()
)))
->pipe(new \koolreport\pivot\processes\Pivot([
"dimensions"=>array(
"row" => "student_name, q_area_id, q_topic_id",
),
"aggregates"=>array(
"avg"=>"item_grade",
),
"computations" => array(
"item_grade_pos" => function($aggRow) {
return $aggRow["item_grade - avg"] <= 0.0 ? 0.0 : $aggRow["item_grade - avg"];
},
"DigCompEduLevel" => function($aggRow) {
return todigcompedulevel($aggRow["item_grade - avg"]);
}
),
])
)
->pipe(new ColumnMeta(array(
"item_grade - avg"=>array(
'type' => 'number',
"decimals" => 2,
),
"item_grade_pos"=>array(
'type' => 'number',
"decimals" => 2,
),
"DigCompEduLevel" => array(
'type' => 'string',
)
)))
->pipe($this->dataStore('report1_grades_pivottable'));
And this the PivotMatrix:
PivotMatrix::create(array(
"id" => "pivotMatrix1",
'dataSource' => $this->dataStore('report1_grades_pivottable'),
'rowCollapseLevels' => array(1),
'headerMap' => array(
'student_name' => $strs->report1_pivotstudent,
'q_area_id' => $strs->report1_pivotarea,
'gender' => $strs->report1_pivotsex,
'q_topic_id' => $strs->report1_pivottopic,
'item_grade - avg' => $strs->report1_pivotgrade,
'item_grade_pos' => $strs->report1_pivotgrade,
'DigCompEduLevel' => $strs->report1_pivotdcelevel
),
'totalName' => $strs->report1_pivotgrade . " ($strs->report1_score/$grademax, $strs->report1_pivotlevel)",
'measures'=>array(
// "item_grade - avg", <= if we put this it works
"item_grade_pos",
"DigCompEduLevel"
),
'waitingFields' => array(
"gender" => "label",
),
'paging' => array(
'size' => 100,
'maxDisplayedPages' => 5,
'sizeSelect' => array(5, 10, 20, 50, 100, 200, 500)
),
'hideSubtotalRow' => false,
));