Analytics API in Apex and Google Visualization API - Bubble Chart

bubbleChart.page

<apex:page readOnly="true" controller="BubbleChartController" docType="html-5.0">

<script src="https://www.google.com/jsapi"></script>
<script>
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);

var chart, dataTable, options;  
var stages = { 
    "Closed Lost" : 0,
    "Prospecting": 1,
    "Analysis": 2,
    "Proposal": 3,
    "Negotiation": 4,
    "Closed Won": 5
};

function drawChart() {
    chart = new google.visualization.BubbleChart(document.getElementById('chart_div'));
    options = {
        title: "Opportunity Bubble Chart",
        height: 500,
        width: 900,
        colorAxis: { colors: ['yellow', 'red'] },
        vAxis: { 
            ticks: [
                { v: -1, f: "" },
                { v: 0, f: "Closed Lost - 0" },
                { v: 1, f: "Prospecting - 1" },
                { v: 2, f: "Analysis - 2" },
                { v: 3, f: "Proposal - 3" },
                { v: 4, f: "Negotiation - 4" },
                { v: 5, f: "Closed Won - 5" }
            ],
            title: "Stage",
            minValue: -1,
            maxValue: 7
        },
        hAxis : {
            ticks: [
                { v: 0, f: "0%" },
                { v: .10, f: "10%" },
                { v: .30, f: "30%" },
                { v: .60, f: "60%" },
                { v: .75, f: "75%" },
                { v: .95, f: "95%" },
                { v: 1.00, f: "100%" }
            ],
            title: "Probability",
            format: '###%',
            minValue: -0.2,
            maxValue: 1.2
        }
    };
    
    // Construct the data table and define columns
    createDataTable();
    
    // Invoke remote action to get bubbles and draw chart
    updateBubbles();
}

function createDataTable() {
    dataTable = new google.visualization.DataTable();
    dataTable.addColumn('string', 'ID');
    dataTable.addColumn('number', 'Probability');
    dataTable.addColumn('number', 'Stage');
    dataTable.addColumn('number', 'Number of Opportunities');
    dataTable.addColumn('number', 'Amount');
}

function updateBubbles() {

    // Get the date strings (yyyy-MM-dd)
    var fromDate = document.getElementById('fromDate').value;
    var throughDate = document.getElementById('throughDate').value;

    Visualforce.remoting.Manager.invokeAction(
        '{!$RemoteAction.BubbleChartController.getUpdatedBubbles}', 
        fromDate,
        throughDate, 
        function(bubbles, event){
            if (event.status) {
                // success!
                drawBubbles(bubbles);
            } else if (event.type === 'exception') {
                document.getElementById('errors').innerHTML = event.message;
            } else {
                document.getElementById('errors').innerHTML = event.message;
            }
        }, 
        {escape: true}
    );
}

function drawBubbles(bubbles) {
    // if there are any existing bubbles, clear them
    if (dataTable.getNumberOfRows() > 0) {
        dataTable.removeRows(0, dataTable.getNumberOfRows());
    }
    
    var b;
    for (var i = 0; i < bubbles.length; i++) {
        b = bubbles[i];
        dataTable.addRow([
            b.rowCount.toString(),
            b.probability/100.0,
            stages[b.stage],
            b.rowCount,
            b.amount
        ]);   
    }
    
    var pctF = new google.visualization.NumberFormat({pattern:'###%'});
    pctF.format(dataTable, 1);
    var currF = new google.visualization.NumberFormat({pattern:'$#,###'});
    currF.format(dataTable, 4);
    
    chart.draw(dataTable, options); 
}
</script>

<div id="errors"></div>    
<div id="chart_div"></div> 

<h1>Close Date Filter</h1>
<apex:form >
    From: 
    <input type="date" value="{!deafultFromDate}" id="fromDate" />
    Through: 
    <input type="date" value="{!defaultThroughDate}" id="throughDate" /><br/>
    <button onclick="updateBubbles(); return false;">Update</button>
</apex:form>

</apex:page>

