1

Hi I have a need to display a google bubble chart that aggregates data from a child records. It works perfect with data hard coded, but I cannot figure out how to display data pulled from an aggregate query in a controller method. Below is the example code that displays properly

VF CODE

<apex:page>
    <html>
    <head>
        <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
        <script type="text/javascript">
        google.charts.load('current', {'packages':['corechart']});
        google.charts.setOnLoadCallback(drawSeriesChart);

        function drawSeriesChart() {

        var data = google.visualization.arrayToDataTable([
            ['ID', 'Life Expectancy', 'Fertility Rate', 'Region',     'Population'],
            ['CAN',    80.66,              1.67,      'North America',  33739900],
            ['DEU',    79.84,              1.36,      'Europe',         81902307],
            ['DNK',    78.6,               1.84,      'Europe',         5523095],
            ['EGY',    72.73,              2.78,      'Middle East',    79716203],
            ['GBR',    80.05,              2,         'Europe',         61801570],
            ['IRN',    72.49,              1.7,       'Middle East',    73137148],
            ['IRQ',    68.09,              4.77,      'Middle East',    31090763],
            ['ISR',    81.55,              2.96,      'Middle East',    7485600],
            ['RUS',    68.6,               1.54,      'Europe',         141850000],
            ['USA',    78.09,              2.05,      'North America',  307007000]
        ]);

        var options = {
            title: 'Fertility rate vs life expectancy in selected countries (2010).' +
            ' X=Life Expectancy, Y=Fertility, Bubble size=Population, Bubble color=Region',
            hAxis: {title: 'Life Expectancy'},
            vAxis: {title: 'Fertility Rate'},
            bubble: {textStyle: {fontSize: 11}}
        };

        var chart = new google.visualization.BubbleChart(document.getElementById('series_chart_div'));
        chart.draw(data, options);
        }
        </script>
    </head>
    <body>
        <div id="series_chart_div" style="width: 900px; height: 500px;"></div>
    </body>
    </html>
</apex:page>

this displays perfectly

hard coded

The goal is to display a bubble chart that shows related records with the same Hour__c and Day_Of_Week__c values. The aggregate query I would like to display data from is

SELECT Hour__c, Day_Of_Week__c, COUNT(Id) Total FROM Custom_Object__c where Related_Case_Record__c =: recID group by Hour__c, Day_Of_Week__c

with 'recId' being the id of the current page. The query functions correctly, I just can't figure out how to load the datatable with the information.

I have tried to replicate this solution

https://www.biswajeetsamal.com/blog/google-charts-in-visualforce-page/

but had no luck. I think it is similar to what I need but I could not set it up correctly for a bubble chart.

1
  • Hi, and welcome to SFSE! Please take a moment to read about How to Ask and take our quick tour. You have a pretty great first question, so here's a +1 from me. I edited your question to take out the "any help appreciated, thank you" bits--we assume as a community that you are here for our help and that we have your gratitude, so this does not need to be stated in the question. I also cleaned up your code formatting--you can use the {} button to format code, or use the triple-backtick to outline code (edit the question to see how I did this).
    – sfdcfox
    Mar 17, 2023 at 19:22

1 Answer 1

0

I figured out the code and will share below. It was a little tricky because the bubble chart requires the x and y axis' to be integers, so I had to capture the day of the week as an integer then hide the x axis and create a div below the graph that displays the days of the week properly spaced for the chart. Because of this, and because I reuse aggregate values to control bubble color and size, I hid the hover-over function. This chart does not support customizations to the hover-over event. Also please note that the first column has to be a string and that these datetimes will be converted to that of the current user.

VISUALFORCE PAGE

