Tip - how to add custom fields to an exisiting report - e.g. member/details

[Eileen]

If you want custom fields on your report - e.g. member details report you can add them with one line

     protected $_customGroupExtends = array( 'Contact' );   

I added this around line 50 of CRM/Report/Form/Member/Detail.php and magically it was possible to select & filter by custom fields that extend 'Contact'. Note that contact doesn't include 'Individual' in this context so if the field extends an individual you need to change it to that

I tried this on CiviCRM 3.1.3  (and I think 3.1.2 too). I don't promise it will work for all existing report templates but it works here.

[Michael McAndrew]

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:

Code: [Select]

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!

[Yoda_Oz]

im resurrecting this thread because i really REALLY need to have my custom fields in my reports.

unfortunately, the tip above has no effect on my report. my custom fields are "Individual" and so i just changed Contact to Individual but nothing happened. nothing happens when i put in Contact either...

is there another way to get custom fields into the reports?

thanks

[Yoda_Oz]

aha!

worked it out...

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

[sbotts]

Thank you, Eileen, it truly was magical! That made our county voter data usable and saved me many hours of unhappiness.

[ashley]

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

[Slovak]

Wonderful tip, thank you!

Just to follow the convention, shouldn't this live somewhere in the files directory instead of hacking core, which would make this edit disappear during an upgrade?

[Eileen]

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

[Eileen]

but for some reason the tpl file isn't picked up from there

Correction - the tpl file isn't picked up from there if you have CiviEngage installed & haven't got the patch I recently did to stop it over-writing your custom tpl directory

[sjthespian]

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!

[Eileen]

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( );

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

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

[sjthespian]

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:

Code: [Select]

        $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:

Code: [Select]

                   '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]

Try the criteria.tpl - it takes the pain out of having too many custom fields without you having to spend ages figuring out how to remove them

[sjthespian]

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....