2

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

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

  • 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 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 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 at 16:38
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,
    ),

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.