Answers.Body: <p>Jake sent me this - posting it here for save keeping!</p>
  
  <pre><code> 'total_amount_sum' =&gt;
      array('title' =&gt; ts('Aggregate Contribution Amount'),
        'type' =&gt; CRM_Report_Form::OP_INT,
        'dbAlias' =&gt; 'civicrm_contribution_amounts_ytd',
        'having' =&gt; TRUE,
      ),
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-10-18T21:45:15.433Z
Answers.Text: Hi @KarinG Thanks for posting this. I also would like to add this option to Top Donor report. When I added the code and entered a value I get a white screen. and an error in the log "Undefined index: message in CRM_ReportError_Utils::sendReport()". Is there any other line needs to be added to TopDonor.PHP?
Answers.UserDisplayName: Tapash
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-01-31T03:54:52.457Z
Answers.LastActivityDate: 2019-01-31T03:54:52.457Z
Answers.UserDisplayName: KarinG - Semper IT
Answers.Body: <p>I think you should use having clause rather where clause i.e skip the field in whereclause() and have it filter in having clause. </p>
  
  <p><code>SUM(CASE WHEN year(contribution_civireport.receive_date)=2018 THEN contribution_civireport.total_amount ELSE 0 END) &gt; 0</code></p>
  
  <p>Other option would be to create a temporary table to insert the all the data grouping by sum and contact and then using the temporary table to retrieve the final result with where clause good eg will be Activity Detail report or Contribution Detail report.</p>
  
  <p>HTH</p>
  
  <p>Pradeep</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-01-29T15:50:16.660Z
Answers.Text: Sorry Pradeep - I don’t understand what you mean. I need this to show under the Filter tab in the report so admins can decide on thresholds.
Answers.UserDisplayName: KarinG - Semper IT
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-01-29T16:27:17.317Z
Answers.Text: I think he's partly saying you can look at the activity details report to see how it does it. It has custom field filters.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-01-29T16:38:18.353Z
Answers.Text: Oh, but you mean custom as in created by the report code itself not a regular custom field. The activity detail report might not have that. Nevermind.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-01-29T14:29:30.740Z
Answers.LastActivityDate: 2019-01-29T14:29:30.740Z
Answers.UserDisplayName: Pradeep Nayak
Body: <p>In a custom report -> I've created a field</p>
  
  <pre><code> 'civicrm_contribution' =&gt; array(
      'dao' =&gt; 'CRM_Contribute_DAO_Contribution',
      'fields' =&gt; array(
  </code></pre>
  
  <p>...</p>
  
  <pre><code>         'amounts_ytd' =&gt; array(
             'title' =&gt; ts('Amount - YTD 2018'),
             'required' =&gt; TRUE,
             'dbAlias' =&gt; "SUM(CASE WHEN year(contribution_civireport.receive_date)=2018 THEN contribution_civireport.total_amount ELSE 0 END)",
           ),
  </code></pre>
  
  <p>This works nicely - it produces the correct total in the amounts_ytd column.</p>
  
  <p>How would I go about filtering on this amount - to e.g. find all contacts who have amounts_ytd > $100? </p>
  
  <p>Under:</p>
  
  <pre><code>    'filters' =&gt; array(
  </code></pre>
  
  <p>What does this need to look like?</p>
  
  <pre><code>  'amounts_ytd' =&gt;
       array('title' =&gt; ts('Aggregate amounts_ytd'),
         'operatorType' =&gt; CRM_Report_Form::OP_FLOAT,
         'type' =&gt; CRM_Utils_Type::T_FLOAT,
         '??'
       ),
  </code></pre>
  
Tags: <civireport>
Title: Custom report: how can I add a filter for a dbAlias field?
ThreadText: # Custom report: how can I add a filter for a dbAlias field?
  
  (Asked by: username_0 on 2019-01-29)
  
  In a custom report -> I've created a field
  
  ```
   'civicrm_contribution' => array(
      'dao' => 'CRM_Contribute_DAO_Contribution',
      'fields' => array(
  
  ```
  
  ...
  
  ```
           'amounts_ytd' => array(
             'title' => ts('Amount - YTD 2018'),
             'required' => TRUE,
             'dbAlias' => "SUM(CASE WHEN year(contribution_civireport.receive_date)=2018 THEN contribution_civireport.total_amount ELSE 0 END)",
           ),
  
  ```
  
  This works nicely - it produces the correct total in the amounts\_ytd column.
  
  How would I go about filtering on this amount - to e.g. find all contacts who have amounts\_ytd > $100?
  
  Under:
  
  ```
      'filters' => array(
  
  ```
  
  What does this need to look like?
  
  ```
    'amounts_ytd' =>
       array('title' => ts('Aggregate amounts_ytd'),
         'operatorType' => CRM_Report_Form::OP_FLOAT,
         'type' => CRM_Utils_Type::T_FLOAT,
         '??'
       ),
  
  ```
  
  **Tags:** civireport
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2019-01-31. Score: 2
  
  Jake sent me this - posting it here for save keeping!
  
  ```
   'total_amount_sum' =>
      array('title' => ts('Aggregate Contribution Amount'),
        'type' => CRM_Report_Form::OP_INT,
        'dbAlias' => 'civicrm_contribution_amounts_ytd',
        'having' => TRUE,
      ),
  
  ```
  
  > **Comments:**
  > **username_1**: Hi @username_0 Thanks for posting this. I also would like to add this option to Top Donor report. When I added the code and entered a value I get a white screen. and an error in the log "Undefined index: message in CRM_ReportError_Utils::sendReport()". Is there any other line needs to be added to TopDonor.PHP?  
  
  ---
  
  ### Answer by username_2 on 2019-01-29. Score: 0
  
  I think you should use having clause rather where clause i.e skip the field in whereclause() and have it filter in having clause.
  
  `SUM(CASE WHEN year(contribution_civireport.receive_date)=2018 THEN contribution_civireport.total_amount ELSE 0 END) > 0`
  
  Other option would be to create a temporary table to insert the all the data grouping by sum and contact and then using the temporary table to retrieve the final result with where clause good eg will be Activity Detail report or Contribution Detail report.
  
  HTH
  
  username_2
  
  > **Comments:**
  > **username_0**: Sorry username_2 - I don’t understand what you mean. I need this to show under the Filter tab in the report so admins can decide on thresholds.  
  > **username_3**: I think he's partly saying you can look at the activity details report to see how it does it. It has custom field filters.  
  > **username_3**: Oh, but you mean custom as in created by the report code itself not a regular custom field. The activity detail report might not have that. Nevermind.  
  
  ---
  
  