Make a quick vote 🗳 ️How do you refer to SuiteCRM extensions: add-ons or plug-ins? Click here to have your say 👈

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

Hello,

I use Google Charts in dashlets.

Thank you.

@jrivas that’s interesting…

Can you provide a few “steps” to set that up?

And maybe a screenshot? If you have trouble uploading images here, use https://pasteboard.co

Thanks!

Hello,

Initially I copied the example from the url: https://developers.google.com/chart/interactive/docs/quick_start in the googlecharts.php and put it in the Suitecrm root.

Therefore, consult the data to be plotted.

In the following example, two graphs are entered with the same data:

New php file in the root (googlecharts.php)


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



$query="SELECT SUM(amount) as total FROM opportunities where assigned_user_id='".$current_user->id."' and sales_stage='Closed Won'";

  $result=$db->query($query,true);
  $row=$db->fetchByAssoc($result);
  $OpportunitiesWon=$row['total'];
 
 $query = "SELECT SUM(amount) as total FROM opportunities where assigned_user_id='".$current_user->id."' and sales_stage='Closed Lost'";

 
 $result=$db->query($query,true);
  $row=$db->fetchByAssoc($result);
  $OpportunitiesLost=$row['total'];
  
   $query = "SELECT SUM(amount) as total FROM opportunities where assigned_user_id='".$current_user->id."' and sales_stage='Proposal/Price Quote'";

 
 $result=$db->query($query,true);
  $row=$db->fetchByAssoc($result);
  $OpportunitiesProposal=$row['total'];

  
?>
    <!--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([
          ['Ganadas', <?php echo $OpportunitiesWon ?>],
          ['Perdidas', <?php echo $OpportunitiesLost ?>],
          ['Ofertadas', <?php echo $OpportunitiesProposal ?>],
            
        ]);

        // Set chart options
        var options = {'title':'Oportunidades Ganadas Vs. Perdidas',
                       'width':800,
                       'height':460, 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: How Much Pizza I Ate Last Night',
                       width:800,
                       height:460,
                       legend: 'none'};
        var barchart = new google.visualization.BarChart(document.getElementById('barchart_div'));
        barchart.draw(data, barchart_options);
      }
   </script>
<body>
    <!--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>
  </body>

Then I call the file through an entrypoint. .in include/MVC/Controller/entry_point_register.php or custominclude/MVC/Controller/entry_point_register.php


 'googleCharts' => array('file' => 'googlecharts.php', 'auth' => true),

Finally I just add a dashlet type url and incerto the url with the entrypoint.

http://yourcrm.com/crmesatic/index.php?entryPoint=googleCharts

Note set
Title

Generic Dashlet
Auto-Refresh

Website Location

http://esatic.com/crmesatic/index.php?entryPoint=googleCharts

Dashlet Height (in pixels) 1000

Thank you and I hope it is a base to create many graphics of all the modules.

Javer Rivas.

1 Like

Thanks! I’m going to try this out now.

One quick correction is the way you’re doing the entry point registration isn’t the best, see here

https://docs.suitecrm.com/developer/entry-points/#_creating_an_entry_point

And a Quick Repair and Rebuild is required to get the registration operational.

1 Like

Hi, @pgr

Thanks for your recommendation. please try the procedure and leave us your comments to get more out of this.

thank you very much.

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

Hi @pgr.

Fantastic, I Will test ir again.

Thanks You very much.

Javer Rivas.

This is still a work in progress, but just look at how cool these graphs can be

Note that there is one additional variable being displayed discretely: the opportunity Amount, which is the width of the connecting lines.

Hello,

There are some errors in:

$query = “SELECT sales_stage as stage, SUM(amount) as total FROM opportunities where assigned_user_id=’”.$current_use$"’ GROUP BY sales_stage";

Thanks.

Thanks, that was an error copy-pasting my code from the terminal.

I edited both my last posts, fixing the code (hopefully) and providing a new graph with better looking sample data.

Hi,

I get the error: PHP Catchable fatal error: Object of class User could not be converted to string in /home/xxxxx/public_html/xxxx/custom/googlecharts.php on line 7

Instead of

$current_user

it should read

$current_user->id

Sorry once again. This is the problem of writing code on the forums, without testing…

1 Like

It works perfect.

Thank you very much.

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

Another nice addition I just discovered is that you can add these Graphs to Detail views and other views by using an iFrame field. You can even pass parameters into it (for example, the current record’s id).

Just apply this trick to that entrypoint that creates the Google Chart:

https://suitecrm.com/forum/suitecrm-7-0-discussion/16297-iframe-field-how-to-embed-externale-web-page-with-context

PGR,

Would this work with the Google SANKEY Chart?

Thanks,
CM

That’s exactly what it is, a Google sankey chart. You can see it in my post above, and you can see the code used to generate it.

1 Like

Hi everyone,

I am trying to add a Google chart but ran into a problem. When I add one chart, everything works fine. But when I try to create more than one chart, I can get javascript conflict error and the dashboard goes crazy. To clarify, I am not adding 2 charts from one dataset. I basically replicated the instructions above twice for 2 different charts with 2 different data query.

Looking forward to getting your advice :slight_smile:

I have a working example with both a chart and a table (also rendered by Google), also from the same data, so it should be the same as your case. After setting all the properties on the graphs this is how it ends:


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

        var table = new google.visualization.Table(document.getElementById('table_div'));

        table.draw(data, {showRowNumber: true, width: '100%', height: '100%'});

      }
   </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 charts-->
    <table class="columns" align="center">
      <tr>
        <td><div id="chart_div" style="border: 1px solid #ccc;"></div></td>
      </tr>
      <td><div id="table_div" style="border: 1px solid #ccc;"></div></td>
      </tr>
    </table>
</div>
  </body>

Thank you very much for your reply.

I apologize for the confusion. I basically followed your instructions posted on “5 Oct 2018 12:48” and created 2 different charts. So I repeated your steps 1 to 5 twice. Meaning, I created two entry points, two different google chart files, 2 different dashlets and maybe because of this, I get Javascript conflict (Dashboard keeps reloading and not working).

I am trying to have 3 google charts,

  1. All leads by Status - Column Chart
  2. All Open Tasks by assigned users - Pie Chart
  3. All Converted leads by month - line Chart.

Kindly, let m know if I can achieve what I want by following your steps and repeating them 3 times? and how do I solve the Javascript conflict?