• Page
|
• Edit

Abstract

This tutorial shows how to add charts to your Force.com applications, without writing any code. Instead of coding, we'll show how to create dynamic Google Chart mashups using the built-in formula expression language, which lets you embed images based on external, parameterized URLs.

After following this tutorial you will be in a position to add data visualization to your own applications by simply creating rollup summary fields, creating formula fields, and modifying the page layouts.

Introduction

A formula is similar to an equation that is executed at run time. Depending on the context of the formula, it can make use of various data and operations to perform a calculation. See An Introduction to Formulas for an in-depth overview.

A particular formula function, IMAGE(), is very useful for embedding an image. You can give it a URL, and it will render the image found at that URL. When such a formula is present on a page layout, the resulting URL will be fetched and displayed. That's one half of the solution.

The other half is Google Charts, which provides a way to dynamically render data visualizations specified through a URL. For example, consider the following URL:

http://chart.apis.google.com/chart?cht=p3&chd=t:0.3,0.7&chs=275x100&chf=bg,s,F3F3EC&chl=Won|Lost&chco=5555ff

If you copy and paste that into your browser's location bar, it will render the following graph:

Combining Google Charts and the IMAGE() formula gives you a powerful tool. You'll be able to use the formula language to draw on data in your application that you want to visualize, and construct a URL for the IMAGE() function to display that data with sophisticated charts and graphs. All without writing any code. This tutorial shows you how to do this.

As an added twist, we're also going to show you how to use rollup summary fields to produce more sophisticated data.

Getting Started

This section walks you through creating a pie chart visualization. We start with the scenario, followed by a number of steps that you can reproduce to create a pie chart of your own data.

Scenario

So let's get started. Our goal is to show you how you how to create pages like this one, which illustrates a simple Account detail page with embedded charts:

We're going to show you how to do this - simply because the Developer Edition environment comes with an Account database object out of the box. Log on, go to the Sales application for example, and choose the Accounts tab. If you hit "Go" you'll see a list of Accounts. Selecting an Account will show the detail page for that Account. It's this detail page that we want to change.

What we're going to do is add a pie chart to this Account detail page to show the percentage of deals lost versus the percentage of deals won. After these steps, the rest of the tutorial will examine some of the features in a little more detail.

Step 1: Creating the Data to Visualize

Before we create a visualization, we need to ensure we have the appropriate data to visualize. You can of course visualize any data in your Force.com application. In this case, we want a pie chart with two pieces of data:

• The percentage of deals lost
• The percentage of deals won

A deal "won" is defined as an Opportunity object (a child object of the Account object) that has its Stage field set to "Closed Won". What we're going to do is create a Rollup Summary Field on the Account object that automatically tallies all Opportunity records in this state.

Go to Setup | Customize | Accounts | Fields. To create the rollup summary field, create a new field, select the type of "Roll-Up Summary", give it a label of "Total Deals Won" and name of Won. Select "Opportunities" as the Summarized Object, Sum the Amount field, and add a filter criteria ensuring that the Stage field equals "Closed Won", as depicted in the following diagram.

Now the Account object has an additional field, Won, which is automatically calculated. You need to repeat this exact process, creating a field labeled Total Deals Lost (with name Lost), where the criteria is that the Stage field is set to "Closed Lost".

At this point we've modified our Account database object to include two additional fields - representing the data that we want to visualize.

Step 2: Create a custom formula field to display the chart or graph

Now that we have the data, we simply need to graph it. To do this, we'll create a formula field. Go to Setup | Customize | Accounts | Fields, and create a new field, this time of type "Formula". Call the field "Win Rate", select a return type of "Text", and use the following as the formula:

IMAGE("http://chart.apis.google.com/chart?cht=p3&chd=t:" & 
 Text(Won__c/(Won__c + Lost__c)) & "," & 
 Text(Lost__c/(Won__c + Lost__c)) & 
 "&chs=275x100&chf=bg,s,F3F3EC&chl=Won|Lost&chco=5555ff", 
 "chart text")

Note that the pie chart expects the percent for each category, so in this example, we need to provide the percent on Won and Lost deals, not the total amounts for Won and Lost deals. We can do this calculation directly in the Win Rate formula, as we've done with (Won__c/(Won__c + Lost__c)). Formula references such as Won__c are called "merge fields" – their values are merged into the formula at runtime.

Here's the result:

Summary

That's it! The key is to ensure the object that you wish to graph has the appropriate data, and then to create a Formula field using the IMAGE() function to do the actual graphic, having this field refer to the other fields containing the data to graph.

Roll-Up Summary Field Basics

Roll-Up Summary Fields are a special custom field type that is defined on the master object of a master-detail relationship to store an aggregated value (SUM, MAX, MIN, or COUNT) based on the detail object's records. This is useful for displaying key metrics on detail pages and lists.

Rollup Summary Fields have some limitations:

• They are only supported master-detail relationships, not on lookup relationships.
• They are supported for all custom master-detail relationships, but only for one standard relationship: Account-Opportunity-Opportunity Product.
• The Account-Opportunity relationship does not support Rollup Summary Fields if Advanced Currency Management is enabled in your org.
• You can only create ten Rollup Summary Fields per object, because they impact the performance when your end users save the detail records.

