Displaying Salesforce Data with Google Charts and AJAX Toolkit

In this second post about using Google Charts to visualize Salesforce data, I’ll show how to use the AJAX Toolkit to query data in related objects and display a summary in a chart. In the first example, I created a column chart with data from custom fields on the Account. In this next example, let’s assume we have an object called AccountHistory which has the sales by Product and Account. On the Account page layout, we want to be able to see a summary of sales by Product Family.

In order to show this, we need to run a SOQL query summarizing sales by Product Family. I’ll take advantage of the AJAX Toolkit to query the data without having to use a custom controller. Using the AJAX toolkit is pretty simple to query data. First we query data using SOQL and then iterate over the results, adding each record returned to the chart.

<apex:page standardController="Account">
  <head>
  <script type='text/javascript' src='https://www.google.com/jsapi' />
  <script src="/soap/ajax/19.0/connection.js" type="text/javascript" />
 
  <script type="text/javascript">
 
  // use this function to format the label
  formatCurrencyLabel = function( value )
    {
      return "$" + String(value);
    }
 
    google.load("visualization", "1", {packages:["corechart"]});
    google.setOnLoadCallback(drawChart);
 
    function drawChart() {
        // Create a new data table with two columns: the label and the value
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Product Family');
        data.addColumn('number', 'Sales');
 
        // We need the sessionId to be able to query data
        sforce.connection.sessionId = '{!$Api.Session_ID}';
        // Query data using SOQL.
        var result = sforce.connection.query("Select ah.Product__r.Family productFamily, sum(ah.Sales__c) sales " +
                      "from AccountHistory__c ah where ah.Account__c = '{!account.Id}' group by ah.Product__r.Family " +
                      "order by sum(ah.Sales__c) desc");
        // Iterate over the result
        var it = new sforce.QueryResultIterator(result);
        while(it.hasNext()) {
            var record = it.next();
            // Add the data to the table
            data.addRow([record.productFamily, {v:parseFloat(record.sales), f: formatCurrencyLabel(record.sales)}]);
        }
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, {width: 800, height: 300, pieSliceText:'label'});
    };
 
  </script>
  </head>
  <div id="chart_div" />
</apex:page>

Once we have the Visualforce page created, it can be added to an Account page layout. Remember to make the height the same as defined on the page (in this case 300 px). The result should look something like this:

I’ve updated my unmanaged package to include this example. It includes the custom object and Visualforce page as well as the example from my previous post. You can install it into your org by clicking on the following link: https://login.salesforce.com/packaging/installPackage.apexp?p0=04t80000000xviT. Make sure you have some products with Product Families assigned and then populate some Account History records. Add the new Visualforce page to an existing page layout and you should be up and running.

Obviously, this is a very simple example, but I hope it illustrates how this can be a great addition to any page layout.

Updating Salesforce Data with SQLForce

Recently, Jeff Douglas posted about an ANSI SQL command line tool on his blog. The tool, called SQLForce, fills in some holes in SOQL. Specifically, it allows you to run commands such as UPDATE, DELETE, INSERT. You can also output data to txt files.

Jeff posted a great video of how to use it on his blog, but I prefer written steps, so here they are. CAUTION: you can really screw up your data with this tool, so make sure the command you are running is what you want.

  1. Download the most recent version in a zip file (sqlforce_1.20.5.zip) from http://code.google.com/p/sqlforce/downloads/list.
  2. Extract the zip file to an easy to access folder.
  3. Open a command prompt in the extracted sqlforce folder and then run the command java -jar sqlforce.jar
  4. Type in “HELP” to see all available commands.
  5. Connect to your Salesforce Org (I’d recommend trying it out in your sandbox first): CONNECT [PRODUCTION:SANDBOX] username password [activationKey]
  6. Wait for the connection to complete – it takes a while.
  7. Run SQL commands on your Salesforce data!

There are some good code snippets to see some of the ways to use the tool at http://code.google.com/p/sqlforce/wiki/SQLForce. One of my uses is for quick updates or deletes of certain data. For example, I had a problem with a picklist value and needed to update a bunch of records that had a certain picklist value to change it.

Displaying Salesforce Data with Google Chart Tools

Sometimes the standard text only detail pages in Salesforce get a bit dull and you need to spice things up with a chart. One trick I use is to add custom VisualForce pages to my page layouts to display inline charts. I’ll be using the Google Chart Tools library to make this happen. This is an all Javascript approach and none of your data goes to another server to render the chart – it also avoids the use of Flash so you can view these charts on an iPad or other iOS device.

To get started, I created some custom fields on the account to show revenue from previous years. I’ll call these fields Revenue 2008, 2009, 2010, etc. They will be the revenue for the account for each year. What I want is a column chart showing the revenue for each year so I can visualize whether sales for this account are trending up or down. To do this, I need to create a new VisualForce page. The page uses the Account standard controller and just accesses the fields it needs to display on the chart, puts the data in a Google data table and then draws the chart.

[sourcecode]
<apex:page standardController="Account">

<head>

<script type=’text/javascript’ src=’https://www.google.com/jsapi’ />

<script type="text/javascript">

//use this function to format the hover text
formatCurrencyLabel = function ( value )
{
return "$" + String(value);
}

google.load("visualization", "1", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);

function drawChart() {
// we need a table to act as the datasource for the chart
var data = new google.visualization.DataTable();

// the table needs two columns
data.addColumn(‘string’, ‘Period’);
data.addColumn(‘number’, ‘Annual Revenue’);

// add the rows of data which consist of a value and a formatted label
data.addRow([‘2008’, {v:{!Account.Revenue_2008__c}, f: formatCurrencyLabel({!Account.Revenue_2008__c})}]);
data.addRow([‘2009’, {v:{!Account.Revenue_2009__c}, f: formatCurrencyLabel({!Account.Revenue_2009__c})}]);
data.addRow([‘2010’, {v:{!Account.Revenue_2010__c}, f: formatCurrencyLabel({!Account.Revenue_2010__c})}]);
data.addRow([‘2011’, {v:{!Account.Revenue_2011__c}, f: formatCurrencyLabel({!Account.Revenue_2011__c})}]);

//assign the chart to the appropriate div and draw the chart
var chart = new google.visualization.ColumnChart(document.getElementById(‘chart_div’));
chart.draw(data, {width: 300, height: 200, title:’Annual Revenue Trend’, legend:’none’,
vAxis: {minValue:0}, hAxis: {slantedTextAngle:45}});
};

</script>

</head>

<body>
<div id="chart_div" />
</body>
</apex:page>

[/sourcecode]

With the page created, I can now edit the Account page layout and add the new page to it. Remember to set the width of it to 100% and the height to be the same as what you set in the javascript (in this case 200px). You should get something that looks kind of like this. Note the hover text.

Google Chart Example

I’ve created an unmanaged package with the custom fields and the VisualForce page. You can install it here: https://login.salesforce.com/packaging/installPackage.apexp?p0=04t80000000xviP. Once you install it, add the page to a page layout, populate the custom fields and you should be good to go.

My next post will show how to use this technique with the AJAX toolkit to query data and display it in a chart – really useful for summarizing related objects or creating your own dashboards.