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.

Improve this question
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 at 19:22

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.