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.

36 thoughts on “Displaying Salesforce Data with Google Chart Tools”

    1. Thanks Cory! I’ll have to take a look at your code. I have definitely run into limitations with the built in Salesforce dashboards and have seriously considered trying to roll my own. I hope to get a new post up soon about how I use the AJAX toolkit to query data from within the JavaScript and then build reports. It might also be fun to do something with JavaScript Remoting too.

  1. Just wanted to say Daniel, this is superb. I know I struggled with your other solution (the AJAX Toolkit one), but this one comes in just fine! Love it. Thanks so much for sharing this!

  2. Daniel, I’m trying to populate the chart with data from a related object Sales_History__c (lookup relationship). Replaced your {!Account.Revenue_2008__c} with my {!Sales_History__r.SC01__c} field but getting error Error: Unknown property ‘AccountStandardController.Sales_History__r’ when I try to save the VF page. What am I missing?

    1. You can’t access related objects using this technique. You’ll need to use SOQL to grab the data. Have you looked at my other post on using SOQL to display data?

  3. I did redo it using the SOQL approach and left a script sample over there. I cannot get anything to show on the page. Please check over there. Thanks.

  4. This is a great example, thank you for sharing and putting it in on the AppExchange. I know it’s possible to display a Google Chart using a custom formula field and the IMAGE function. Could you provide a brief explanation for why/when it would be better to use one method vs the other? Does it relate to your sentence, “This is an all Javascript approach and none of your data goes to another server to render the chart.”?

    1. Thanks! I prefer this approach over the Google image charts for a couple of reasons. First, your data doesn’t get sent to another server for rendering. Second, it is much more flexible and allows things like mouse over, on-click and other events to allow you to interact with the chart and data.

  5. Hi..

    The result looks great but is it restricted to just standard object? I want to use is on a custom object.

    Pls guide me.

    Thanks!

    1. Sure, this can be used for any object. In fact, you could probably generalize the code to have only one class that does it for you. Contacts is just the most common one, since it is a master/ detail relationship with Accounts.

      1. My comment above probably seems odd. I replied via my phone and thought the comment was on a different post. That said, this technique can be used for custom objects easily. You’d change the standard controller to reference the custom object and then update what fields are used as well.

        1. Would this not work with roll-up fields? I tried the code out with custom fields I have for accounts that reference opportunities (specifically, the fields pull totals of closed opportunities for each year) and I’m getting the unknown property error. Specifically this:

          Error: Unknown property ‘AccountStandardController.cx__Last_Year_Transaction_Amount__c’

          Hoping I can make it work since I’d love to have visual summaries of folks’ giving histories on their account pages for my non-profit SF instance!

          1. It works for me to add a rollup field. Is there a reason you are using AccountStandardController instead of just Account? Also, it looks like you are using a namespace. Have you tried it with and without the namespace?

  6. HI Thanks for ur response, appreciate it.

    it is possible to send me the code as i m new to visualforce.

    Thanks!

    1. I’m not really sure what code you want. You can use the code in the post with some slight modifications. If you want more, we can discuss me doing some contract work for you. I’ll send an email to you.

  7. Thanks for sharing this! I’ve been able to create a pie chart for my custom objects using your example and Google’s Visualization Playground. The one problem I’ve run into is that it displays fine in Chrome, but not in Firefox. I installed Firebug based on your previous comments and the error I’m seeing is this “ReferenceError: google is not defined” and it appears to be directed at the following line:

    google.load(“visualization”, “1”, {packages:[“corechart”]});

    I’m a novice at using Firebug, but wondering if this gives you any idea as to what is going on that causes the problem in Firefox but not Chrome.

    1. You can’t use the Google Visualization API or the Visualforce charting components in a PDF. The reason is that they both use JavaScript to create the charts and the PDF engine doesn’t run any JavaScript on the page. I have solved this need by using Conga Composer – creating an Excel template and then converting that to PDF.

    1. Conga Composer is an AppExchange App that you can purchase. It allows you to merge to Word, Excel, PowerPoint and PDF. You would not use Google Visualization with it. You’d instead use the built in Excel charting to create your charts. It can be rather difficult to get it right, though. Go look at their website to learn more about this tool: http://www.congamerge.com.

  8. Hi, thanks so much for sharing, I did try your example over the standard Account object and it works as expected. I’ve then tried to have it working over a custom object amending the reference
    and relative fields {!CustomObj__c.MD_API__c} and failing miserably. no graph is generated, just a blank page. Any suggestion ?
    thank,
    Alex

    1. It is hard to say why it doesn’t work. Can you post your code over on salesforce.stackexchange.com? If we can see all of your code, it will be a lot easier to troubleshoot.

  9. Hi,

    Thanks for the good idea. But the code doesn’t work with my settings. I want to add the values hard and I changed the code as below.
    But If I open the visualforce, nothing ist coming up.

    //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:100, f: formatCurrencyLabel(100)}]);
    data.addRow([‘2009’, {v:125, f: formatCurrencyLabel(125)}]);
    data.addRow([‘2010’, {v:98, f: formatCurrencyLabel(98)}]);
    data.addRow([‘2011’, {v:45, f: formatCurrencyLabel(45)}]);

    //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}});
    };

    Thanks,
    Sascha

Leave a Reply