Multiple Y-Axis From MySQL

Ryan commented on May 21, 2020

Hey guys,

This is my first time using KoolReport and I would like to use it for multiple projects within my company.

I have configured a basic line chart which displays number of sales for a particular flavour, for this month, grouped by days.

As far as I can tell, it works fairly well. However, now I would like to add another line for another flavour. The SQL statement below grabs the flavour 'Cola', so is it possible to add another SQL query that grabs 'Lemon' and adds that as another Y-axis on the chart?

SELECT date(timestamp),count(id) FROM dbTable WHERE MONTH(timestamp) = MONTH(CURRENT_DATE()) AND YEAR(timestamp) = YEAR(CURRENT_DATE()) AND location = 'UK' AND flavour = 'Cola' GROUP BY timestamp;

. SalesByCustomer.php . SalesByCustomer.view.php *. index.php

I have linked all of the PHP files above - sorry I couldn't use proper forum code blocks, as the PHP code isn't compatible with the forum encoder.

Any help would be appreciated!

Thanks, Ryan.

Ryan commented on May 22, 2020

Hey guys,

I have just made this MySQL query;

SELECT date(timestamp), flavour, ROUND ( ( LENGTH(flavour) - LENGTH( REPLACE ( flavour, "Lime", "") ) ) / LENGTH("Lime") ) AS limeCount, ROUND ( ( LENGTH(flavour) - LENGTH( REPLACE ( flavour, "Raspberry", "") ) ) / LENGTH("Raspberry") ) AS raspberryCount, ROUND ( ( LENGTH(flavour) - LENGTH( REPLACE ( flavour, "Blueberry", "") ) ) / LENGTH("Blueberry") ) AS blueberryCount, ROUND ( ( LENGTH(flavour) - LENGTH( REPLACE ( flavour, "PFruit", "") ) ) / LENGTH("PFruit") ) AS pfruitCount FROM budinfo WHERE MONTH(timestamp) = MONTH(CURRENT_DATE()) AND YEAR(timestamp) = YEAR(CURRENT_DATE()) GROUP BY timestamp

This query displays the following output;

Now would it be possible to add the extra Y axis for "raspberryCount", "blueberryCount" & "pfruitCount", instead of adding extra MySQL statements?

Thanks, Ryan

David Winterburn commented on May 25, 2020

Hi Ryan,

Regarding your flavours sales (Cola, Lemon, etc) by date I would suggest using a simple sql query together with our Cube process:

//MyReport.php
$this->src("myDatasource")
->query("Select id, date(timestamp) as date, flavour from dbTable where ...")
->pipe(new \koolreport\cube\processes\Cube(array(
    "row" => "date", 
    "column" => "flavour",
    "count" => "id"
)))
->pipe(new \koolreport\processes\Sort(array(
    "date" => "asc"
)))
->pipe($this->dataStore("sales");

//MyReport.view.php
\koolreport\widgets\google\LineChart::create(array(
    "dataSource" => $this->dataStore("sales"),
    ...
));

You could check more documentation for Cube here: https://www.koolreport.com/docs/cube/cube_process/

Let us know if you understand your question correctly. Thanks!

KoolReport's Forum

Multiple Y-Axis From MySQL #1457

Build Your Excellent Data Report