<apex:page controller="Google_Chart_Controller" sidebar="false">
  <html>
    <head>
      <style type="text/css">
        td {
          font-size: 12px;
          width: 150px;
        }
      </style>
      <div style="font-size: 16px; font-weight: bold;"> This is a Custom Chart Header </div>
      <br />
      <!-- Google API inclusion -->
      <apex:includeScript id="a" value="https://www.google.com/jsapi" />
      <!-- Google Charts will be drawn in this DIV -->
      <div id="chartBlock" />
      <script type="text/javascript">
        // Load the Visualization API and the piechart package.
        google.charts.load('current', {
          'packages': ['corechart']
        });
        // Set a callback to run when the Google Visualization API is loaded.
        google.setOnLoadCallback(initCharts);

        function initCharts() {
          var recordid = '{!$currentPage.parameters.id}';
          Google_Chart_Controller.loadOpps(recordid, function(result, event) {
            // load bubble chart
            var visualization = new google.visualization.BubbleChart(document.getElementById('chartBlock'));
            // Prepare table model for bubble chart data columns
            var data = new google.visualization.DataTable();
            data.addColumn('string', 'Total');
            data.addColumn('number', 'Day of Week');
            data.addColumn('number', 'Hour of Day (User Time Zone)');
            data.addColumn('number', 'Total Suspects');
            data.addColumn('number', 'Bubble Size');
            data.addRow(['EMPTY', 1, 1, 1, 0]);
            // add rows from the remoting results
            for (var i = 0; i < result.length; i++) {
              var r = result[i];
              data.addRow([r.TOTAL.toString(), r.DAY, r.HOUR, r.TOTAL, r.TOTAL]);
            }
            var options = {
              //hAxis: { textPosition: 'none' },
              hAxis: {
                viewWindow: {
                  max: 7.5,
                  min: 0.5
                },
                textPosition: 'none'
              },
              vAxis: {
                title: 'Hour of Day',
                viewWindow: {
                  max: 23,
                  min: 8
                },
                ticks: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24]
              },
              colorAxis: {
                colors: ['yellow', 'red']
              },
              tooltip: {
                trigger: 'none'
              },
              sizeAxis: {
                maxSize: 25,
                minSize: 15
              },
              bubble: {
                textStyle: {
                  fontSize: 18,
                  bold: true,
                  auraColor: 'none'
                }
              },
              chartArea: {
                left: 40,
                top: 35,
                bottom: 10,
                width: "75%",
                height: "100%"
              },
              height: 500,
              width: 1000
            };
            var chart = new google.visualization.BubbleChart(document.getElementById('series_chart_div'));
            visualization.draw(data, options);
          }, {
            escape: true
          });
        }
      </script>
    </head>
    <body>
      <div id='series_chart_div'></div>
      <div style="margin-left: 87.5px">
        <table width="752px">
          <tr>
            <td> S </td>
            <td> M </td>
            <td> T </td>
            <td> W </td>
            <td> T </td>
            <td> F </td>
            <td> S </td>
          </tr>
        </table>
      </div>
      <br />
      <div style="margin-left: 366px; font-size: 14px; font-style: italic;"> Day of the Week </div>
      <br />
      <br />
    </body>
  </html>
</apex:page>

APEX CONTROLLER

    global with sharing class Google_Chart_Controller{

    public Id RecordId { get; set; }
    
    @RemoteAction    
    global static AggregateResult[] loadOpps(Id RecordId) {
   // String recID = ApexPages.currentPage().getParameters().get('id');
        return [select COUNT(Id) TOTAL, HOUR_IN_DAY(convertTimezone(Date_Time__c)) HOUR, DAY_IN_WEEK(convertTimezone(Date_Time__c)) DAY from Contact 
        where Related_Record__c = :RecordId GROUP BY HOUR_IN_DAY(convertTimezone(Date_Time__c)), DAY_IN_WEEK(convertTimezone(Date_Time__c))];
    } 
}

In the test method after creating your data do:

Test.startTest();
        List<AggregateResult>result = Google_Charts_Controller.loadopps(parent record.Id);
Test.stopTest();

The final result will look like this enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .