Author Topic: How to add Group(s) as a FIELD, not just a filter? (Read 764 times)

CiviTeacher.com · February 07, 2011, 07:20:48 pm

Hi there,

Groups are commonly used in CiviReport as a Filter. However, I'd like to see if if there is a way to add GROUPS as a Field.

Of course, a consideration is that a Contact may be in many Groups. Thus, the output field in the report might contain the names of several groups. The Groups listed within the output field could be space or | delineated, that was my first idea. It doesn't have to be pretty, so long as the names of the Groups are there.

I haven't seen a Report that does this yet.. Does anyone have examples or code snippets, or just ideas?

Thanks Guys!

Rajan Mayekar · February 07, 2011, 09:32:13 pm

Hi Stoob,

Following diff will help you

. Diff provides group field (multiple groups separated by comma ) of the contacts on Constituent Report (Summary) .

Code: [Select]

Index: CRM/Report/Form/Contact/Summary.php
===================================================================
--- CRM/Report/Form/Contact/Summary.php	(revision 32272)
+++ CRM/Report/Form/Contact/Summary.php	(working copy)
@@ -114,6 +114,11 @@
                           array( 'phone'  => null),
                           'grouping'  => 'contact-fields',
                           ),
+                   'civicrm_group_field' => 
+                   array( 'dao'    => 'CRM_Contact_DAO_Group',
+                          'fields'    =>
+                          array( 'title'    => 
+                                 array( 'title' => ts('Groups') ) ) ),
 
                    'civicrm_group' => 
                    array( 'dao'    => 'CRM_Contact_DAO_Group',
@@ -151,8 +156,12 @@
                         } else if ( $tableName == 'civicrm_phone' ) {
                             $this->_phoneField = true;
                         }
-
-                        $select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
+                        
+                        if ( $tableName == 'civicrm_group_field' && $fieldName == 'title' ) {
+                            $select[] =  "GROUP_CONCAT(DISTINCT {$field['dbAlias']}  ORDER BY {$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'];
                     }
@@ -175,6 +184,8 @@
                    ON ({$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_address']}.contact_id AND 
                       {$this->_aliases['civicrm_address']}.is_primary = 1 ) ";
         
+        $this->_from .= "LEFT JOIN civicrm_group_contact gc ON {$this->_aliases['civicrm_contact']}.id = gc.contact_id  AND gc.status = 'Added'
+                         LEFT JOIN civicrm_group {$this->_aliases['civicrm_group_field']} ON {$this->_aliases['civicrm_group_field']}.id = gc.group_id "; 
         if ( $this->_emailField ) {
             $this->_from .= "
             LEFT JOIN  civicrm_email {$this->_aliases['civicrm_email']} 
@@ -194,6 +205,9 @@
         $this->_orderBy = " ORDER BY {$this->_aliases['civicrm_contact']}.sort_name ";
     }
     
+    function groupBy( ) {
+        $this->_groupBy = " GROUP BY {$this->_aliases['civicrm_contact']}.id ";
+    }

Rajan

CiviTeacher.com · February 10, 2011, 04:41:50 pm

This was extremely helpful. Thank you. I got it working on my contribution report.

CiviCRM Community Forums (archive)

News:

Author Topic: How to add Group(s) as a FIELD, not just a filter? (Read 764 times)

CiviTeacher.com

How to add Group(s) as a FIELD, not just a filter?

Rajan Mayekar

Re: How to add Group(s) as a FIELD, not just a filter?

CiviTeacher.com

Re: How to add Group(s) as a FIELD, not just a filter?