Salesforce.com and the Google Charts API Data Query

Question

I'm an API code newb and really need assistance.

I've been working in Salesforce.com and need a particular chart that doesn't exist out of the box in SF (BCG Matrix (AKA Bubble Chart)).

I've discovered the Google Chart API and have used this code (Pulled directly from the Google charts page) to create a lovely pie chart in a Salesforce page:

    <!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">

  // Load the Visualization API and the piechart package.
  google.load('visualization', '1.0', {'packages':['corechart']});

  // Set a callback to run when the Google Visualization API is loaded.
  google.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([
    ['Mushrooms', 3],
    ['Onions', 1],
    ['Olives', 1],
    ['Zucchini', 1],
    ['Pepperoni', 2]
  ]);

  // Set chart options
  var options = {'title':'How Much Pizza I Ate Last Night',
                 'width':400,
                 'height':300};

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

The problem is: the data for this chart is hard-coded in the script. I need to make a data call to a custom object that I have in Salesforce. This is where everything breaks down for me. I just don't have the know-how to do this and would like some guidance.

I know the API name of the object I want to pull the data from (Employee__c) and the two fields that I want to use in the chart: (Name (a standard field)) and (Years_Experience__c).

I also know how to change the PieChart to a BarChart.

Can someone help, please?

Thanks, Laura

Answer 1

Please read about using JavaScript on VisualForce pages and try to apply this info to your case. I prefer to use @RemoteAction for such things but you can choose any option. I guess that JS on VF is pretty simple topic. But if you will have any troubles with it please add your code as update to your question and tag me in comment about this update, I will help.

Answer 2

Please check Below code

VF page

Google_Bubble_Chart

<apex:page controller="BubbleChartController" >  
<script type="text/javascript" src="https://www.google.com/jsapi"></script>

<apex:form >


        <!-- Right Section Start-->
        <div class="rightSectHome fR">
            <!-- Open Case Status Section Start -->
            <div class="section ">
                <h2>Open Case Status</h2>
                <div id="chart_div" class = "openCaseChart" ></div>
            </div>
            <!-- Open Case Status Section End -->


        <!-- Right Section End-->
        <div class="clr"></div>
    </div>
</apex:form>

<!-- javascript -->
<script type = "text/javascript">

    // Load the Visualization API and the Bar Chart package.
    google.load("visualization", "1", {packages:["corechart"]});

    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(drawChart);
    var status, priority;

    /*Draw Bar Chats*/
    function drawChart() {
        /*Call Remoting*/
        BubbleChartController.loadOpenCases(
        function(result, event){
        console.log(result);
        var chart = new google.visualization.BubbleChart(document.getElementById('chart_div'));
        var data = new google.visualization.DataTable(result);

        var options = {
             hAxis: {
                minValue:0,
                maxValue:6,
                format:'#',
                 viewWindow: {
                    min: 0
                }
            },

            title: ''
        };
        chart.draw(data, options);


        },{escape:false});
    }






</script>

Controller

BubbleChartController.cs

global with sharing class BubbleChartController {

    @RemoteAction
    global static String loadOpenCases() {

        Integer noOfCase, count = 0;
        List<CaseStatus> statusVal;
        /* json string for all open cases */
        String status, priority, json = '{"cols": [{"id": "Status", "label": "Status", "type": "string"}';

        /* Map of priority with column Order */
        map<integer, string> mapOrderPriority = new map<integer, string>();
        try {
            /* List of Open Cases */
            List<aggregateResult> results = [Select count(id) caseCount, Status, Priority
                                             From Case
                                             Where IsClosed != true
                                             Group By Status, Priority];



            /*Get Status Picklist Value*/
            statusVal = [SELECT Id, MasterLabel
                         FROM CaseStatus
                         WHERE IsClosed != true
                         ORDER BY SortOrder DESC];


            /*Get Priority Picklist Value*/
            Schema.DescribeFieldResult fieldResult = Case.Priority.getDescribe();
            List<Schema.PicklistEntry> priorityVal = fieldResult.getPicklistValues();

            /*Create json For Chart column*/
            for (Schema.PicklistEntry value : priorityVal ) {
                json += ',{"id": "Name", "label": "' + value.getValue() + '", "type": "number"},{"id": "annotation", "type": "number", "role":"annotation"}';
            }

            json += '],"rows": [';

            /*Create json For Chart row*/
            for (Integer sValCount = statusVal.size() - 1; sValCount >= 0; sValCount-- ) {
                json += '{"c":[{"v": "' + statusVal[sValCount].MasterLabel + '"}';

                for (Schema.PicklistEntry value : priorityVal ) {
                    noOfCase = 0;
                    // Get number of cases for Respective Status and Priority
                    for (aggregateResult resultVal : results ) {
                        status = (String)resultVal.get('Status');
                        priority = (String)resultVal.get('Priority');

                        if (status == statusVal[sValCount].MasterLabel && priority == value.getValue()) {
                            noOfCase = (Integer)resultVal.get('caseCount');
                            break;
                        }
                    }
                    json += ',{"v": ' + noOfCase + '},{"v": ' + noOfCase + '}';
                    //json += ',{"Row":"'+noOfCase+'"}';
                }
                json += ']},';
            }
            json += ']}';
        } catch (QueryException e) {
            system.debug( 'Exception IS====' + e.getMessage() );
        }
        return json;
    }

}

Please run those file in your salesforce org and let me know if you have any issue to run above code.