bubbleChart2.page

<apex:page readOnly="true" controller="BubbleChart2Controller" docType="html-5.0">

<script src="https://www.google.com/jsapi"></script>
<script>
google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);

var chart, dataTable, options;  
var stages = { 
    "Closed Lost" : 0,
    "Prospecting": 1,
    "Analysis": 2,
    "Proposal": 3,
    "Negotiation": 4,
    "Closed Won": 5
};

function drawChart() {
    chart = new google.visualization.BubbleChart(document.getElementById('chart_div'));
    options = {
        title: "Opportunity Bubble Chart",
        height: 500,
        width: 900,
        colorAxis: { colors: ['yellow', 'red'] },
        vAxis: { 
            ticks: [
                { v: -1, f: "" },
                { v: 0, f: "Closed Lost - 0" },
                { v: 1, f: "Prospecting - 1" },
                { v: 2, f: "Analysis - 2" },
                { v: 3, f: "Proposal - 3" },
                { v: 4, f: "Negotiation - 4" },
                { v: 5, f: "Closed Won - 5" }
            ],
            title: "Stage",
            minValue: -1,
            maxValue: 7
        },
        hAxis : {
            ticks: [
                { v: 0, f: "0%" },
                { v: .10, f: "10%" },
                { v: .30, f: "30%" },
                { v: .60, f: "60%" },
                { v: .75, f: "75%" },
                { v: .95, f: "95%" },
                { v: 1.00, f: "100%" }
            ],
            title: "Probability",
            format: '###%',
            minValue: -0.2,
            maxValue: 1.2
        }
    };
    
    // Construct the data table and define columns
    createDataTable();
    
    // Invoke remote action to get bubbles and draw chart
    updateBubbles();
}

function createDataTable() {
    dataTable = new google.visualization.DataTable();
    dataTable.addColumn('string', 'ID');
    dataTable.addColumn('number', 'Probability');
    dataTable.addColumn('number', 'Stage');
    dataTable.addColumn('string', 'Region');
    dataTable.addColumn('number', 'Amount');
}

function updateBubbles() {

    // Get the date strings (yyyy-MM-dd)
    var fromDate = document.getElementById('fromDate').value;
    var throughDate = document.getElementById('throughDate').value;

    Visualforce.remoting.Manager.invokeAction(
        '{!$RemoteAction.BubbleChart2Controller.getUpdatedBubbles}', 
        fromDate,
        throughDate, 
        function(bubbles, event){
            if (event.status) {
                // success!
                drawBubbles(bubbles);
            } else if (event.type === 'exception') {
                document.getElementById('errors').innerHTML = event.message;
            } else {
                document.getElementById('errors').innerHTML = event.message;
            }
        }, 
        {escape: true}
    );
}

function drawBubbles(bubbles) {
    // if there are any existing bubbles, clear them
    if (dataTable.getNumberOfRows() > 0) {
        dataTable.removeRows(0, dataTable.getNumberOfRows());
    }
    
    var b;
    for (var i = 0; i < bubbles.length; i++) {
        b = bubbles[i];
        dataTable.addRow([
            b.rowCount.toString(),
            b.probability/100.0,
            stages[b.stage],
            b.region,
            b.amount
        ]);   
    }
    
    var pctF = new google.visualization.NumberFormat({pattern:'###%'});
    pctF.format(dataTable, 1);
    var currF = new google.visualization.NumberFormat({pattern:'$#,###'});
    currF.format(dataTable, 4);
    
    chart.draw(dataTable, options); 
}
</script>

<div id="errors"></div>    
<div id="chart_div"></div> 

<h1>Close Date Filter</h1>
<apex:form >
    From: 
    <input type="date" value="{!deafultFromDate}" id="fromDate" />
    Through: 
    <input type="date" value="{!defaultThroughDate}" id="throughDate" /><br/>
    <button onclick="updateBubbles(); return false;">Update</button>
</apex:form>

</apex:page>

BubbleChart2Controller.cls

