1

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, "', '") . "' )";
}
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.

  • Do you know, is there a way to remove duplicates? I have a report now that shows those who registered in 15/16 and not 17, but it is showing anyone who registered in both 15 and 16 twice – Kimberlie Davis Jul 13 '17 at 22:30
0

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.

  • I went with the other posted solution before I saw your comment, but I will have to check that out for the future. Thanks! – Kimberlie Davis Jul 12 '17 at 20:31
  • Do you know, is there a way to remove duplicates? I have a report now that shows those who registered in 15/16 and not 17, but it is showing anyone who registered in both 15 and 16 twice. – Kimberlie Davis Jul 12 '17 at 20:39
  • Not sure if you are asking this having set up Summary Fields or not. – petednz - fuzion Jul 13 '17 at 0:29
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.