Answers.Body: <p>I have accomplished similar results to what you are looking for by doing the following.</p>
  
  <ul>
  <li>I created a smart group for all those who have registered for the current event (2017)</li>
  <li>I then used the participant report filters to include all registrants from the previous events (2015 &amp; 2016)</li>
  <li>I then added a filter to exclude any members of the smart group for the current event.</li>
  </ul>
  
  <p>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.</p>
  
  <p>Hope this helps.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-13T22:30:16.770Z
Answers.Text: 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
Answers.UserDisplayName: Kimberlie Davis
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-12T15:22:11.783Z
Answers.LastActivityDate: 2017-07-12T15:22:11.783Z
Answers.UserDisplayName: KilakwaBT
Answers.Body: <p>Another approach would be to use the excellent <a href="https://civicrm.org/extensions/summary-fields" rel="nofollow noreferrer">Summary Fields extension</a> 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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-12T20:31:48.823Z
Answers.Text: I went with the other posted solution before I saw your comment, but I will have to check that out for the future.  Thanks!
Answers.UserDisplayName: Kimberlie Davis
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-12T20:39:00.960Z
Answers.Text: 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.
Answers.UserDisplayName: Kimberlie Davis
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-13T00:29:38.283Z
Answers.Text: Not sure if you are asking this having set up Summary Fields or not.
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-12T20:20:03.130Z
Answers.LastActivityDate: 2017-07-12T20:20:03.130Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>An alternative solution might be to use Drupal Views instead of Report : Views provides easy filtering features.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-07-27T20:57:29.600Z
Answers.LastActivityDate: 2017-07-27T20:57:29.600Z
Answers.UserDisplayName: Jacques-Marie Moranne
Body: <p>I am using Drupal 7 and CiviCRM 4.6.28.</p>
  
  <p>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.</p>
  
  <p>I am running this query in the where() function in the report php file.</p>
  
  <p>Here are the contents of the where() function:</p>
  
  <pre><code>$sql = "SELECT {$this-&gt;_aliases['civicrm_participant']}.contact_id, 
  {$this-&gt;_aliases['civicrm_participant']}.register_date 
  FROM civicrm_participant {$this-&gt;_aliases['civicrm_participant']}";
  
  $dao = CRM_Core_DAO::executeQuery($sql);
  $i = 0;
  $registered = array();
  
  while($dao-&gt;fetch()) {
      if (strpos($dao-&gt;civicrm_participant_register_date, '2017') !== false ) 
      {
          $registered[$i] = $dao-&gt;civicrm_participant_contact_id;
          $i++;
      }
   }
  
  $this-&gt;_where = "WHERE ({$this-&gt;_aliases['civicrm_event']}.title = 'SF Open 
  Studios 2017' 
  OR {$this-&gt;_aliases['civicrm_event']}.title = 'SF Open Studios 2016' 
  OR {$this-&gt;_aliases['civicrm_event']}.title = 'SF Open Studios 2015') 
  AND {$this-&gt;_aliases['civicrm_participant']}.contact_id 
  NOT IN ( '" . implode($registered, "', '") . "' )";
  }
  </code></pre>
  
Tags: <php><reports><custom-reports>
Title: Issue with custom report that shows all users registered for event in last 2 years but not this year
ThreadText: # Issue with custom report that shows all users registered for event in last 2 years but not this year
  
  (Asked by: username_0 on 2017-07-12)
  
  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, "', '") . "' )";
  }
  
  ```
  
  **Tags:** php, reports, custom-reports
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_1 on 2017-07-12. Score: 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.
  
  > **Comments:**
  > **username_0**: 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  
  
  ---
  
  ### Answer by username_2 on 2017-07-12. Score: 0
  
  Another approach would be to use the excellent [Summary Fields extension](https://civicrm.org/extensions/summary-fields) 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.
  
  > **Comments:**
  > **username_0**: I went with the other posted solution before I saw your comment, but I will have to check that out for the future.  Thanks!  
  > **username_0**: 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.  
  > **username_2**: Not sure if you are asking this having set up Summary Fields or not.  
  
  ---
  
  ### Answer by username_3 on 2017-07-27. Score: 0
  
  An alternative solution might be to use Drupal Views instead of Report : Views provides easy filtering features.
  
  
  ---
  
  