How to use pivot table with the pure PHP integration?

Giselle Machado commented on Jun 20, 2019

I saw the below example for "Column Chart" and "Table", but I couldn't understand the "DataSource" format of pivot table. How should I use "use \koolreport\pivot\processes\Pivot;"?

<?php
    use \koolreport\widgets\koolphp\Table;
    use \koolreport\widgets\google\ColumnChart;

    $data = array(
        array("category"=>"Books","sale"=>32000,"cost"=>20000,"profit"=>12000),
        array("category"=>"Accessories","sale"=>43000,"cost"=>36000,"profit"=>7000),
        array("category"=>"Phones","sale"=>54000,"cost"=>39000,"profit"=>15000),
        array("category"=>"Movies","sale"=>23000,"cost"=>18000,"profit"=>5000),
        array("category"=>"Others","sale"=>12000,"cost"=>6000,"profit"=>6000)
    );
?>
<html>
    <head>
        <title>KoolReport's Widgets</title>
    </head>
    <body>
    <body>
        <?php 
        Table::create(array(
            "dataSource"=>$data
        ));
        ?>
        <?php 
        ColumnChart::create(array(
            "dataSource"=>$data
        ));
        ?>
    </body>
</html>

Giselle Machado commented on Jun 20, 2019

I tried the following data format but it isn't working.

Array
(
    [aggregates] => Array
        (
            [leads] => Array
                (
                    [0] => count
                )

        )

    [dimensions] => Array
        (
            [row] => Array
                (
                    [0] => status, leads
                )

        )

    [data] => Array
        (
            [0] => Array
                (
                    [leads] => Lead 01
                    [status] => Attempted
                    [staff] => Giselle Barbosa
                    [customfieldvalueC] => 
                )

            [1] => Array
                (
                    [leads] => Leads 03
                    [status] => Customer
                    [staff] => Giselle Barbosa
                    [customfieldvalueC] => 
                )

            [2] => Array
                (
                    [leads] => Lead 02
                    [status] => Attempted
                    [staff] => Giselle Barbosa
                    [customfieldvalueC] => 
                )
        )
)

KoolReport commented on Jun 20, 2019

The format of Pivot is different from normal table format so you need to pipe the normal data into Pivot process and then save them into datastore before display with Pivot widget.

Giselle Machado commented on Jun 20, 2019

I've tried the following:

$pivot_data_db_2 = array('dataStore'=>$pivot_data_db);
$pivot_data_db_2[] = new Pivot(array('dimensions'=>array('row'=>"status, staff, Leads"),'aggregates'=>array('count'=>'Leads')));

But still not working. I don't want to create a new class to pipe it. Can you show me how to do "you need to pipe the normal data into Pivot process", please?

Giselle Machado commented on Jun 20, 2019

I also tried the following:

PivotTable::create(array(
      "dataSource"=>(new PdoDataSource($connection))->query("
                SELECT tblleads.name as leads, tblleads_status.name as status, tblstaff.firstname as staff 
                FROM tblleads
                left join tblleads_status on tblleads.status = tblleads_status.id
                left join tblstaff on tblstaff.staffid = tblleads.assigned
                GROUP BY tblleads.id
                ")->pipe(new Pivot(array(
    'dimensions'=>array(
      'row'=>"status, staff, leads"
    ),
    'aggregates'=>array(
      'count'=>'leads'))));
    ),
    'rowDimension'=>'row',
  );

Giselle Machado commented on Jun 21, 2019

I solved it! Thanks.

KoolReport's Forum

How to use pivot table with the pure PHP integration? #938

Build Your Excellent Data Report