Using the Analytics API in Apex to Create A Google Bubble Chart

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.

Opportunity Report by Stage and Probability

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.

Opportunity Bubble Chart

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.

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 the series option.
  • number:
    A value that is mapped to an actual color on a gradient scale
    using the colorAxis option.
Size; values in this column are mapped to actual pixel values using the
sizeAxis option.
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.

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.

The runReport method builds a Reports.ReportMetadata object to be used as options for the report, runs the report, and returns the 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.

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.

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.

The remote action is invoked from the updateBubbles function in the JavaScript.

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.

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.

Opportunity Report by Region, Stage, and Probability

The resulting chart is as follows:

Opportunity Bubble Chart

 

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.

The JavaScript changed the column at index 3 to be the region and its type to be String.

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:

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.

10 thoughts on “Using the Analytics API in Apex to Create A Google Bubble Chart

  1. Hi peter
    The Article is excellent.
    Can you provide the links for understanding the methods in Analytic API in apex?
    Thanks in advance

    1. 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.

  2. 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 .

  3. 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 .

  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *