The Analytics API in Apex provides methods to retrieve metadata about reports as well as the ability to run reports and interpret the results. This article details how the Analytics API in Apex can be used in conjunction with the Google Visualization API to build a bubble chart with filtering.
Report and Chart
The Google Bubble Chart documentation describes a bubble chart as a chart that “is used to visualize a data set with two to four dimensions. The first two dimensions are visualized as coordinates, the third as color and the fourth as size.”
For this article, I created a summary report that groups Opportunities by stage, then probability, and sums the amounts. The report is used as the source of the data for the bubble chart.
The bubble chart plots bubbles that represent the sum of the amounts of Opportunities in a given stage with a given probability. Each bubble is also a certain color on the color scale, representing the number of Opportunities in it and a certain size representing the sum of the amounts.
The Code
Here is the Visualforce page and controller for reference. They are also available on GitHub. The rest of the article details the code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
<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> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
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; } } } |
Chart Construction
The chart is constructed by creating a DataTable representing the bubbles to be drawn and and specifying an object of chart configuration options. The DataTable consists of rows, each one representing a bubble, and columns, each one representing one of the attributes of the bubble. The bubble chart documentation defines the data format of the five possible columns as follows:
Column 0 Column 1 Column 2 Column 3 (optional) Column 4 (optional) Purpose: ID (name) of the bubble X coordinate Y coordinate Either a series ID or a value representing a color on a gradient scale,
depending on the column type:
- string:
A string that identifies bubbles in the same series. Use the same
value to identify all bubbles that belong to the same series;
bubbles in the same series will be assigned the same color. Series
can be configured using theseriesoption.- number:
A value that is mapped to an actual color on a gradient scale
using thecolorAxisoption.Size; values in this column are mapped to actual pixel values using the
sizeAxisoption.Data Type: string number number string or number number
The five columns in the Opportunity bubble chart are as follows:
- ID – This value shows up as text on the bubble. Since each bubble didn’t really need an actual unique identifier, I just set this to the number of Opportunities in the bubble.
- Y Coordinate – The Opportunity stage. This field must be a number according to the API and if it isn’t an error will occur stating that the type is invalid (e.g., column 1 cannot be of type string). More on this later.
- X Coordinate – The Opportunity probability. Note that it is represented as a whole number on the Opportunity and the Google API expects a decimal between 0 and 1, so the value from the Opportunity must be divided by 100.
- Value representing a color on a gradient scale – The number of Opportunities. The darker the bubble, the more Opportunities it represents.
- Size – Sum of Amounts of the Opportunities. The bigger the bubble, the more money it represents.
The JavaScript is divided in to two separate pieces: the code to define the bubble chart and the code to retrieve bubbles from the controller and display them on the chart. The chart definition is done a single time after the page loads, while the retrieval and display of the bubbles is done on the initial load and every time the user clicks the Update button.
The options are specified in the JavaScript as follows. Note that there are many more chart options that can be specified than were used.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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 } }; |
A min and max that are slightly lower and slightly higher than the lowest and highest values, respectively, are specified so that any bubble plotted on the extreme points (e.g., “Closed Won”, 100%) show up fully. The ticks (graph lines) are explicitly specified in the options. This is done so that each probability in the Sales Process defined in the org is represented by a tick. The stage ticks were a bit more tricky. The X and Y coordinate columns must be a number as defined by the API, but the stage name Opportunity field is a string. If a string is used an error “Column 2 cannot be of type string” is generated by the chart engine. Each tick has a value (v) and a format (f). The format is what gets displayed on the graph and the value is what is used to plot the bubble. Each tick value is specified as an integer increment and each format is the stage name. A JavaScript map is used to map the stage name to its value, so that when the bubbles are constructed there is a way to retrieve the value based on the stage name string of the bubble.
The Bubbles
A remote action is invoked to retrieve the bubble data for the DataTable. There is a from date input and a through date input for the Close Date. This allows the user to narrow or widen the Opportunities included in the chart. The date range is defaulted to the entire current year.
The getUpdatedBubbles remote action runs the report and then creates a List of Bubble objects to return. Bubble is a custom inner class in the controller.
|
1 2 3 4 5 6 |
@RemoteAction public static List<Bubble> getUpdatedBubbles(String fromDate, String throughDate) { Reports.ReportResults results = runReport(fromDate, throughDate); List<Bubble> bubbles = createBubbles(results); return bubbles; } |
The runReport method builds a Reports.ReportMetadata object to be used as options for the report, runs the report, and returns the results.
|
1 2 3 4 5 |
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; } |
The Reports.ReportManager.runReport method is overloaded to take a Reports.ReportMetadata argument in addition to the report ID. This is very powerful. It functions the same as the POST request body in the Analytics REST API. A whole lot more can be done than just the date filtering done here. The getReportOptions method constructs two Reports.ReportFilter objects and sets them as the report filters on the Reports.ReportMetadata. Note that the dates must be in the format of yyyy-MM-dd or an error “Filter the date in the correct format. Accepted formats are yyyy-MM-dd’T’HH:mm:ss’Z’ and yyyy-MM-dd.” will be generated. You can find the available columns and filter operator values by looking at the report type metadata. This article has more detail on that.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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; } |
The Reports.ReportResults are returned and then used to create the bubbles. The createBubbles method processes the Reports.Dimension representing the first level grouping in the report (Stage Name). Each Reports.Dimension has a List of Reports.GroupingValue objects. Each Reports.GroupingValue has a value representing the value, e.g., “Closed Won” as well as a key that can be used to get the summary values for that grouping from the fact map (more on that shortly).
The Reports.ReportResults also contain a Map<String, Reports.ReportFact> fact map obtained by calling the getFactMap method. The format of the keys in the map are defined in the Decode the Fact Map section of the Analytics API reference:
<First level row grouping_second level row grouping_third level row grouping>!T: T refers to the row grand total. Grouping levels are identified under groupingsDown.
The value of the key in each Reports.GroupingValue instance simply needs to have “!T” appended to it to get its summary value from the fact map. Since the bubbles in the chart represent the sum of the amounts by Stage Name and Probability, the second level grouping values are retrieved from the fact map.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Reports.Dimension groupingsDown = results.getGroupingsDown(); Map<String, Reports.ReportFact> factMap = results.getFactMap(); 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()) ); } } |
Note the amountIdx and rowCountIdx variables used to get the values from the Reports.ReportFact. The fact.getAggregates() returns a List of Reports.SummaryValue objects. The position of each entry in the list is the same as the position in the Reports.ReportMetadata’s aggregates list, so that can be queried prior to processing the the results.
|
1 2 3 4 5 6 7 8 9 10 |
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; } } |
The remote action is invoked from the updateBubbles function in the JavaScript.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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} ); } |
Once the List of Bubbles has been constructed it is returned from the remote action as an array of JavaScript objects. The JavaScript callback function, drawBubbles, iterates over the list, adding each one as a row to the DataTable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
function drawBubbles(bubbles) { // if there are any existing bubbles, clear them if (dataTable.getNumberOfRows() > 0) { dataTable.removeRows(0, dataTable.getNumberOfRows()); } for (var i = 0; i < bubbles.length; i++) { dataTable.addRow([ bubbles[i].rowCount.toString(), bubbles[i].probability/100.0, stages[bubbles[i].stage], bubbles[i].rowCount, bubbles[i].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); } |
After the rows have been added formatting as done for the probability percentage and the amount currency. Finally, the chart is drawn.
The from and through dates can be set to different values to narrow or widen the reporting period. When the Update button is clicked the updateBubbles function is called to invoke the remote action and draw the bubbles again.
A tooltip is displayed on the hover of a bubble. The stage is displayed as its numerical value. To make the tooltip stage value easier to interpret the numerical value of each stage is displayed on the chart as part of its tick label (e.g., “Closed Won – 5”).
A Different Kind of Bubble Chart
Column three can be a number or a string. In the Opportunity bubble chart it was a number representing the number of Opportunities and the values mapped to a color on a gradient scale. To experiment with the other option of it being a string, I created a slightly different summary report that that groups Opportunities by region, stage, and probability, and sums the amounts. Region is a custom picklist field on the Account with values such as North, East, South, West.
The resulting chart is as follows:
The code change was very simple. The Apex changed to add another outer level of groupingsDown to represent the region and the region was added to the Bubble.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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()) ); } } } |
The JavaScript changed the column at index 3 to be the region and its type to be String.
|
1 2 3 4 5 6 7 8 9 10 |
... dataTable.addColumn('string', 'Region'); ... dataTable.addRow([ b.rowCount.toString(), b.probability/100.0, stages[b.stage], b.region, b.amount ]); |
The issue with this chart is bubble overlap that occurs when more than one region has Opportunities in the same stage and probability (very likely, of course). I could have written some code to detect the overlap that would adjust the underlying values very slightly to create a slight offset for better viewing or written JavaScript to alter the chart itself.
Tips/Tricks
Any of the Apex objects can be converted to JSON and output on the page or the debug log very easily. For example:
|
1 2 3 4 |
Somewhere in the controller... resultsJson = JSON.serializePretty(results); ...Somewhere on the page <pre>{!resultsJson}</pre> |
Google has excellent documentation on the Bubble Chart and on the Google Visualization API. Google provides a visualization playground that can be used to quickly experiment with various options and configurations of different charts.
Conclusions
This article described how the Analytics API in Apex can be combined with the Google Visualization API to create bubble charts. I barely scratched the surface on what can be done. There are many other options and methods available in both APIs. The great thing about the Apex Analytics API is that it can be used with any client side charting library. With hardly any (likely zero) modifications the BubbleChartController written for this article could be used with a completely different charting library.
All code is available on GitHub.
Hi peter
The Article is excellent.
Can you provide the links for understanding the methods in Analytic API in apex?
Thanks in advance
Thanks Maneesh. Glad you liked it. 🙂
There is not any documentation on the methods in Apex at the time of this writing. I used the REST documentation as a reference. The code completion feature of the Developer Console helped me figure out the equivalent Apex features.
Thank you for the reply peter.
The “Reports.ReportManager.runReport(id,options)” method oi not returning value for tabular report. I am not made anything to summarize in the report . also not fix any filters .
The “Reports.ReportManager.runReport(id,options)” method oi not returning value for tabular report. I am not made anything to summarize in the report ,also not fixed any filters .
The Reports.ReportManager.RunReport method is overloaded to take a Boolean includeDetails argument.
If you want the details you must include it. For example:
Reports.ReportResults results = Reports.ReportManager.runReport(REPORT_ID, options, true);This is similar to the REST API method of passing includeDetails=true in request.
I just saw that the new Apex Developer’s Guide is available. You can now see the Reports Namesapce documentation.
The steps to generate the report is not working at all. Can any one give a try. Any more changes to be done apart from the report id on line # 4 of BubbleChartController.
Yes, i have also tried..the graph is not getting displayed..Anybody having idea?
New web invent:
http://nelson.projects.telrock.org