public with sharing class BubbleChart2Controller{
    public String deafultFromDate { get; set; }
    public String defaultThroughDate { get; set; }
    
    private static final String REPORT_ID = '00Ox0000000hEzq';
    
    public BubbleChart2Controller() {
        // Default to the current year.
        // These instance variables are only used to
        // populate the default values on the inputs.
        Integer year = Date.today().year();
        deafultFromDate = String.valueOf( Date.newInstance(year, 1, 1) );
        defaultThroughDate = String.valueOf( Date.newInstance(year + 1, 1, 1).addDays(-1) );
    }
   
    @RemoteAction
    public static List<Bubble> getUpdatedBubbles(String fromDate, String throughDate) {
        Reports.ReportResults results = runReport(fromDate, throughDate);  
        List<Bubble> bubbles = createBubbles(results);
        return bubbles;
    }
    
    public static Reports.ReportResults runReport(String fromDate, String throughDate) {
        Reports.ReportMetadata options = getReportOptions(fromDate, throughDate);
        Reports.ReportResults results = Reports.ReportManager.runReport(REPORT_ID, options);
        return results;
    }
    
    private static Reports.ReportMetadata getReportOptions(String fromDate, String throughDate) {
        List<Reports.ReportFilter> reportFilters = new List<Reports.ReportFilter>();
        Reports.ReportFilter fromFilter = new Reports.ReportFilter();
        fromFilter.setColumn('CLOSE_DATE');
        fromFilter.setOperator('greaterOrEqual');
        fromFilter.setValue(fromDate);
        reportFilters.add(fromFilter);
        
        Reports.ReportFilter toFilter = new Reports.ReportFilter();
        toFilter.setColumn('CLOSE_DATE');
        toFilter.setOperator('lessOrEqual');
        toFilter.setValue(throughDate);
        reportFilters.add(toFilter);
        
        
        Reports.ReportMetadata opts = new Reports.ReportMetadata();
        opts.setReportFilters(reportFilters);
        
        return opts;
    }
    
    private static List<Bubble> createBubbles(Reports.ReportResults results) {          
        // Get the index of each aggregate from the metadata. 
        // Will be used to get values from the fact map aggregates.
        Integer amountIdx, rowCountIdx;
        List<String> aggColNames = results.getReportMetadata().getAggregates();
        for (Integer i = 0, cnt = aggColNames.size(); i < cnt; i++) {
            String aggColName = aggColNames.get(i);
            if (aggColName == 's!AMOUNT') {
                amountIdx = i;
            } else if (aggColName == 'RowCount') {
                rowCountIdx = i;
            }
        }
        
        Reports.Dimension groupingsDown = results.getGroupingsDown();
        Map<String, Reports.ReportFact> factMap = results.getFactMap(); 
        List<Bubble> bubbles = new List<Bubble>();
        
        for (Reports.GroupingValue grouping1 : groupingsDown.getGroupings()) {
            String region = (String) grouping1.getValue();
       
            for (Reports.GroupingValue grouping2 : grouping1.getGroupings()) {
               String stage = (String) grouping2.getValue();
                
                for (Reports.GroupingValue grouping3 : grouping2.getGroupings()) {
                    Decimal probability = (Decimal) grouping3.getValue();
                        
                    Reports.ReportFact fact = factMap.get(grouping3.getKey() + '!T');
                    Decimal amount = (Decimal) fact.getAggregates().get(amountIdx).getValue();
                    Decimal rowCount = (Decimal) fact.getAggregates().get(rowCountIdx).getValue();
    
                    bubbles.add( new Bubble(region, stage, probability, amount, rowCount.intValue()) );
                }
            }
        }
        return bubbles;
    }
   
    class Bubble {
        public String region { get; set; }
        public String stage { get; set; }
        public Decimal probability { get; set; }
        public Decimal amount { get; set; }
        public Integer rowCount { get; set; }
        
        public Bubble(String rg, String s, Decimal p, Decimal a, Integer rc) {
            region = rg;
            stage = s;
            probability = p;
            amount = a;
            rowCount = rc;
        }
    }
}

