Issue with custom report that shows all users registered for event in last 2 years but not this year

Question

I am using Drupal 7 and CiviCRM 4.6.28.

I am trying to create a report that will display all of the users who registered for an event in 2015 and 2016, but not yet in 2017. I have the report working to the point of displaying all who registered in 2015, 16, and 17. I am not sure how to go about removing those who have not registered in 2017. The idea I've been trying is to create an array of contact_ids that are registered in 2017, and then use that array in the WHERE clause, to only select rows that do not contain that contact_id. It is still displaying users who have registered in 2017, though.

I am running this query in the where() function in the report php file.

Here are the contents of the where() function:

$sql = "SELECT {$this->_aliases['civicrm_participant']}.contact_id, 
{$this->_aliases['civicrm_participant']}.register_date 
FROM civicrm_participant {$this->_aliases['civicrm_participant']}";

$dao = CRM_Core_DAO::executeQuery($sql);
$i = 0;
$registered = array();

while($dao->fetch()) {
    if (strpos($dao->civicrm_participant_register_date, '2017') !== false ) 
    {
        $registered[$i] = $dao->civicrm_participant_contact_id;
        $i++;
    }
 }

$this->_where = "WHERE ({$this->_aliases['civicrm_event']}.title = 'SF Open 
Studios 2017' 
OR {$this->_aliases['civicrm_event']}.title = 'SF Open Studios 2016' 
OR {$this->_aliases['civicrm_event']}.title = 'SF Open Studios 2015') 
AND {$this->_aliases['civicrm_participant']}.contact_id 
NOT IN ( '" . implode($registered, "', '") . "' )";
}

Answer 1

I have accomplished similar results to what you are looking for by doing the following.

• I created a smart group for all those who have registered for the current event (2017)
• I then used the participant report filters to include all registrants from the previous events (2015 & 2016)
• I then added a filter to exclude any members of the smart group for the current event.

I do all this using the report filters on the standard participant report but you could probably apply similar filters to a custom report. I also use the current event registrant smart group to exclude folks who have already registered from promotional mailings. It comes in handy.

Hope this helps.

Answer 2

Another approach would be to use the excellent Summary Fields extension which gives you the "Date of the last attended event field", and you should then be able to use relative date filters to get what you required, or an include/exclude group.

Answer 3

An alternative solution might be to use Drupal Views instead of Report : Views provides easy filtering features.