Pivot: non dimensional columns

Eugene commented on Mar 6, 2019

Hi, Is it possible to add some columns to a pivot table?

For example, if I have data like this

It is easy to get this using Pivot

But is it possible to get some additional data for each expanded row

David Winterburn commented on Mar 6, 2019

Hi Eugene,

Please try to add refNo and id to row dimension to see if it works for your requirement. Another option is to group date and refNo and id into one field:

select ..., concat(date, '-', refNo, '-', id) as dateRefID from ...

Then use dateRefID in the pivot's row dimension. Let us know your opinion. Thanks!

Eugene commented on Mar 6, 2019

Thanks, David, as a dimension row, the table looks too bulky, so I will make a group field.

Eugene commented on Mar 6, 2019

One more question, I've already asked it about the Table widget but is it possible for PivotTable - to make the value clickable as a link to open another report. The solution for Table widget is formatValue property but is there something similar for PivotTable?

Eugene commented on Mar 10, 2019

up :-/

David Winterburn commented on Mar 11, 2019

Hi Eugene,

In both PivotTable and PivotMatrix widgets you have a "map" property that is similar to Table's formatValue function:

PivotTable::create(array(
    ...
    'map' => array(
        'rowHeader' => function($rowHeader, $headerInfo) {
            return $rowHeader;
        },
        'columnHeader' => function($colHeader, $headerInfo) {
            return $colHeader;
        },
        'dataCell' => function($value, $cellInfo) {
            return $value;
        }
    ),
    ...
))

Just modify $rowHeader, $colHeader or $value to your link and return it. Thanks!

Eugene commented on Mar 15, 2019

Thank you, David, but It looks like I need some examples. The docs are not enough for me and I cannot understand how to add the link to the value in only one column that I need

My Pivot process:

$this->src('quinos')
            ->query($query)
            ->params($query_params)

            ->saveTo($node);
        $node->pipe($this->dataStore('p'));
            $node->pipe(new Pivot(array(
                "dimensions" => array(
                    "row" => "Partner, dateRefID"
                ),
                "aggregates"=>array(
                    "sum" => "total",
                    "count" => "total"
                )
                )))

            ->pipe($this->dataStore('purchasing'));

I would like to add the link to the dateRefID value and the link must include the id but it is not the part of the row now...

David Winterburn commented on Mar 18, 2019

Hi Eugene,

In your case, if you want to add link to dateRefID field please try this:

PivotTable::create(array(
    ...
    'map' => array(
        'rowHeader' => function($rowHeader, $headerInfo) {
            if ($headerInfo['fieldName'] === 'dateRefID')
                return "<a href='path/to/$rowHeader'>$rowHeader</a>";
            else
                return $rowHeader;
        },
    ),
    ...
))

Let us know if this works for you. Thanks!

Eugene commented on Mar 18, 2019

Thank you, David

But how to add the id to the link. If it were the usual table I could use the row array but I don't have the id field after the pivot process already but I still need it to use in the link... as a solution, I can try to get it from the sting dateRefID but is it the optimal way?

KoolReport commented on Mar 18, 2019

Your dateRefID now is in the format "date-refNo-id" so actually you can extract the id you may use the substr() and strrpos() to get the id.

Eugene commented on Mar 19, 2019

Thank you.

You should add some details to the Pivot's documentation :-)

KoolReport's Forum

Pivot: non dimensional columns #727

Build Your Excellent Data Report