I am trying to create a chart that shows number of items for each month (three different columns from SQL server). The SQL database has individual rows that contain among other data the following fields (date(a text field containing the date in the following format m/d/y) and equiptype(a text field containing the type of equipment). I am using a SQL select statement to count the number of each equipment type in each month. My SQL select statement is as follows:

SELECT date_format(str_to_date(date,'%m/%d/%Y)'),'%M') as Month, count(*), equiptype from Decom GROUP BY Month,equiptype

The data returned is as follows:

I would like my chart to look similar to the following:

With Books, Accessories, etc being the Months and Sale, Cost, Profit being the type of equipment.

The code in my report file is a follows:

ColumnChart::create(array(
"title"=>"Disposals by Month",
"dataSource"=>$this->dataStore('permonth'),
"columns"=>array(
"Month",
"count(*)"=>array("label"=>"Number"),
"equiptype",
)
));

This gives me an error of All series on a given axis must be of the same data type. I can take out one of either Month or equiptype and get either number of records for each month or number of records for each equipment type, but cannot get a list of months with the number of each equipment type for that month. What am I missing?

I was able to get this working without using Cube, since I don't have the license for this function. I was able to get it working by accessing the sql data passed over in the array and providing the data in the format that the chart function is looking for.

<?php
use \koolreport\widgets\google\ColumnChart;
$data=array($this->datastore('permonth'));
$report=array(array("category"=>"January"));
$report[1]["category"]="February";
$report[2]["category"]="March";
$report[3]["category"]="April";
$report[4]["category"]="May";
$report[5]["category"]="June";
$report[6]["category"]="July";
$report[7]["category"]="August";
$report[8]["category"]="September";
$report[9]["category"]="October";
$report[10]["category"]="November";
$report[10]["category"]="December";
$continue = "True";
$i = 0;
while ($continue == "True")
{
  switch ($data[0][$i]["equiptype"])
   {
     case "Desktop":
        $equip = "Desktop";
        break;
     case "Server":
        $equip = "Server";
        break;
     case "Bulk Disks";
        $equip = "Bulk Disks";
        break;
     case "Laptop";
        $equip = "Laptop";
        break;
     case "Mobile";
        $equip = "Mobile";
        break;
     case "Network";
        $equip = "Network";
        break;
     case "Storage";
        $equip = "Storage";
        break; 
     case "";
        $continue = "False";
        break;
   }
   switch ($data[0][$i]["Month"])
   {
     case "January":
       $report[0]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "February":
       $report[1]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "March":
       $report[2]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "April":
       $report[3]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "May":
       $report[4]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "June":
       $report[5]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "July":
       $report[6]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "August":
       $report[7]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "September":
       $report[8]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "October":
       $report[9]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "November":
       $report[10]["$equip"]=$data[0][$i]["count(*)"];
       break;
     case "December":
       $report[11]["$equip"]=$data[0][$i]["count(*)"];
       break; 
   }
$i++;
}
?>
<div class="report-content">
   <div class="text-center">
   <h1>Disposal by Month</h1>
</div>
<?php
ColumnChart::create(array(
"title"=>"Disposals by Month",
"dataSource"=>$report,
"columns"=>array(
   "category",
   "Server"=>array("label"=>"Servers"),
   "Desktop"=>array("label"=>"Desktops"),
   "Laptop"=>array("label"=>"Laptops"),
   "Storage"=>array("label"=>"Storage"),
   "Bulk Disks"=>array("label"=>"Bulk Disks"),
   "Network"=>array("label"=>"Network"),
   "Mobile"=>array("label"=>"Mobile"),
  )
));
?>
</div>