Campaign Member Summary using Google Charts

I was inspired by Sam Arjmandi’s post about embedding Google Charts into VisualForce pages.  I have a use case that requires a little different approach.  I needed to get a quick view of the Campaign Member Statuses for a Campaign.  I went ahead and started with Sam’s example and tweaked it for my purpose.  Here’s the result!

It’s an embedded VisualForce component in the Results section of my Campaign page.  It shows a quick count for each Member Status that is being used.  To get it going in your org, here’s what you need.

VisualForce Page

It’s a simple page containing 1 DIV so that I can set the background color to match that of a Page Layout.  Other than that it’s just an image returned from Google Charts.  There is a lot of flexibility with Google Charts.  Therefore, I made it so most of the URL can be tweaked in VisualForce. Only the data values and its labels come from the controller.  This is nice because you can edit VF in a production org, but you can’t edit Apex. This will let you change things like width, height, chart colors, chart type, etc.


<apex:page standardController="Campaign" extensions="VFController_CampaignMemberStatusChart">
    <style>
        #DIV_Container{padding: 0; margin: 0; background-color: #F3F3EC;}
    </style>
    <div id="DIV_Container">
        <!-- See http://code.google.com/apis/chart/ for more info on customizing the chart -->
        <apex:image url="http://chart.apis.google.com/chart?cht=p3&chs=1000x125&chf=bg,s,F3F3EC&chco=CC9933{!chartData}"></apex:image>
    </div>
</apex:page>

Apex Controller
The controller is an extension of the Campaign standard controller. This is so the VisualForce page becomes an option to include on a Campaign Page Layout. The method that does all the work is getChartData. It gets the available Campaign Member Statuses and then queries the Campaign Member object for that Campaign and adds a data/label value for each one. It then returns a query string that you include into the Image src on the Visual Force page.

I am bad at Test classes, but there is 1 in there that passes and should be good enough. This functionality is pretty harmless.

public class VFController_CampaignMemberStatusChart {

	private final Campaign camp;

	public VFController_CampaignMemberStatusChart(ApexPages.StandardController stdController) {
		this.camp = (Campaign)stdController.getRecord();
	}
	
	public String getChartData() {
		
		// The list of chart items	
		List<ChartDataItem> items = new List<ChartDataItem>();
		
		// List of valid Campaign Member Statuses for the Campaign
		List<CampaignMemberStatus> list_cms = [select Id, Label from CampaignMemberStatus where CampaignId = :camp.id];
		
		// Loop through each Campaign Member Status, get a count of Campaign Members and add it to the items list 
		for (CampaignMemberStatus cms:list_cms) {
			integer Count = [select count() from CampaignMember where CampaignId = :camp.id AND Status = :cms.Label];
			if (Count > 0) {
				items.add(new ChartDataItem(cms.Label, Count.format()));
			}
		}
		
		// Initialize Strings
		String chd = ''; // Data
		String chl = ''; // Labels
	
		for(ChartDataItem citem : items) {
			chd += citem.ItemValue + ',';
			chl += citem.Label + ' (' + citem.ItemValue + ')|';
		}
		
		//remove the last comma or pipe
		if (items.size() > 0) {
			chd = chd.substring(0, chd.length() -1);
			chl = chl.substring(0, chl.length() -1);
		}
		
		// We are only returning the values and labels. The rest of the URL string is in the VF page
		String result = '&chd=t:' + chd + '&chl=' + chl; // &chl returns with labels pointing to pie pieces
		//String result = '&chd=t:' + chd + '&chdl=' + chl; // &chdl returns with labels in a legend
		
		return result;
	}

	// Class holding each chart data item
	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;
		}
	}
	
	static testMethod void testVFController_Sidebar_Summary() {
		
		// Create Campaign
        Campaign c = new Campaign();
        c.Name = 'Test Campaign';
        insert c;
        
        // Create Lead
        Lead l = new Lead();
        l.LastName = 'Last Name';
        l.Company = 'Company';
        insert l;
        
        // Create Campaign Member
        CampaignMember cms = new CampaignMember();
        cms.CampaignId = c.id;
        cms.LeadId = l.id;
        insert cms;
		
		test.startTest();
		
		ApexPages.StandardController sc = new ApexPages.StandardController(c);
		VFController_CampaignMemberStatusChart controller = new VFController_CampaignMemberStatusChart(sc);
		String s1 = controller.getChartData();
		
		test.stopTest();
	}

}

Page Layout
When you add it to the Page Layout, make the height of the component the same as the height you specified in the VF page’s image src for Google Charts. In this example, it’s 125. Doing this will ensure the background colors match your Page Layout.

7 Comments

  1. JP Seabury Said,

    October 31, 2008 @ 1:18 pm

    Very cool!

  2. Chris Raskulinecz Said,

    November 3, 2008 @ 4:54 pm

    This looks very cool. One note, I was testing it on a campaign that had over 12,000 members and it gave a System.Exception: Too many query rows:10001 error.

    Worked great on smaller campaigns though.

  3. Brett Kahnke Said,

    February 9, 2010 @ 3:33 pm

    Wow, very nice. It looks like you’ll only hit the 10000 limit if any one Status value has over 10,000. Is that right? If so, you can probably just add “limit 10000” to the query and inform users that there is a cap for any one value. Probably only affects the initial Sent (or equivalent) status.

  4. Scott Hemmeter Said,

    February 9, 2010 @ 3:39 pm

    @Brett, i should write an updated post soon. With Spring 10, a lot of this can be replaced with an aggregate SOQL query (I think) and do one query that only pulls back summary information.

  5. Using Aggregate Functions | Perspectives on Salesforce.com Said,

    February 10, 2010 @ 10:51 am

    […] it is to put it into practice.  To do so, I am going to “upgrade” the code from my Campaign Member Summary using Google Charts post.  The goal is to create a chart that looks like […]

  6. Monday AM Admin- Guest Post: Everybody Loves Eye Candy « sales & marketing ideas blog Said,

    October 25, 2010 @ 8:04 am

    […] followed the instructions in the tutorial here to add this to my Campaign page […]

  7. Monday AM Admin- Guest Post: Everybody Loves Eye Candy « Said,

    October 29, 2010 @ 9:12 pm

    […] followed the instructions in the tutorial here to add this to my Campaign page […]

RSS feed for comments on this post