AnsweredAssumed Answered

How to use Kreports vardefs to filter on related activity counts and dates?

Question asked by Michael Michael on Nov 17, 2014
Latest reply on May 5, 2015 by Ashish Rao
For some time I wanted to create a report that shows leads that have not received a call or an email in the last N weeks, or have too few related activities, and have no calls set in the future.

This would help my sales team "mine for gold" among thousands of leads that may have been forgotten or have "slipped through the cracks." 

This is how I got it working with K-reporter 3.0 in SugarCRM CE 6.5.16

For cross module reporting, I use Kreports.  But even Kreporter needed some customization to be able to do this. First, take a moment to read the documentation which I found to be essential, mostly accurate, but not thorough enough. 

The biggest reporting challenge was to be able to filter (select) based on the *most recent* activity date.  Kreporter doesn't allow this type of data by default.  There are two ways to bring in data that requires a custom query in Kreports.

The first is creation of Custom Functions which brings data derived from SQL logic right into the display of your report.  Very cool.  But we cannot filter based on this data.  

The second is to create a custom VarDef.  This requires writing some PHP on the server that results in a new field that appears in the Kreporter interface along with the other module fields.   This new can  be evaluated in the "Select" tab for filtering reports. 

Here is how I created a very useful custom vardefs for Leads in SugarCRM CE 6.5.16 using Kreports 3.0.

In the directory custom/Extension/modules/Leads/Ext/Vardefs I created four files.  

Leads.kreporter.related_count_call.php    
 // This will create a field in Kreports that shows the count of held calls related to a lead
 $dictionary['Lead']['fields']['related_count_call'] = array(
 'name' => 'related_count_call',
 'vname' => 'LBL_RELATED_COUNT_CALL',
 'type' => 'kreporter',
 'source' => 'non-db',
 'kreporttype' => 'int',
 'eval' => '
SELECT  COUNT(calls.date_start)
FROM calls,   calls_leads
WHERE calls_leads.lead_id = {t}.id
AND calls.id = calls_leads.call_id
and calls.status = "Held"
AND  calls.deleted <> 1
'
);
 ?>
Leads.kreporter.related_recent_call.php
// This will create a field in Kreports that shows the date of the most recently-held call related to a Lead
 $dictionary['Lead']['fields']['related_recent_call'] = array(
 'name' => 'related_recent_call',
 'vname' => 'LBL_RELATED_RECENT_CALL',
 'type' => 'kreporter',
 'source' => 'non-db',
 'kreporttype' => 'date',
 'eval' => '
SELECT  MAX(calls.date_start)
FROM calls,   calls_leads
WHERE calls_leads.lead_id = {t}.id
AND calls.id = calls_leads.call_id
and calls.status = "Held"
'
);
 ?>
Leads.kreporter.related_count_email.php
// This will create a field in Kreports that shows the count of sent emails related to a lead
$dictionary['Lead']['fields']['related_count_email'] = array(
 'name' => 'related_count_email',
 'vname' => 'LBL_RELATED_COUNT_EMAIL',
 'type' => 'kreporter',
 'source' => 'non-db',
 'kreporttype' => 'int',
 'eval' => '
SELECT  COUNT(emails.date_sent)
FROM emails
WHERE emails.parent_id = {t}.id
AND  emails.parent_type = "Leads"
AND  emails.'status' = "sent"
AND  emails.'deleted' <> 1
'
);
 ?>
Leads.kreporter.related_recent_email.php
 // This will create a field in Kreports that shows the date of the most recently sent email related to a Lead
 $dictionary['Lead']['fields']['related_recent_email'] = array(
 'name' => 'related_recent_email',
 'vname' => 'LBL_RELATED_RECENT_EMAIL',
 'type' => 'kreporter',
 'source' => 'non-db',
 'kreporttype' => 'date',
 'eval' => '
SELECT  MAX(emails.date_sent)
FROM emails
WHERE emails.parent_id = {t}.id
AND  emails.parent_type = "Leads"
AND  emails.'status' = "sent"
'
);
 ?>
Then, in custom/Extension/modules/Leads/Ext/Language
 I created en_us.kreporter.leads.lang.php
$mod_strings['LBL_RELATED_RECENT_CALL'] = 'related recent call date';
$mod_strings['LBL_RELATED_RECENT_EMAIL'] = 'related recent email date';
$mod_strings['LBL_RELATED_COUNT_EMAIL'] = 'count related emails sent';
$mod_strings['LBL_RELATED_COUNT_CALL'] = 'count related calls held';
?>

Lastly, I performed a quick repair and rebuild in SugarCRM Admin UI and the fields now show up in Kreporter (both in the Manipulate AND the Select Tab).. 

Now, these three reports are possible in our CRM using these new Kreporter fields: 
  1. Which leads are scored at four+ stars with less than six call (or email) attempts and no recent rep activity (rep-sent email or rep logged calls)  in the last two weeks + no calls scheduled in the next four weeks? 
  2. Which leads have no calls, emails, or change of status within the prior four weeks and also do not have a call scheduled in the next 25 weeks, and are not of status "Never Contact", "Moved to Opportunity" or "For Marketing Love"?
  3. Which leads have a high company employee count or  a large number of potential participants and have less than six call (or email) attempts and no recent rep activity (rep sent email or rep logged calls) in the last two weeks + no calls scheduled in the next four weeks?
I hope this helps folks understand a bit more of what it takes to get Vardefs built and the power they open up in Kreports.  

-Michael 

Outcomes