Newbie Needs help with How to make a line chart from Mssql Data

23 views
Skip to first unread message

Tom Davis

unread,
Nov 10, 2015, 10:28:12 AM11/10/15
to Google Visualization API
I want to create a line chart like this one(see Below) but I want to use the data from the sql page also below.


/////////----------------Google Line Chart
<html>
<head>
  <script type="text/javascript" src="https://www.google.com/jsapi"></script>
  <script type="text/javascript">
    google.load('visualization', '1.1', {packages: ['line']});
    google.setOnLoadCallback(drawChart);

    function drawChart() {

      var data = new google.visualization.DataTable();
      data.addColumn('number', 'Month');
      data.addColumn('number', '2013');
      data.addColumn('number', '2014');
      data.addColumn('number', '2015');

      data.addRows([
        [1,  37.8, 80.8, 41.8],
        [2,  30.9, 69.5, 32.4],
        [3,  25.4,   57, 25.7],
        [4,  11.7, 18.8, 10.5],
        [5,  11.9, 17.6, 10.4],
        [6,   8.8, 13.6,  7.7],
        [7,   7.6, 12.3,  9.6],
        [8,  12.3, 29.2, 10.6],
        [9,  16.9, 42.9, 14.8],
        [10, 12.8, 30.9, 11.6],
        [11,  5.3,  7.9,  4.7],
        [12,  6.6,  8.4,  5.2]
       
      ]);

      var options = {
        chart: {
          title: 'Sales for:',
          subtitle: ''
        },
        width: 900,
        height: 500,
        axes: {
          x: {
            0: {side: 'top'}
          }
        }
      };

      var chart = new google.charts.Line(document.getElementById('line_top_x'));

      chart.draw(data, options);
    }
  </script>
</head>
<body>
  <div id="line_top_x"></div>
</body>
</html>


/////////////----------------Page with sql data-------------------------------------//////




<!DOCTYPE html>
<html lang="en">
<head>
  <title>DashBoard</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="http://localhost/css/bootstrap.min.css">
  <script src="https://localhost/js/jquery.min.js"></script>
  <script src="http://localhost/js/bootstrap.min.js"></script>
</head>
<body>
<style>
table {
border: 1px solid #B0CBEF;
border-width: 1px 0px 0px 1px;
font-size: 14pt;
font-family: Calibri;
font-weight: 100;
border-spacing: 0px;
border-collapse: collapse;
}

 TH {
background-image: url(excel-2007-header-bg.gif);
background-repeat: repeat-x; 
font-weight: normal;
font-size: 17px;
border: 1px solid #9EB6CE;
border-width: 0px 1px 1px 0px;
height: 17px;
}

 TD {
border: 0px;
padding: 0px 4px 0px 2px;
border: 1px solid #D0D7E5;
border-width: 0px 1px 1px 0px;
}

 TD B {
border: 0px;
background-color: white;
font-weight: bold;
}

 TD.heading {
background-color: #E4ECF7;
text-align: center;
border: 1px solid #9EB6CE;
border-width: 0px 1px 1px 0px;
}



</style>

<?php
$grandTotal = 0;
$connect =odbc_connect("removed");
if(!$connect) {
exit("Connection Failed: " . $connect);
}

$sql=" SELECT      
 CONVERT(CHAR(4), ompOrderDate, 120)  as year
, CONVERT(CHAR(2), ompOrderDate, 101)  as month
, sum( ompOrderSubtotalBase)as total
FROM m1_KF.dbo.SalesOrders Left Join
m1_KF.dbo.Organizations on SalesOrders.ompCustomerOrganizationID = Organizations.cmoOrganizationID left Join
m1_KF.dbo.Employees on lmeEmployeeID = cmoAccountManagerEmployeeID Left Join
m1_KF.dbo.OrganizationLocations on Organizations.cmoOrganizationID = OrganizationLocations.cmlOrganizationID and
SalesOrders.ompShipLocationID = OrganizationLocations.cmlLocationID 

                   
 Where ompOrderDate > '01-01-2013' and  lmeEmployeeID = 'MF001'   and ompClosed =-1  
 group by    lmeEmployeeID
 ,CONVERT(CHAR(2), ompOrderDate, 101), CONVERT(CHAR(4), ompOrderDate, 120)
          
  order by year, month ";

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
#echo "SalesPerson Total". date("m-d-Y") ;
echo "<table><tr>";
echo "<th>Year</th>";
 echo "<th>  </th>";
echo "<th>Month</th>";
 echo "<th>  </th>";
echo "<th>Total</th></tr>";

while (odbc_fetch_row($result)) {
 
  $year=odbc_result($result,"year");
  $month=odbc_result($result,"month");
  $total=odbc_result($result,"total");


$num = number_format($total, 2, '.', ',');
 $grandTotal += $total;
 
  echo "<tr><td>$year</td>";
  echo "<td> &#x25c3; </td>";
  echo "<td>$month</td>";
   echo "<td> &#x25b9; </td>";
  echo "<td>$num</td>";
  # echo "<td align='right'>$num</td></tr>";
   
}
#$num2 = number_format( $grandTotal, 2);
#echo "Grand Total: $num2";

odbc_close($connect);
?>

</body>
</html>
Reply all
Reply to author
Forward
0 new messages