Skip to content

Instantly share code, notes, and snippets.

@peterknolle
Created February 9, 2014 19:17
Show Gist options
  • Save peterknolle/8904546 to your computer and use it in GitHub Desktop.
Save peterknolle/8904546 to your computer and use it in GitHub Desktop.
Analytics API in Apex and Google Visualization API - Bubble Chart
<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>
<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>
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;
}
}
}
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;
}
}
}
@peterknolle
Copy link
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment