CiviCRM Community Forums (archive)

Can you see any pattern in the rows that are not displayed? Any reason why they might be missing?

Actually, it looks like the missing rows may not have an associated record in the civicrm_membership_payment table.  But I checked, and they never had one even in the older versions before the upgrade, but the report worked in the older versions. 

I ran the report and had it output the sql.  When I run the sql and comment out the section that references the contributions and payments, it returns the missing rows.  The sql that works looks like this:

SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, membership_civireport.membership_type_id as civicrm_membership_membership_type_id,
email_civireport.email as civicrm_email_email
/*,
contribution_civireport.id as civicrm_contribution_contribution_id, contribution_civireport.currency as civicrm_contribution_currency  */
         FROM  civicrm_contact contact_civireport
               INNER JOIN civicrm_membership membership_civireport
                          ON contact_civireport.id =
                             membership_civireport.contact_id AND membership_civireport.is_test = 0
               LEFT  JOIN civicrm_membership_status mem_status_civireport
                          ON mem_status_civireport.id =
                             membership_civireport.status_id
              LEFT JOIN civicrm_email email_civireport
                        ON contact_civireport.id =
                           email_civireport.contact_id AND
                           email_civireport.is_primary = 1

              /*LEFT JOIN (
                  SELECT cc.*, cmp.membership_id as membership_id
                  FROM civicrm_membership_payment cmp
                    JOIN civicrm_contribution cc
                      ON cc.id = cmp.contribution_id
                  ORDER BY cc.receive_date DESC
                  ) contribution_civireport
                ON membership_civireport.id =
                  contribution_civireport.membership_id*/
 WHERE ( membership_civireport.join_date >= 20120501 ) AND ( mem_status_civireport.id IN (1, 2) )
 /*AND ( contribution_civireport.contribution_status_id IN (1) ) */
 AND contact_civireport.is_deleted = 0   GROUP BY contact_civireport.id, membership_civireport.membership_type_id   ORDER BY contact_civireport.sort_name, contact_civireport.id, membership_civireport.membership_type_id
 /*, contribution_civireport.receive_date*/ DESC
When I run the query without commenting out those sections, there are many rows missing in both the new and old databases.  So the query for this report must have changed in newer versions of CiviCRM.  Can you help me to understand what's going on and how to remedy this?