Custom report: how can I add a filter for a dbAlias field?

Question

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

Answer 1

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

Answer 2

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

Pradeep