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

👨‍💻 Development Help
8

This gives very nice results, so I made a few improvements! I will eventually create a technical blog post about this, but I want to use fancier graphics for that, to show off the potential B-)

So my reviewed tutorial is here:

  1. Create this file (creating any necessary subdirectories first)

custom/Extension/application/Ext/EntryPointRegistry/googleCharts.php


<?php
  $entry_point_registry['googleCharts'] = array(
      'file' => 'custom/googlecharts.php',
      'auth' => true,
  );

(the missing ?> at the end is deliberate, don’t add it).

  1. Now run a Quick Repair and Rebuild to get the entry point registered.

  2. Create this file

custom/googlecharts.php


<html>
  <head>
       <?php
  global $current_user;
  global $db;

  $query = "SELECT sales_stage as stage, SUM(amount) as total FROM opportunities where assigned_user_id='".$current_user->id.
           "' GROUP BY sales_stage";
  $result=$db->query($query,true);
  $rows=array();
  while( $row = $db->fetchByAssoc($result)) {
     $rows[$row['stage']]= $row['total'];
//   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']});

      // 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', 'Topping');
        data.addColumn('number', 'Slices');
           data.addRows([
               <?php foreach ($rows as $key => $value) {
                  echo "['", (string) $key, "', ", (string) $value, "],";
                }
                ?>
            ]);

        // Set chart options
        var options = {'title':'Opportunities Closed Won Vs. Closed Lost',
                       'width':400,
                       'height':260, colors: ['#e0440e', '#1D7603', '#334CFF']};

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

        var barchart_options = {title:'Barchart: Opportunities',
                       width:400,
                       height:260,
                       legend: 'none'};
        var barchart = new google.visualization.BarChart(document.getElementById('barchart_div'));

        barchart.draw(data, barchart_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>

  1. Access your graphs directly for tests at this URL: http://your.server.org/index.php?entryPoint=googleCharts

  2. Create new Web dashlet with the same URL, to get it into your Dashboard.

I made a few improvements:

a. Made the script resilient to empty result sets

b. Changed it to English

c. Made it dynamically grab the various Sales stages! This greatly increases the utility of the script, and facilitates applying it to different queries.

d. Added a div to allow scaling the final result (although at a factor of 1.0, it currently is not scaling anything)

e. Moved the Entry Point registration and the custom file to a more proper location.

Now when I have some time I’d like to try some nice-looking multi-level Sankey graphs!

1 Like