CiviCRM Community Forums (archive)

Yes, seems like a bug. Would help if you can take a look at the report and see if you can figure out whats happening and why.

lobo

I noticed the sql produced by this report had two fields with the same name "civicrm_contribution_total_amount":

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)
When I ran the sql, 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 made the following test edit to the report to the function select() 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.:
/*
            if ($fieldName == 'total_amount') {
              $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}";
             
            }
*/

            if (CRM_Utils_Array::value('is_statistics', $field)) {
              $this->_columnHeaders[$fieldName]['type'] = $field['type'];
              $this->_columnHeaders[$fieldName]['title'] = $field['title'];
              continue;
            }
            elseif ($fieldName == 'receive_date') {
              $select[] = "YEAR({$field['dbAlias']}) as {$tableName}_{$fieldName}";
            }
            elseif ($fieldName == 'total_amount') {
              $select[] = "SUM({$field['dbAlias']}) as {$tableName}_{$fieldName}";
            }
            else {
              $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array::value('type', $field);
              $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = $field['title'];
            }

If you think this addresses the issue, I can look to post a patch file.

can you please file an issue and attach the patch there

thanx

lobo

Helen - Wondering if there's a better name for this report? "Contribution Aggregate by Relationship" or Also, we dropped the word '... Report' from report titles in 4.3.

Separately I think we should include an instance of this report for new installs in 4.3 +. If you're up for including a patch to the report meta-data to do this, that would be great. Check the report instance inserts in xml/templates/civicrm_navigation.tpl starting at line 650 (in 4.3) for examples of this happens.

Hi Dave, I found the report instance entries you described and am happy look at creating a patch.  I'll create a separate issue for this.

Helen, thank you so much for taking this project on!


Helen or Dave, since I have a bit of programming experience but am new to the open source community (at least at the programming level), could you please tell me how I read the top of the patch file? More specifically, how do I read the patch file to know exactly where to put the code? I think I've got it but would like to make sure.


Thank you again!!!!!!!!!!

Helen, thank you so much for taking this project on!

Helen or Dave, since I have a bit of programming experience but am new to the open source community (at least at the programming level), could you please tell me how I read the top of the patch file? More specifically, how do I read the patch file to know exactly where to put the code? I think I've got it but would like to make sure.

Thank you again!!!!!!!!!!

Guy - please pay it back by helping some other folks when u can on the forums, and consider pitching in for one of the 4.4 'make it happen' campaigns.

>>http://civicrm.org/make-it-happen