In my last post I described a technique for merging the results of multiple summary SOQL queries into a single data structure to hold all of the results. Once the results are merged, you can use them for Visualforce charting (which requires this format), reporting, and so on.

But the code used multiple SOQL queries, and the Apex loop to merge multiple results was fairly complicated, and would be even more so with additional queries to merge in. It left a bit of a “there must be an easier way” taste in my mouth, and maybe yours too.

That easier way is to take advantage of more than just the programmatic side of the Force.com platform, that is, more than just SOQL and Apex. One of the advantages of developing on the Force.com platform is all the additional development capabilities it provides that do not require programming. Learn to use both, together, and you will really take off.

Back to my controller code. My goal here is to “clean up” the data access methods to eliminate the extra SOQL query, and avoid some repetitive logic. The trick to doing so is to use one tiny feature from the declarative side, calculation fields.

Here’s one of the old SOQL queries:

Here’s the improved version of the query, which actually combines both of the old queries:

What’s the trick to the new query? It’s a couple of very simple calculation fields that effectively pull one of the WHERE conditions out of the SOQL and onto each record. Notice that the new WHERE clause requires only that an opportunity be closed (IsClosed = TRUE), not closed a specific way. This lets me combine the two SOQL queries into one. The calculation fields keep track of which opportunities are Closed Won vs. Closed Lost. Here’s Closed_Won__c:

The other two calculations are also simple, so I’ll just show their formulas:

• Closed Lost: IF(ISPICKVAL(StageName,"Closed Lost"), 1, 0)
• Revenue Won: IF(ISPICKVAL(StageName,"Closed Won"), Amount, 0)

Each calculation is either zero if the record is not the right stage, or a summable number if it is. With these simple formulas defined, I can just sum the values to get counts and revenue totals. The query is simpler to understand, and it may be more efficient for the database.

With this new query I can simplify the controller code considerably, eliminating the other query, simplifying the loop, and eliminating a third of the code overall. Here’s the revised controller:

The Visualforce markup for the chart remains the same, and so does the resulting chart; see the prior article if you want a recap. I can switch between the two controllers with no other change than to my <apex:page> tag.

This technique is not without some tradeoffs:

• Most obviously, you have to be able to create custom fields on the objects you want to query against. If you don’t have permission to do so, you will need assistance from your Salesforce administrator.
• While the controller is simpler, I’ve moved some of the logic out of the controller, into multiple, separate calculation field definitions. So the “code” is now spread across both declarative and programmatic parts of the organization. That may actually be more complicated.

I think the complexity of individual calculation fields can be managed, through naming conventions and good descriptions for the calculation fields. The upside is being able to take direct advantage of the declarative side of the Force.com platform from your code. Worth considering, at the very least!