How to add other charts in Report Charts, For ex. Line Chart, Bubble Chart, Gauge chart, Funnel Chart etc.

👨‍💻 Development Help
16

Here is an improved version of my custom/googlecharts.php file. It is a bit more generic, storing the data in a PHP associative array, so you can use column names when adding the data rows.

This one shows that nice looking “multilevel-Sankey” Opportunities Flow Graph I posted earlier.


<html>
  <head>
       <?php
  global $current_user;
  global $db;
  $rows=array();

  $query = "SELECT lead_source, sales_stage as stage, amount as total, user_name as user
            FROM opportunities
            LEFT JOIN users ON users.id = opportunities.assigned_user_id
            WHERE opportunities.deleted=0
            ";
  $result=$db->query($query,true);
  while( $row = $db->fetchByAssoc($result)) {
     $rows[]= $row;
   //  $rows[]= array($row['lead_source'], $row['stage'], $row['total'], $row['user']);
   //echo $row['stage'], ": ", $row['total'], '<br/>';
  }
//  if (count($rows)==0) echo 'Query returned no rows.';
//  var_dump($rows);
//  foreach($rows as $key => $value) {
//     var_dump($key);
//     var_dump($value);
//   }
?>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

      // Load the Visualization API and the corechart package.
//      google.charts.load('current', {'packages':['corechart']});
      google.charts.load('current', {'packages':['sankey']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.charts.setOnLoadCallback(drawChart);
      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart() {

        // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'From');
        data.addColumn('string', 'To');
        data.addColumn('number', 'Amount');
        data.addRows([
            <?php foreach ($rows as $r) {
               echo "['", $r['lead_source'], "', '", $r['user'], "', ", $r['total'], "],", PHP_EOL;
               echo "['", $r['user'], "', '", $r['stage'], "', ", $r['total'], "],", PHP_EOL;
             }
            ?>
        ]);

        // Set chart options
        var colors = ['#a6cee3', '#b2df8a', '#fb9a99', '#fdbf6f',
                  '#cab2d6', '#ffff99', '#1f78b4', '#33a02c'];
        var options = {'title': 'Opportunities', // Source, Employee, Outcome, with Amount shown as Width',
                       'width': 400,
                       'height':400,
        sankey: {
          node: {
      //      colors: colors
          },
          link: {
            colorMode: 'gradient',
      //      colors: colors
          }
        }

        };

        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.Sankey(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
   </script>

<body>
<div style="zoom: 1.0; -moz-transform: scale(1.0); transform-origin: 0 0; -moz-transform-origin: 0 0">
    <!--Table and divs that hold the pie charts-->
    <table class="columns">
      <tr>
        <td><div id="chart_div" style="border: 1px solid #ccc;"></div></td>
      </tr>
      <td><div id="barchart_div" style="border: 1px solid #ccc;"></div></td>
      </tr>
    </table>
</div>
  </body>

</html>

There’s a bunch of debug code in there, and “color” option that aren’t used, and scaling CSS operations that aren’t used either. But I left all these things there on purpose so you can tweak them and use them easily, if needed.

2 Likes