Body: <p>We are working in civiCRM 4.6.4 with Drupal 7.50.</p>
  
  <p>Our goal is to have a report template where one can pull up a list of event participants (possibly for multiple events) who used a specific discount code and/or all participants who used a discount code for a specific event(s).</p>
  
  <p>Some of our discount codes (automatic member discount etc) are used over the course of multiple events so the users list available from the civiDiscount admin page doesn't really accomplish this well for us.</p>
  
  <p>A quick google search did not turn up an example report form for me to follow - nor do I see this in the 'extended reports' extension.</p>
  
  <p>I see that the participant table seems to have discount_id and discount_amount columns, but when I inspect those columns in mysql I see all null and when I try a participant get with the API I get weird results when using those as parameters so maybe that won't work?</p>
  
  <p>My next thought was to look at fee_level because participants seem to have the discount codes inserted into the fee level text. I thought I might add an additional participant_fee_level filter and title it 'discount code' with a 'like' operator to allow searching on discount code.</p>
  
  <p>While this functionally works, I am wondering if there is a better solution as this one seems a bit rough. An example would be if someone wants to pull up all discount codes used for an event they need to know to fill the field with the value 'Discount' or '(', which pull up the same list. Also the 'starts with', 'ends with', '=', '!=', 'null', 'not null' options for the 'like' operator would not function as the user would expect.</p>
  
  <p>Any thoughts on whether this is the best solution for now or there is a better (attainable) way to proceed are much appreciated!</p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-05T20:02:16.637Z
Comments.Text: Hey @eli - Did you ever build something like this?  If so, could you let me know either here, or on chat.civicrm.org (I'm "junglebird" there)?  Thanks!
Comments.UserDisplayName: Jon G - Megaphone Tech
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-07T16:02:59.883Z
Comments.Text: For anyone who finds this... I replied to Jon on mattermost because my answer was longer than a comment. TLDR; I never quite got this working better than what is described above before priorities changed at our org.
Comments.UserDisplayName: Eli Lisseck
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-30T16:29:20.120Z
Comments.Text: @jon-g If you're still looking at this, the need has come back at this org and I will be working on this this afternoon / early next week depending on whether it plays nice or not :)
Comments.UserDisplayName: Eli Lisseck
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-06-30T18:13:23.920Z
Comments.Text: Thanks for the heads-up!  I received an inquiry from another Civi shop about this conversation recently, too.
Comments.UserDisplayName: Jon G - Megaphone Tech
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-07-06T20:08:47.153Z
Comments.Text: @jon-g What I ended up with so far is a clone of the "Event Income (detail)" summary report which also gives you stats for discount codes used per event. I packed it up quickly at https://github.com/elisseck/com.elisseck.discountreport so you can check it out. Ideally I would get a clone of "Event Participants List" with discount info going, but that has proven to be much, much more difficult. I don't think I know enough about the fancy 'where()' stuff going on to get it working in a reasonable amount of time right now.
Comments.UserDisplayName: Eli Lisseck
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-07-07T19:16:27.657Z
Comments.Text: Edit - I may have spoken too soon here. This was working on my local installation with a small set of test data... but does not appear to be working properly on a large set of real data.
Comments.UserDisplayName: Eli Lisseck
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-07-07T19:26:49.523Z
Comments.Text: Silly me - used an api call and forgot to turn off the auto-limiter... so was not working with the large dataset. Also a typo :). Should be fixed in the repo linked above.
Comments.UserDisplayName: Eli Lisseck
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-07-31T18:18:16.210Z
Comments.Text: @jon-g Found an issue with the method i've used here. Looks like if a discount code is used, and then the participant record needs to later be deleted, it looks like the cividiscount table entry still persists so there's not currently a mechanism for this report to update to reflect that deletion? More thinking about this to be done. I am moving very slowly on it due to other responsibilities...
Comments.UserDisplayName: Eli Lisseck
Tags: <civireport><cividiscount><custom-reports>
Title: How to create a better report template that includes CiviDiscount codes?
ThreadText: # How to create a better report template that includes CiviDiscount codes?
  
  (Asked by: username_0 on 2016-08-25)
  
  We are working in civiCRM 4.6.4 with Drupal 7.50.
  
  Our goal is to have a report template where one can pull up a list of event participants (possibly for multiple events) who used a specific discount code and/or all participants who used a discount code for a specific event(s).
  
  Some of our discount codes (automatic member discount etc) are used over the course of multiple events so the users list available from the civiDiscount admin page doesn't really accomplish this well for us.
  
  A quick google search did not turn up an example report form for me to follow - nor do I see this in the 'extended reports' extension.
  
  I see that the participant table seems to have discount\_id and discount\_amount columns, but when I inspect those columns in mysql I see all null and when I try a participant get with the API I get weird results when using those as parameters so maybe that won't work?
  
  My next thought was to look at fee\_level because participants seem to have the discount codes inserted into the fee level text. I thought I might add an additional participant\_fee\_level filter and title it 'discount code' with a 'like' operator to allow searching on discount code.
  
  While this functionally works, I am wondering if there is a better solution as this one seems a bit rough. An example would be if someone wants to pull up all discount codes used for an event they need to know to fill the field with the value 'Discount' or '(', which pull up the same list. Also the 'starts with', 'ends with', '=', '!=', 'null', 'not null' options for the 'like' operator would not function as the user would expect.
  
  Any thoughts on whether this is the best solution for now or there is a better (attainable) way to proceed are much appreciated!
  
  **Tags:** civireport, cividiscount, custom-reports
  
  > **Comments:**
  > **username_1**: Hey @eli - Did you ever build something like this?  If so, could you let me know either here, or on chat.civicrm.org (I'm "junglebird" there)?  Thanks!  
  > **username_0**: For anyone who finds this... I replied to Jon on mattermost because my answer was longer than a comment. TLDR; I never quite got this working better than what is described above before priorities changed at our org.  
  > **username_0**: @jon-g If you're still looking at this, the need has come back at this org and I will be working on this this afternoon / early next week depending on whether it plays nice or not :)  
  > **username_1**: Thanks for the heads-up!  I received an inquiry from another Civi shop about this conversation recently, too.  
  > **username_0**: @jon-g What I ended up with so far is a clone of the "Event Income (detail)" summary report which also gives you stats for discount codes used per event. I packed it up quickly at https://github.com/elisseck/com.elisseck.discountreport so you can check it out. Ideally I would get a clone of "Event Participants List" with discount info going, but that has proven to be much, much more difficult. I don't think I know enough about the fancy 'where()' stuff going on to get it working in a reasonable amount of time right now.  
  > **username_0**: Edit - I may have spoken too soon here. This was working on my local installation with a small set of test data... but does not appear to be working properly on a large set of real data.  
  > **username_0**: Silly me - used an api call and forgot to turn off the auto-limiter... so was not working with the large dataset. Also a typo :). Should be fixed in the repo linked above.  
  > **username_0**: @jon-g Found an issue with the method i've used here. Looks like if a discount code is used, and then the participant record needs to later be deleted, it looks like the cividiscount table entry still persists so there's not currently a mechanism for this report to update to reflect that deletion? More thinking about this to be done. I am moving very slowly on it due to other responsibilities...  
  
  ---
  
  ## 0 answers
  
  