Google Visualization API Joins in Visualforce

The Google Visualization API provides a rich set of functionality for manipulating and visualizing data.  In particular, there is a a join() method that can join data between two tables of data.  This can be used in Visualforce to create Google charts or tables that report on two objects that do not have relationships with the same object(s) (required for Salesforce Joined Reports).  The rest of this article explores how the Google Visualization API can be used in a custom component to create joins of data from Salesforce.

Join Method Definition

The syntax of the join method is documented in the Google Visualization API Reference as follows:

google.visualization.data.join(dt1, dt2, joinMethod, keys, dt1Columns, dt2Columns)

The method joins tables dt1 and dt2 on the specified key columns, using the specified join method (full, inner, left, right).  The resulting table contains the key columns, and the additionally specified columns from dt1 (dt1Columns) and dt2 (dt2Columns).

Example Data

The sample code for this article seeks to relate the number of site page views on a date with the number of Leads created on that date.  In the interest of being able to create mock data I created a custom field Created_Date__c on Lead, but in reality the standard CreatedDate field would be used.  Additionally, I created a Page View Summary object that summarizes the number of page views for a date.  In reality, report types from the Force.com Sites Usage Reporting AppExchange, some other site analytics app, or your own custom objects might be used.

The Page

The finished page displays a Google Line Chart and a Google Table.  The mock data has two dates on which no Leads were created.  The first screen shot below shows a left join of Page View Summary to Lead AggregateResult.  A left join includes all rows from the left table (Page View Summary) regardless of whether there are rows in the right table (Lead AggregateResult).  Unmatched rows will have null values in the resulting table.  On the two dates with no Leads created a break in the graph is displayed.  This is due to the value being null in the join. The Google Visualization API has an option to interpolate nulls, but there is no option to substitute zeroes for nulls. Separate post processing or data manipulation would need to be done to achieve that.  For simplicity, that wasn’t implemented.

visualforce page with left join

The second screen shot shows an inner join.  The inner join shows only those rows where keys match.  Notice the breaks in the graph have gone away.  That is because the dates with no Leads are not included in the result table, even though there were Page Views for those dates.

visualforce page with inner join

Controller

The controller is extremely simple, so it is not shown in this article.  It just gets the data and serializes it to JSON for the page to use.  SOQL is used; however, the Analytics API via Apex might be more appropriate depending on the situation.  The Controller code is available in this Gist.

Visualforce Page

The page is very simple, as well.  It loads the Google libraries and then creates an instance of a custom component, <c:join>, that hides the implementation details of how the joins are executed and displayed.

The <c:join> custom component requires the developer to specify the data JSON for both tables, configuration JSON for both tables, and join information.

The data JSON is the JSON that is generated by the Controller.

The config JSON requires the user to specify a label, data type, and name for each column in the data.  The name should match the name in the data JSON (e.g., field name).

The keys specify the columns, by position, from each table that should be used as keys in the join.  In this case it is the date field which is column 0.

The dt1Cols and dt2Cols specify the columns of dt1 and dt2 that should be included in the result table, in addition to the keys.  In this case both just have the one extra column, so the results table will have three columns: 1) the date key, 2) the number of views, and 3) the number of leads.

The JSON for the config, keys, dt1Cols, and dt2Cols was hard-coded, but it could have been determined dynamically from the controller like the data JSON was.

Visualforce Custom Component

The custom component contains JavaScript for building the Google DataTables objects, joining and drawing them initially, and redrawing them when the join type is switched.

Constructing the DataTables

Each DataTable is created by the createTable function.

The createTable function uses the data JSON and config JSON to construct the DataTable.  First, it defines the columns and then populates each row, using the column definitions from the config to do both.  Property access using bracket notation is used to dynamically get the value from the data.

Joining and Drawing

Once the two DataTables have been constructed they can be joined and the resultant joined chart and table can be drawn.  The chart is a LineChart, so it might not always make sense to draw it depending on the make up of the input data.  Optional component attributes control whether the table or chart are drawn.  Since the component could be used multiple times on the same page a unique identifier attribute, ident, is used to prefix all element ids.

The user can select a different join type from the select options and click the button to apply the new join.  The DataTables do not need to be populated again.  The button click event handler calls the same drawJoin method that is used when the component is initially loaded.

Alternative

An alternative to using the Google join() method to join together two objects that do not have relationships with the same object(s) would be to join them directly in the controller using Apex.  Custom Apex would need to be written that replicated the join logic.  Some advantages of using the Google join() method as it is in this article, is that it allows the end user to select and apply join types dynamically and the joining logic is taken care of by the Google library.

Summary

Using the join() method of the Google Visualization API in a custom Visualforce component was explored in detail.  How to use the component was described and the implementation of the component was detailed.  The component, or code similar to what is in it, could be used to give an end user the ability to join data, with a join method of their choice, from objects that do not have any relationships to the same object(s).

All code is available in this Gist.

2 thoughts on “Google Visualization API Joins in Visualforce

  1. Hey There. I found your blog using msn. This is an extremely well written article.

    I will be sure to bookmark it and come back to read more of your useful information. Thanks for the post.
    I will certainly return.

    My website … additional reading – Milagro

  2. I see you share interesting stuff here, you can earn some extra cash, your website has huge potential,
    for the monetizing method, just search in google – K2
    advices how to monetize a website

Leave a Reply

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