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

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?

I don’t know, I never tried it, but…

Are you giving different names to the Javascript variables, and to the divs?

What errors do you see in your browser’s Javascript console?


I see this error. and not sure how to rename the variables or the divs.
Any idea what is this error is?

You’re messing up your Javascript. ArrayToDataTable is a static method, a helper function, not an object that you can instantiate with new

https://developers.google.com/chart/interactive/docs/reference#arraytodatatable

It would be easier to help you if you shared your entire code.

Hi @pgr so sorry for the late response. Here is what I have done.

So I have followed your instructions above and created 2 different entry point:
File 1: custom/Extension/application/Ext/EntryPointRegistry/googleCharts1.php
File 2: custom/Extension/application/Ext/EntryPointRegistry/googleCharts2.php
(I actually need to create multiple charts using Google chart but the data will have to be different)

and then I created the following chart files in (/custom/charts):

Sorry couldn’t copy the code here as it wont let me.

Thanks a lot for your help with this.

Focus on the name chart_div. You can’t repeat that for both charts, if they’re showing in the same page.

The id of the div you specify in the HTML part:
<td><div id="chart_div" style="border: 1px solid #ccc;"></div></td>

… must match what you tell the Google visualization object to use:

var chart = new google.visualization.ColumnChart(document.getElementById("chart_div"));

Then, for the second chart, change both of those: a new div id in the HTML, and that same id is passed to the visualization object.

You can paste code here by enclosing it in triple-backtick tags, like this:

```
code
```

Using custom Entrypoint and Adding Web URL Dashlets, it produces amazing results.

3 Likes

That’s really :cool:, yes!

I love your typo: instead of Sankey graph, you called it a Snakey graph… which actually describes it a lot better :stuck_out_tongue:

@pgr THANK YOU VERYYYYYYYYYYYYYYYYYYYY MUCH. It worked :slight_smile:

I wanted to ask a little extra help with database queries to get the following charts:

  1. Active Leads by Status (excluding dead/close/converted leads) - column chart
  2. Active Product by Status (I created a custom dropdown called Status for the product module, so want to show all products excluding dead/close products) - bar chart
  3. Ready products by month/year (custom dropdown “status = Ready, Final, Delivering” and group them by two custom fields Month and years. I created one dropdown with all months and one dropdown with year from 2019 to 2025. Is it possible to group the results to Jan 2020, Feb 2020)? - Column Chart
  4. Best Converted Source - Pie Chart
  5. Active Leads by Quality - Leads status=non equal to dead, converted. Group the results by a custom dropdown called Quality (includes Good, Bad, Very Good) - Pie Chart
  6. Open Tasks by Assigned Users = Pie Chart
  7. Open Meetings by Assigned Users = Pie Chart

I am not very familiar with DB queries so would really appreciate your help.

Also wanted to ask your opinion on:
I can get the reports with build in reports and chart and apply necessary filters but can’t figure out the DB queries to build google charts. Is there an easy way find what DB queries is being used after I create an report?