BubbleChartController.cls

public with sharing class BubbleChartController{
    public String deafultFromDate { get; set; }
    public String defaultThroughDate { get; set; }
    
    private static final String REPORT_ID = '00Ox0000000hIrg';
    
    public BubbleChartController() {
        // Default to the current year.
        // These instance variables are only used to
        // populate the default values on the inputs.
        Integer year = Date.today().year();
        deafultFromDate = String.valueOf( Date.newInstance(year, 1, 1) );
        defaultThroughDate = String.valueOf( Date.newInstance(year + 1, 1, 1).addDays(-1) );
    }
   
    @RemoteAction
    public static List<Bubble> getUpdatedBubbles(String fromDate, String throughDate) {
        Reports.ReportResults results = runReport(fromDate, throughDate);  
        List<Bubble> bubbles = createBubbles(results);
        return bubbles;
    }
    
    public static Reports.ReportResults runReport(String fromDate, String throughDate) {
        Reports.ReportMetadata options = getReportOptions(fromDate, throughDate);
        Reports.ReportResults results = Reports.ReportManager.runReport(REPORT_ID, options);
        return results;
    }
    
    private static Reports.ReportMetadata getReportOptions(String fromDate, String throughDate) {
        List<Reports.ReportFilter> reportFilters = new List<Reports.ReportFilter>();
        Reports.ReportFilter fromFilter = new Reports.ReportFilter();
        fromFilter.setColumn('CLOSE_DATE');
        fromFilter.setOperator('greaterOrEqual');
        fromFilter.setValue(fromDate);
        reportFilters.add(fromFilter);
        
        Reports.ReportFilter toFilter = new Reports.ReportFilter();
        toFilter.setColumn('CLOSE_DATE');
        toFilter.setOperator('lessOrEqual');
        toFilter.setValue(throughDate);
        reportFilters.add(toFilter);
        
        
        Reports.ReportMetadata opts = new Reports.ReportMetadata();
        opts.setReportFilters(reportFilters);
        
        return opts;
    }
    
    private static List<Bubble> createBubbles(Reports.ReportResults results) {          
        // Get the index of each aggregate from the metadata. 
        // Will be used to get values from the fact map aggregates.
        Integer amountIdx, rowCountIdx;
        List<String> aggColNames = results.getReportMetadata().getAggregates();
        for (Integer i = 0, cnt = aggColNames.size(); i < cnt; i++) {
            String aggColName = aggColNames.get(i);
            if (aggColName == 's!AMOUNT') {
                amountIdx = i;
            } else if (aggColName == 'RowCount') {
                rowCountIdx = i;
            }
        }
        
        Reports.Dimension groupingsDown = results.getGroupingsDown();
        Map<String, Reports.ReportFact> factMap = results.getFactMap(); 
        List<Bubble> bubbles = new List<Bubble>();
        
        for (Reports.GroupingValue grouping1 : groupingsDown.getGroupings()) {
           String stage = (String) grouping1.getValue();
            
            for (Reports.GroupingValue grouping2 : grouping1.getGroupings()) {
                Decimal probability = (Decimal) grouping2.getValue();
                    
                Reports.ReportFact fact = factMap.get(grouping2.getKey() + '!T');
                Decimal amount = (Decimal) fact.getAggregates().get(amountIdx).getValue();
                Decimal rowCount = (Decimal) fact.getAggregates().get(rowCountIdx).getValue();

                bubbles.add( new Bubble(stage, probability, amount, rowCount.intValue()) );
            }
        }
        return bubbles;
    }
   
    class Bubble {
        public String stage { get; set; }
        public Decimal probability { get; set; }
        public Decimal amount { get; set; }
        public Integer rowCount { get; set; }
        
        public Bubble(String s, Decimal p, Decimal a, Integer rc) {
            stage = s;
            probability = p;
            amount = a;
            rowCount = rc;
        }
    }
}

This is code from my blog article Using the Analytics API in Apex to Create a Google Bubble Chart.