Tips for using Rollup Summary Fields in charts:

• Often you want two Rollup Summary Fields to compare key metrics (deals won vs. deals lost). In the example above, we also needed to know the total deals closed, so that we could show deals won as a percent of the total. You could do this with another Rollup Summary Field, but as you see above, we did this with a custom formula field instead. It is more efficient to do the calculations with a custom formula field, it saves you one Rollup Summary Field, and it improves performance for your end users.
• To display an average in a chart, use two Rollup Summary Fields: one to COUNT the total records and another to SUM the records. Then use a custom formula field to do the math for you.

IMAGE() Formula Function Basics

The IMAGE() formula function is a very cool little function that enables you to enhance the user interface with graphics quickly and easily. See Examples of Advanced Formula Fields in the References for more image examples.

The image function is deceptively simple: just provide the URL of the image, and the text you want displayed on hover:

IMAGE( image_url, alt_text )

The power lies in the Google Charts API – it is doing the work for you of constructing the image for the chart based on the values you provide.

Copy and paste your way to success! When using the IMAGE() function for a pie chart, just follow the pattern from the example above, replacing the merge fields with the merge fields for your application, and replacing the text you want displayed.

For examples on using IMAGE() function with other types of Google Charts APIs, install this unmanaged package of Google Chart API examples from one of our savvy Sales Engineers. It also shows Rollup Summary Fields and custom formula fields as a basis for creating your own.

Tips for using the IMAGE() function for charts:

• In the query string of the URL, you are passing the values you want to display in the chart. Most likely, those values are numbers, not text. But, the URL can only handle text. Use the TEXT() function to convert numeric values to text and the Concatenate operator (&) to piece your text together with the URL string.
• In a detail page, the image will display like a regular field value, with a label to the left of it. You have control of the size of the image via the chart size (&chs) query string parameter in the URL. Consider the placement of the image on your page layouts: is this something that you want to be very prominent or more as a tickler? Are there certain users (User Profiles) or record types that you want this image to be bigger or smaller for? Do you want the image to be bigger if the Account is more important? You can use one formula field with an IF() statement to change the size of the image based on Profile, Record Type, or any criteria. For example:

IF(MySpecialField__c, "A", IMAGE("http://chart.apis.google.com/chart?cht=p3&chd=t:" & Text(Won__c) & "," & Text(Lost__c) & "&chs=275x100&chf=bg,s,F3F3EC&chl=Won|Lost&chco=5555ff", "chart text"), IMAGE("http://chart.apis.google.com/chart?cht=p3&chd=t:" & Text(Won__c) & "," & Text(Lost__c) & "&chs=150x50&chf=bg,s,F3F3EC&chl=Won|Lost&chco=5555ff", "chart text"))

• In a list, the image will display in a column, like a regular field value, taking up the amount of space you specify in the chart size (&chs) query string parameters in the URL. Most likely, the size you want in the detail page is bigger than the size you want when viewing a list of records. If so, create two custom formula fields with different chart sizes: e.g., Win_Rate_Large__c and Win_Rate_Small__c. Put Win_Rate_Large__c in your page layout and Win_Rate_Small__c on your Search Layouts (e.g., Accounts Tab). Train your users to include Win_Rate_Small__c in their personal List Views.

Google Charts API Basics

Here is a quick rundown of the basics of Google Charts:

• Returns a PNG-format image in response to a URL
• Several types of image can be generated, including
  • Line, bar, and pie charts
  • Scatter plot
  • Google-o-meter
  • Map
  • Radar
  • Venn diagram
• For each type you can specify attributes such as size, colors, and labels
• Google Chart API URLs must be in the following format: http://chart.apis.google.com/chart?<parameter 1>&<parameter 2>&<parameter n>

There is excellent help and examples available at the Google Charts API home page.

Summary

In this tutorial we demonstrated a powerful combination of features to enable data visualization utilizing Google Charts. The key to the technique is modifying your data model to expose the data to be charted, and then using the IMAGE() formula function together with selected set of Google Chart URL options to render the desired result.

With this combination you can display sophisticated charts and graphs on your detail pages or lists, improving end-user productivity and making your pages a little bit snazzier!

References

• See An Introduction to Formulas to learn more about formulas on Force.com.
• The Google Chart API has plenty help and examples.
• The Formulas page in the Technical Library points to many additional resources.
• For an alternative approach, see the Google Visualizations project which uses Visualforce Components to embed the visualizations.

About the Authors

• Mary Scotton is the product manager for formulas at salesforce.com. She is passionate about converting both technical developers and non-technical administrators into formula evangelists. She keeps an eye on the Formula Discussion Board (on the CRM Community site) and really appreciates how the community responds to posts and takes such good care of each other.
• Jon Mountjoy is the community manager and editor-in-chief at Developer Force. He gets kicks out of learning new things and communicating these to the community. You can find Jon on the Developer Force blog, Twitter, FriendFeed and more.