It’s been a while since I wrote my last tutorial on SugarCRM, and I think it’s time for an update. In my previous set of examples, I discussed connecting/authenticating to the SugarCRM SOAP API, inserting records and relating records programatically. In this edition I’m going to discuss how to query your SugarCRM database to retrieve a count of records. Then I’m going to show you how to present this data with the Google Charts API.

If you’re new to the SugarCRM Community Edition, then you may have noticed that there is very little support built in for reporting. While the CE version is incredibly full in terms of features, it does lack some of the expanded functionality provided by paid version of the software. Have a look at this chart, if you haven’t already. You’ll notice that most of the features you get when you upgrade to a paid version of SugarCRM relate to reporting. This is because a CRM system is great if it simply prevents you from passing around Excel files and Access or FileMaker databases, but it’s almost useless if it still doesn’t give you a high-level view of your data. The goal of this article is to provide some entry-level examples of querying your data so that ultimately you can create your own reports, save thousands of dollars for your project and impress your friends. If you like what you see, and you want to see more, please consider visiting one of our sponsors. Each click makes us a few cents.

Getting Started

First, you’ll need to create your PHP document and add the components mentioned previously to connect to your database via the SOAP API. Again, you’ll find this explained in detail here. In the previous example I integrated my custom PHP with WordPress’ cForms plug in. This time I’m going to forgo the extra step of integration and focus on the queries themselves. So, after you’ve created your PHP document, you should put it somewhere like the htdocs\sugarcrm\custom on your SugarCRM server. Of course, you don’t have to do this. Since you’re connecting via SOAP, you can keep this file on any web server. For reporting purposes though you may want to keep it on your SugarCRM server so that you can link to it and access it easily from within Sugar.

Second, you’ll need to get familiar with the Google Charts API. This API is one of the most amazing utilities to come out of Google. If this is the first time you’ve encountered the API, you’ll want to take a minute to look over the documentation here. Google Charts works by passing in a URL with certain GET variables for the image SRC declaration in your HTML tag. Got it? Good.

The Bad News First

Let me get it out of the way and say that Sugar’s SOAP API is weak. They are purported to have enhanced this functionality in the upcoming version 5.5, which is currently in its beta release. I’ve already mentioned in my previous post that the documentation for such is lacking, too. It’s highly problematic when a blogger such as myself is an authority on something I had no part in writing–I’ve just brute-forced my way into it. So the approaches I am taking now to achieve the level of functionality I need with Sugar are substandard, but I’m hoping that their promises of a better API in version 5.5 will indeed be upheld.

Yann Tiersen on the headphones. Let’s get down to business. If you haven’t seen LornaJane’s PHPDocumentor dump, go ahead and check that out here. It will at least tell you the components that make up the many SugarCRM SOAP calls. But not much more than that. Speaking of time, though, you’ll need to add the following two lines of code to the top of your php page so that your queries don’t eat up all the memory and your page doesn’t time out while loading.

ini_set(“memory_limit”,”80M”);  // fix for soap
set_time_limit(120);  // another fix

The get_entries_count() Call

The get_entries_count() call (thanks Joe!) is a simply query that just counts the number of records specified and returns that integer. This is a very useful query because SugarCRM’s SOAP API is really slow when used to return actual records lists. If you’re just building a report, then you’re probably mostly interested in record counts anyway. This query will run in less than half the time of the get_entry_list() call, which I explain in more detail below.

Let’s start by creating a simple query. Let’s query the Opportunities table and find out how many new records were created in April of 2009. You’ll need LornaJane’s documentation and a little experience with SQL.

$startdate=”2009-04-01″;
$enddate=”2009-04-30″;
$month_response = $client->get_entries_count($session_id,’Opportunities’, ‘date_entered between “‘.$startdate.’” and “‘.$enddate.’”‘, FALSE);
$month_num = $month_response->result_count;

print $month_num;

It’s pretty easy to see what’s happening here. The get_entries_count() function requires the Session ID, declared in your authentication statement, as well as the table you’re querying. Then there’s the SQL query WHERE statement. You’ll inevitably find it annoying that only the WHERE statement goes into the function. This removes a ton of functionality and makes it difficult to retrieve data efficiently. SQL has a COUNT parameter, for instance, and if I could specify more of the SQL text then there wouldn’t be the need for this get_entries_count() function at all. The “FALSE” statement at the end tells Sugar not to include deleted records. If no records were entered in the Opportunities table in April, then this will output 0. Otherwise you should see an integer.

Now let’s make it fun. Say you had several different sales stages specified in your Opportunities table. In our company’s Sugar implementation, we have five sales stages: Active, Dormant, Closed, Closed Won, and Closed Lost. Let’s say that we want to make a pie chart of this using the Google Charts API. We’ll need to make several different calls to count the different sales stages, then we’ll need to perform some basic mathematical operations to get some percentages.

// get data for the OUTCOME pie chart
$active_response = $client->get_entries_count($session_id,’Opportunities’, ‘sales_stage like “Active”‘, FALSE);
$active_num = $active_response->result_count;
$dormant_response = $client->get_entries_count($session_id,’Opportunities’, ‘sales_stage like “Dormant”‘, FALSE);
$dormant_num = $dormant_response->result_count;
$closed_response = $client->get_entries_count($session_id,’Opportunities’, ‘sales_stage like “Closed”‘, FALSE);
$closed_num = $closed_response->result_count;
$closedwon_response = $client->get_entries_count($session_id,’Opportunities’, ‘sales_stage like “Closed Won”‘, FALSE);
$closedwon_num = $closedwon_response->result_count;
$closedlost_response = $client->get_entries_count($session_id,’Opportunities’, ‘sales_stage like “Closed Lost”‘, FALSE);
$closedlost_num = $closedlost_response->result_count;

// do some math
$active_percent=round($active_num/$total_num*100);
$dormant_percent=round($dormant_num/$total_num*100);
$closed_percent=round($closed_num/$total_num*100);
$closedwon_percent=round($closedwon_num/$total_num*100);
$closedlost_percent=round($closedlost_num/$total_num*100);

The first thing you should notice is that this is highly inefficient. In normal SQL land we would have used the COUNT and GROUP BY calls to get this information in a few lines. There should be a better way to do this, and I’m convinced there is–I just haven’t been able to find it yet because Sugar’s documentation and API are just so thin. Imagine, for instance, if I wanted to return the total number of Opportunities for each country. It would take hours to list all of the necessary queries to do that, and it would take minutes to run.

Now, to display this data in a pretty Google Chart, just add the following bits.

print ‘<img src=”http://chart.apis.google.com/chart?chs=325×165&chts=333333,12&chco=5DA4A4,cccccc,666666,65C665,C36464&chtt=’.$total_num.’ Opportunities as of ‘.$today.’|&chd=t:’.$active_percent.’,’.$dormant_percent.’,’.$closed_percent.’,’.$closedwon_percent.’,’.$closedlost_percent.’&cht=p&amp;chl=Active ‘.$active_num.’ (‘.$active_percent.’%)|Dormant ‘.$dormant_num.’ (‘.$dormant_percent.’%)|Closed ‘.$closed_num.’ (‘.$closed_percent.’%)|Won ‘.$closedwon_num.’ (‘.$closedwon_percent.’%)|Lost ‘.$closedlost_num.’ (‘.$closedlost_percent.’%)” alt=”Opportunity Outcomes” />’;

You should see this, but with your own numbers, of course: