Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-12637

Contribution History by Relationship Report - needs to use sum value of all contributions per contact per year

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Minor
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.2.8
    • Fix Version/s: 4.3.4
    • Component/s: CiviReport
    • Labels:
      None

      Description

      I noticed the sql produced by this report had two SELECT fields with the same name "civicrm_contribution_total_amount":
      first instance: SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount,
      second instance: contribution_civireport.total_amount as civicrm_contribution_total_amount,

      Copy of full SQL:
      SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name,
      contact_civireport.id as civicrm_contact_id,
      SUM(contribution_civireport.total_amount) as civicrm_contribution_total_amount,
      contribution_civireport.total_amount as civicrm_contribution_total_amount,
      YEAR(contribution_civireport.receive_date) as civicrm_contribution_receive_date
      FROM civicrm_contact contact_civireport
      INNER JOIN civicrm_contribution contribution_civireport
      ON contact_civireport.id = contribution_civireport.contact_id
      AND contribution_civireport.is_test = 0
      WHERE contact_civireport.id IN (1,2,3)
      AND contribution_civireport.is_test = 0
      AND ( contribution_civireport.contribution_status_id IN (1) )
      GROUP BY contribution_civireport.contact_id, YEAR(contribution_civireport.receive_date)

      I ran the sql directly in the db to view the raw results. I think that the report is using the second instance (which would only capture a single contribution per row), but think it should be using the SUM value in the first instance (which would sum all contributions by contact by year).

      I edited the function select() code for the total_amount field (moving it to within the if/else statement below), which resulted in only the SUM value (first instance) being included in the query result set, and this sum value being used by the report.

      Attached is a patch with these edits for v4.2.8

        Attachments

          Activity

            People

            • Assignee:
              dgg David Greenberg
              Reporter:
              hbergin Helen Bergin
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: