In my last post, A Quick Look at Visualforce Charting, I gave you a look at an interesting chart, but I cheated by using a stub controller to provide hard-coded data. Today I’m going to fill in that controller, and show a more realistic method for assembling the data to feed to the chart itself. This article is about the Apex behind the chart, rather than any new charting features. I hope you’ll find some useful techniques, for charting or any Visualforce page.

Note: Sandeep Bahnot recently posted an article with some code from the Winter ’12 webinar, Visualforce Charting in Winter ’12. We’re covering similar topics, so check it out for some interesting differences in our code.

Note: Visualforce charting components are currently available through a pilot program. For information on enabling this feature for your organization, contact salesforce.com. At this time it is not recommended that this feature be used in production code or managed packages.

As a quick reminder, here’s the chart (the markup that produces it is available in the prior article):

This chart is intended to tell a story, about how opportunities closed affect revenue. There are three data series that contribute to that story: the number of opportunities closed won per month, the number closed lost, and monthly revenue.

Getting two of these three data series is fairly easy. Here’s a new controller that retrieves the Closed Won opportunities and revenue per month:

Very simple. Instead of hard-coding the data, I’m using a SOQL query to retrieve the results from the database. Notice that the controller is using SOQL aggregation to do most of the work in the database, instead of writing my own Apex code to calculate averages per month, etc. This is both more efficient, and less likely to run into governor limits.

The SOQL query gives meaningful names to our calculated attributes, for example, “monthlyRevenue” instead of “data1”, so I revised the chart markup. I’ve removed (for now) the closed lost data series, and simplified a few other things:

This produces the following chart:

There are some obvious problems with this version, including:

• Most importantly, I’m missing the Closed Lost opportunities.
• What’s up with hard-coding the year?
• Numeric months are not the most intuitive way to label the chart, but that’s what CALENDAR_MONTH(CloseDate) is returning.
• What do I do about months that don’t have closed opportunities? Sure, December’s a way’s off, but what if I had a really bad July? They just get skipped over in this chart, because there are no AggregateResults for those months.

There’s no obvious way to get both “Closed Won” and “Closed Lost” opportunities in a single SOQL query, since they are mutually exclusive. This means I’m going to have to run two queries, and write some Apex to knit the two result sets into a single list for the charting components. Here’s the revised controller:

Whoa, that is a lot more code! Before I explain what’s going on, let’s look at the results. You can imagine what the revised chart markup looks like, I merely added back the <apex:lineSeries> for the Closed Lost opportunities, and tweaked the months axis label to be dynamic. Here’s the resulting chart:

Cool. All of the critical chart features are back in place, and I’ve also added the ability to change the year summarized, just by adding a “?y=2010” parameter to the URL. So, what is going on here, and why do I need so much more code just to add month labels and the last data series? Let’s look at the different pieces of the controller; when you take it in smaller parts, each of them is pretty easy to understand.

I’ll start with something familiar. Lines 114-130 at the end of the controller class are a “new” inner class, which is a modest tweak of the inner class I used in the prior article. The changes are to make the property names compatible with the List<AggregateResult> version of the chart I started with, and overloading the constructor so I can instantiate new objects with just the month name. I’ll come back to that later. By making the property names of OppsClosedData the same as the aliases I’m using in the SOQL, I can go back and forth between using methods that return List<OppsClosedData> or List<AggregateResult> with no changes to my chart markup.

Lines 46-77 are two new properties that represent the summary statistic records that are retrieved using SOQL queries. By moving them out of the main method, I can re-use them in other data retrieval methods, or even potentially access them directly in my markup. Extracting the SOQL for the Closed Won opportunities to a property is just a simple refactoring. Adding a second property for the Closed Lost opportunities is practically a copy-and-paste exercise. (I ought to DRY this up a bit, but two separate properties is easier to understand here. Yes, that’s the excuse I’m going with.)

There’s one interesting addition to the SOQL, using :theYear as a parameter for selecting the year to calculate stats for. This bind expression allows you to use Apex variables, methods, or, in this case, an Apex property in the SOQL, without having to piece together a query string. The property itself is defined on lines 79-100, and for all of those lines, it’s simply extracting a year parameter from the URL, if there is one. Lines 101-104 make this value available as a string, for use in the months axis label.

Lines 106-112 are simple date utility methods. If you have localization requirements, you might want to do something a little more sophisticated here.

And so, all that’s left to explain is the new getData() method, lines 3-42. At a high level the code is pretty simple:

For each month of the year:

1. If there is Closed Won data for that month, put it in a new ClosedOppsData object.
2. If there is Closed Lost data for that month, put it in a (maybe new) ClosedOppsData object.
3. If there’s data in the ClosedOppsData object, add it to the overall results list.

Why does this look complicated? Why can’t I just use a simple for loop to go through the 12 months of the year to combine my two sets of results?

While any sales organization can expect to have won and lost opportunities in every month, what if you are charting something—service outages, customer defections, alien abductions—which you might reasonably expect and even hope will measure zero in some months? Those months won’t have an item in the AggregateResult list, and that will lead to mis-synced results or, more likely, runtime errors. So the code tracks the current position in each of the results lists independently, and will skip months that have no results at all. (This won’t always be what you want, but you should be able to alter the code to suit your needs easily enough.)

And, well, there you have it. Instead of hard-coded fake data, this solution shows you how to assemble your own live data for use in a chart, or a table, or whatever. Whether you want to work with standard or custom objects, the techniques shown here should let you figure out a way to query the data, package it, and send to your Visualforce markup to be displayed. Happy charting!

[…]

Still here…? It’s bugging you, isn’t it. Two queries, that complicated loop. There has to be a simpler or more efficient method to get charting data. Well, there is, but I’m going to save that for my next article. 😉 Post your optimizations, bug fixes, and ideas for improvement in the comments, and I’ll see you in a week after the Thanksgiving holiday!