I'm checking this example: https://www.koolreport.com/docs/google_charts/column_chart/

But I can't wrap my head around how I can convert MySQL Query to this format:

"dataSource"=>array(
    array("category"=>"Books","sale"=>32000,"color"=>"#4F5060"),
    array("category"=>"Accessories","sale"=>43000,"color"=>"#67819D"),
    array("category"=>"Phones","sale"=>54000,"color"=>"#ADBD37"),
    array("category"=>"Movies","sale"=>23000,"color"=>"#588133",),
    array("category"=>"Others","sale"=>12000,"color"=>"#003B45"),
),

I tried to fiddle around with Transpose, but that didn't get me so far.

Also tried to use Cube:

  ->pipe(new Cube(array(
                            "row" => "year",
                            "column" => "vat",
                            "sum" => "sale_amount"
                        )))

But it doesn't group into years for some reason when using the ColumnChart.

What I'm basically trying to do is to show yearly stats of sales which VAT as grouping.

 SELECT rr.vat,YEAR(r.tstamp) as year,sum(amount*price) as sale_amount 
                            FROM receipts r, receipt_row rr
                            where rr.receipt_id = r.id
                            GROUP BY year,rr.vat

This gives me

vat	year	sale_amount
10.0000	2015	207217.75000000
14.0000	2015	6187.36000000
24.0000	2015	57565.93000000
0.0000	2015	15511.03000000
10.0000	2016	275658.50000000
24.0000	2016	118417.46500000
14.0000	2016	12580.30000000
0.0000	2016	55515.60000000

And I don't have an idea how to get it to the right format for the ColumnChart. This is what I'm aiming for with the cube.

Source data:

End result along these lines (but isStacked = true)

Ok, I looked at the data with fresh eyes. The problem seems to be that year 2015 is missing vat 10%.

Is there a way to check/fill missing values? I know the valid vat percentages before running the query.

Now I got it to work, apart from the ColumnChart. It works if the {{all}} column is there and I use the same dataStore as for the Tabular data.

I optimized the SQL query so it be a little faster. So it groups the data instead of fetching all the rows for KoolReport to chew on.

So. With the source data:

With this code:

  $this->src("tools")->query("
                            SELECT rr.vat as vat,YEAR(r.tstamp) as year,sum(amount*price) as sale_amount 
                            FROM receipts r, receipt_row rr
                            where rr.receipt_id = r.id
                            and year(r.tstamp) in ( 2015, 2016, 2017)
                            group by year,vat
                            order by year,vat
                        ")
                        ->pipe(new ColumnMeta(array(
                            "year" => [
                                'type' => 'string',
                            ],
                            "vat" => [
                                'type' => 'string',
                            ],
                            "sale_amount"=>array(
                                'type' => 'number',
                                "suffix"=>"€",
                            ),
                        )))
                        ->pipe(new ValueMap(array(
                            'vat' => array(
                                '{func}' => function ($value) {
                                    return 'VAT ' . $value. "%";
                                },
                                "{meta}" => array(
                                    "type" => "string"
                                ),
                            )
                        )))
                        ->pipe(new Cube([
                            "row" => "year",
                            "column" =>"vat",
                            "sum" => "sale_amount",
                        ]))
                        ->saveTo($nodeForColumnChart)
                        ->pipe(new ColumnMeta(array(
                            "{{all}}" => array(
                                "label" => _("Total")
                            )    
                        )))
                        ->saveTo($nodeReady);

                        $nodeReady
                        ->pipe($this->dataStore('salescube'));

                        Table::create(array(
                            "dataStore" => $this->dataStore('salescube'),
                            'cssClass' => array('table' => 'table-condensed')
                        ));

                        $nodeForColumnChart
                        ->pipe(new RemoveColumn(array(
                            "{{all}}"
                        )))
                        ->pipe($this->dataStore("salescubeNoAll"));

                     ColumnChart::create(array(
                    "dataStore"=>  $this->dataStore("salescube"),
                    "options" => [
                        "isStacked" => true
                    ],
                     "clientEvents"=>array(
                        "itemSelect"=>"function(params){
                            saleDrillDown.next({year:params.selectedRow[0]});
                        }",
                    )
                ));

I get end result:

Now the only problem is that when I try do change the dataSource to 'salescubeNoAll' which is created like this:

 $nodeForColumnChart
                        ->pipe(new RemoveColumn(array(
                            "{{all}}"
                        )))
                        ->pipe($this->dataStore("salescubeNoAll"));

The ColumnChart doesn't show a chart but gives this error message: no data available in chart

And the answer seems to be little strange:

I have to prepare my dataStores BEFORE showing any of the tables/charts.

If I do this, it fails:

Prepare salescube
Show table
Prepare salescubeNoAll
Show ColumnChart

But this works:

Prepare salescube
Prepare salescubeNoAll
Show table
Show ColumnChart

Do you have any insight why that happens?

Thanks for the help =) Highly appreciated!