CiviCRM Community Forums (archive)

Hey Eileen,

It worked for me

I think the process for adding group by custom fields is similar:

It starts with a one-liner

               protected $_customGroupGroupBy = true;

but something is missing.

The SQL it produced was nearly there:
SELECT SQL_CALC_FOUND_ROWS  COUNT( DISTINCT membership_civireport.id ) as civicrm_membership_member_count, membership_civireport.membership_type_id as civicrm_membership_membership_type_id, IFNULL(SUM(contribution_civireport.total_amount), 0) as civicrm_contribution_total_amount_sum, COUNT(contribution_civireport.total_amount) as civicrm_contribution_total_amount_count, IFNULL(ROUND(AVG(contribution_civireport.total_amount),2), 0) as civicrm_contribution_total_amount_avg, contact_custom_34_civireport.display_name as civicrm_value_org_name_extra_info_1_custom_34 
        FROM  civicrm_membership membership_civireport
              LEFT JOIN civicrm_membership_status
                        ON (membership_civireport.status_id = civicrm_membership_status.id  )
              LEFT JOIN civicrm_membership_payment payment
                        ON ( membership_civireport.id = payment.membership_id )
              LEFT JOIN civicrm_contribution contribution_civireport
                         ON payment.contribution_id = contribution_civireport.id
LEFT JOIN civicrm_value_org_name_extra_info_1 value_org_name_extra_info_1_civireport ON value_org_name_extra_info_1_civireport.entity_id = .id
LEFT JOIN civicrm_contact contact_custom_34_civireport ON contact_custom_34_civireport.id = value_org_name_extra_info_1_civireport.local_party_34
LEFT JOIN civicrm_contact contact_custom_36_civireport ON contact_custom_36_civireport.id = value_org_name_extra_info_1_civireport.regional_party_36  WHERE membership_civireport.is_test = 0 AND
                            civicrm_membership_status.is_current_member = 1 GROUP BY contact_custom_34_civireport.display_name  WITH ROLLUP
The join onto civicrm_value_org_name_extra_info_1 on line 9 is missing a table name.  Adding 'membership_civireport' produced the right SQL.

So I'm presuming that there is a line or two to complement the group by one liner that I need to insert.  Anyone know what to do in this situation?

Ta!

aha!

worked it out...

you need to make your fields in the custom data "Searchable".

Worked perfect for me on the grants report.  Just changed array( 'Contact' ) to array('Grant').  Thanks!

You can put your php file in your custom_php dir but for some reason the tpl file isn't picked up from there. However, the packaging of reports is being looked as part of release 3.3 & this will probably be addressed in some way

I'm trying this with a custom report I am working on and ran into three problems:

• The same thing mentioned in this thread: http://forum.civicrm.org/index.php?topic=13871.0  I get the "Database Error Code: Unknown column 'TABLE_NAME_civireport.FIELD' in 'field list'" error when I try and filter on a my custom data
• Is there a way to use no_display for filtering on custom data?  I have quite a bit of custom data defined for individuals but don't need to filter on most of it for this particular report
• While the custom data shows up in my filters it isn't showing up in the report.  Do I need to include something special in my output columns array to have the custom data appear?  Is there a dao value for custom data?

Thanks!

Hi,  when Eclipse loads I may be able to add some more but off the top

1) It depends a bit on the template you are working from but on pledge I had to add to the PostProcess
        $this->buildACLClause( $this->_aliases['civicrm_contact'] );
        $this->select ( );
        $this->from   ( );
        $this->customDataFrom( );
        $this->where  ( );
        $this->groupBy( );

In CiviCRM 3.2 it looks like that is built into buildQuery (in CRM/Report/Form.php).  My postProcess() just calls does:
        $this->buildACLClause( $this->_aliases['civicrm_contact'] );
        $sql  = $this->buildQuery( true );

        $rows = array( );
        $this-> buildRows( $sql, $rows );
My initial code was taken from the membership detail report.

2) Try replacing criteria.tpl with this one: https://svn.fuzion.co.nz/repos/mprc/civicrm/templates/CRM/Report/Form/Criteria.tpl (per http://forum.civicrm.org/index.php/topic,16582.0.html) - Makes it look much better - I would like to submit this for core but would prefer to get some feedback / testing first

I will have to take a look at that later.

3) Confused by this... You should be able to select your custom fields under 'display' as well as 'filter'

Argh, that was my whole problem!  The database error was due to my trying to filter on a field I hadn't selected for display!  Apparently if you don't display the field you can't filter on it and it doesn't get added to the report temporary tables.

That still leaves me with my 2nd point though, I would really like to remove the fields I am not interested in and to have the ones I am auto-selected for display.  For a non-custom data field I would add something like:
                   'civicrm_membership_status' =>
                   array( 'dao'      => 'CRM_Member_DAO_MembershipStatus',
                          'alias'    => 'mem_status',
                          'fields'   =>
                          array( 'name'  =>  array( 'title'   => ts('Status'),
                                                    'default' => true ),
                                 ),

                          'filters'  => array( 'sid' =>
                                               array( 'name'         => 'id',
                                                      'title'        => ts( 'Status' ),
                                                      'default'      => array('1'),
                                                      'type'         => CRM_Utils_Type::T_INT,
                                                      'operatorType' => CRM_Report_Form::OP_MULTISELECT,
                                                      'options'      => CRM_Member_PseudoConstant::membershipStatus( null, null, 'label') ), ),
                          'grouping' => 'member-fields',
                          ),
to my _columns array.  Is there anything I can add for custom fields?  In this case I want to pre-select one field and hide the rest....

Eileen,

Thanks for the pointer to that template.  It isn't exactly what I want, but it is a whole lot better than a huge list of profile fields....