PivotTable and PivotMatrix: Can an aggregate 'computation' column be a string instead of a number ?

Mariano Sanz commented on Jan 16, 2023

Hi!

Hope you can help me with this.

I'm using a PivotMatrix with an aggregate measure (it is an average 'grade' for some tests). Besides, I need to show an additional column with its translation to a 'Text' level like "A1, A2, B1, B2, C1, C2". I need to apply this to every row in the dynamic table as far the user collapses and open the different row dimensions.

I'm trying to use a 'computation' measure with a function 'translating' the avg grade to text.

The new calculation shows a '0' and I'm not being able to specify it as string or whatever.

Some extracts of the code for my tests:

ListStats.php 705 ->pipe(new \koolreport\pivot\processes\Pivot([

706 "dimensions"=>array(

707 /"column" => "item_grade",/

708 // "row" => "student_name, q_area, q_topic",

709 "row" => "student_name, q_area_id, q_topic_id",

710 ),

711 "aggregates"=>array(

712 "avg"=>"item_grade",

713 ),

714 "computations" => array(

715 "CompDigEduLevel" => function($aggRow) {

716 // return $aggRow["item_grade - avg"] . "level";

717 return "B2";

718 }),

ListStats.view.php 1605 PivotMatrix::create(array(

...

1621 'totalName' => "Average " . $strs->report1_pivotgrade,

1622 1623 'measures'=>array(

1624 "item_grade - avg",

1625 "CompDigEduLevel"

1626 ),

1627

1628 'waitingFields' => array(

1629 "gender" => "label",

1630 "digging_test_eqf_level" => "label",

1631 ),

I think this could not be possible from php....

Is there any way to do what we need ?

Other idea could be if I can apply this as a customized 'formatting' functionality for the aggregate column. Should it be applied in JS? Can you see a way to do it ?

Many thanks in advance.

Hope I've been able to explain this need.

Thanks!!

Sebastian Morales commented on Jan 17, 2023

Yes, you can return a string value in Pivot's aggregate computation. The reason those strings are shown as 0 in PivotTable/PivotMatrix widget is because without an explicit meta type they are assumed numeric values (thus, applying number_format() on a string returns 0). We can set the computation field's meta type like this after Pivot process:

        ->pipe(new Pivot(array(
            ...
            "computations" => array(
                "CompDigEduLevel" => function($aggRow) {
                    ...
                    return "A"; //B, C,..., F
                }
            ),
        )))
        ->pipe(new \koolreport\processes\ColumnMeta([ //add this ColumnMeta process
            "CompDigEduLevel" => ["type" => "string"]
        ]))
        ->pipe($this->dataStore('result'));

Pls try this approach and let us know the result. Tks,

Mariano Sanz commented on Jan 18, 2023

Hi Sebastian!

Thanks a lot!!

I did try the ColumnMeta but I did it BEFORE the Pivot() process.

Now it is working.

Thanks for your clear and focused answer.

Regards!!

KoolReport's Forum

PivotTable and PivotMatrix: Can an aggregate 'computation' column be a string instead of a number ? #2947

Build Your Excellent Data Report