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?


    '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,

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,
  • 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? – Tapash Oct 18 '20 at 21:45

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.



  • 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. – KarinG - Semper IT Jan 29 '19 at 15:50
  • 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. – Demerit Jan 29 '19 at 16:27
  • 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. – Demerit Jan 29 '19 at 16:38

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.