KoolReport's Forum

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

PivotMatrix: Computation columns not evaluated on Pivot update #2991

Open Mariano Sanz opened this topic on on Feb 17, 2023 - 3 comments

Mariano Sanz commented on Feb 17, 2023

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,
));
Mariano Sanz commented on Feb 17, 2023

Can you see a way to do what we need ?

Thanks!!!

Sebastian Morales commented on Feb 21, 2023

I see the problem. It seems the following Pivot process only works on PivotMatrix's measures, which makes computations not working. We will find a solution for this issue and come back to you soon.

Mariano Sanz commented on Mar 1, 2023

Thanks!

By now I am adding the 'aggregated' and 'hide' the column using CSS :-( (So the info is anyway in the page)

So it will be really good if you are able to find another way.

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