Pages

Tuesday, October 28, 2008

How to Easily Bring Graphical Charts into your CRM?

There is no doubt that nothing communicates better and faster than graphics with users. It has been always the case that users fail to utilize software solutions merely because the information is not delivered to them intuitively, comprehensively and graphically.

Managers often require summarized business critical information quick and easy to grasp. Utilizing charts in Web Applications has been a challenge for many of us over the past two decade and now it is all much easier than ever!

Google Chart is an excellent tool available for free! It is actually very easy to work with and practical.

In this article I will demonstrate how you can utilize this outstanding component to bring new life to your Salesforce pages!

Example: I have a requirement to create a Visualforce page to view a pie chart that shows number of Accounts in our organization divided by their Types.


Here is how it works:
The Google Chart API requires me to pass the chart information via URL parameters and then in return Google will send me an image in PNG format. Then all I need to do is to view the image using the HTML img tag!

Ingredients:
Chart Server URL: http://chart.apis.google.com/chart
Parameters List:
  • chs: is in fact the chart size in pixels ex: 300x200
  • chd: you will need to pass the chart data using this parameter. ex: t:60,40
  • cht: chart type, ex: p3
  • chl: chart items' label, ex: Data 1|Data 2
The above are all basic required parameters in order to create charts. So there is more to it if you are interested.

So in order to utilize this component I create a Visualforce page as follows:



<apex:page controller="googleChartCon" tabStyle="Account">
<apex:sectionHeader title="Accounts by Type"></apex:sectionHeader>
<apex:image url="{!chartData}"></apex:image>
</apex:page>


This page only contains an apex:image component which talks to the controller to obtain it's image URL.

The URL is where the magic happens so we will need to focus on that.

In order to do this I first need to get a list of Account's "Type" picklist values and then
in my custom controller, I create an internal Apex class to save the chart's data while I am running through Account's records and find out how many of which type we have in our database.




public class googleChartCon {
private String chartData;

public String getChartData()inter
{
return chartData;
}

public googleChartCon()
{
//obtain a list of picklist values
Schema.DescribeFieldResult F = Account.Type.getDescribe();
List<Schema.PicklistEntry> P = F.getPicklistValues();
//where chart data should be stored.
List<ChartDataItem> items = new List<ChartDataItem>();

//iterate through each picklist value and get number of accounts
// I wish we could do GROUP BY in SOQL!
for(Schema.PicklistEntry pValue : P)
{
integer Count = [select count() from Account where Type = :pValue.getValue() limit 10000];
if (Count > 0)
items.add(new ChartDataItem(pValue.getValue()+ '-['+ Count.format() + ']' , Count.format()));
}

//Prepare the chart URL
String chartPath = 'http://chart.apis.google.com/chart?chs=600x200&cht=p3';
chartData = chartPath + getChartData(items);
}

private String getChartData(List<ChartDataItem> items)
{
String chd = ''; //23,34,56
String chl = ''; //Hello|World

for(ChartDataItem citem : items)
{
chd += citem.ItemValue + ',';
chl += citem.Label + '|';
}
//remove the last comma or pipe
chd = chd.substring(0, chd.length() -1);
chl = chl.substring(0, chl.length() -1);

String result = '&chd=t:' + chd + '&chl=' + chl;
return result;
}

public class ChartDataItem
{
public String ItemValue
{
get;
set;
}

public String Label
{
get;
set;
}

public ChartDataItem(String Label, String Value)
{
this.Label = Label;
this.ItemValue = Value;
}


}

}


For some security might be a concern sending the data via URL parameters.
Up to this point, I have not been able to confirm if Google is officially supporting SSL connections to rectify this problem. However the following seems to be fine:

https://www.google.com/chart?cht=p3&chd=t:60,40&chs=250x100&chl=Hello|World


In this article I have barely scratched the surface of Google Charts API, if you are interested to learn more about it go to http://code.google.com/apis/chart/

13 comments:

  1. The IE security popup is showing up when using the api url, but when using the google SSl, the image does not show up. Do you know why?

    ReplyDelete
  2. I experienced this as well, it seems that while browsing Salesforce (which is secured itself) the Google SSL url is not working.

    To get rid of IE warning message you will need to add Salesforce to your "trusted sites" and then on custom level of "Trusted Sites" enable "mixed Mode Content".

    Firefox does not have this problem at all.

    ReplyDelete
  3. This is great! We have been looking to do some similar things ourselves. Will this also work for bringing auto-updated data from Google spreadsheets that are using the ImportHTML formula? We have considered creating hyperlink fields that auto-update with market index returns that update automatically.

    ReplyDelete
  4. Hi Sam, this is Mina with Ida Apex. Please checkout the way we use Google visualization tools in our AppExchange for salesforce:

    Ida Apps for Google Alerts, News & Blogs Feed
    http://www.salesforce.com/appexchange/detail_overview.jsp?id=a0330000006aPHMAA2

    Mina
    mina@idaapex.com

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. Hi i work on a content management system for my organization. I am using the https: link in a "img" tag. This is throwing a security pop up as you noted. I do not have administrative controls, how can I fix this pop up for the viewers of my page?

    ReplyDelete
  7. Hi Sam

    How easy would it be to implement something like this in Pro edition?

    ReplyDelete
  8. We are using this for a lot of our projects at https://sites.google.com/site/crmconsultus/

    Thanks, brilliant.

    ReplyDelete
  9. how can i show this chart with percentage rather than number of records

    ReplyDelete
  10. Does this appl allow you to change the color of the charts?

    ReplyDelete
  11. Could you please also give us an example with multi picklist data type.

    ReplyDelete
  12. Kindly provide us with an example for an object with a text field but grouped (like age group) to show in bar chart.

    ReplyDelete
  13. Yeah ! But how can we generate chart without any help of Google 's help.

    ReplyDelete