Generating Reports Based on Custom Data

[Upstater]

Can anyone point me toward the code to generate reports based on custom data (as in, generate a list of individuals where CustomDataField1=0)?

Another topic (http://forum.civicrm.org/index.php/topic,10238.0.html) says that there's code out there, but I can't find it.

Thanks!

[Donald Lobo]

exposing custom data is part of the contribution report (and some other reports too)

CRM/Report/Form/Contribute/Summary.php

search for custom

if you want to use it as a filter, you should contact abrookins (from the other forum thread) and see if he can submit a patch that folks can use


lobo

[markimus]

Is this what your looking for? 

http://forum.civicrm.org/index.php/topic,13188.0.html
 

[lathos]

I'm playing with this at the moment, trying to make a generic statistics report so that people can say "Tell me how many people came for an interview in each month" - one problem I had was that although you can group by custom data, if you turn the $_customGroupGroupBy flag on, you can't categorize custom date fields by frequency.

I think the attached patch fixes that; it seems to work for me.

[tripp]

Sorry. A non-recoverable error has occurred.

DB Error: no such field

Database Error Code: Unknown column 'TABLE_NAME_civireport.FIELD' in 'field list', 1054

I could use some help on this topic, also.  I keep getting this error.  What seems to have happened is that once I changed the Custom Data names, CiviReport keeps using the old TABLE NAME and so never finds the proper column.  (was civicrm_value_1_Appointment_Information and now is session_info) (you can also see that it would truncate the v off of the old name)

I am running Drupal 6.17 and CiviCRM 3.1.5.
I have run Upgrade, Reset Menu, and cleared caches.
Also worked on applying this:  http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades

Not sure if this other information would help.

Thanks for any insights.

I get a similar error message whether I try to run an activity report with the custom data or after I hit SAVE when I try to edit the existing custom data labels or settings.

Code: [Select]

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT SQL_CALC_FOUND_ROWS
    contact_civireport.id as civicrm_contact_source_contact_id, contact_civireport.display_name as civicrm_contact_contact_source, civicrm_contact_target.display_name as civicrm_contact_contact_target, activity_civireport.id as civicrm_activity_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.source_contact_id as civicrm_activity_source_contact_id, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_assignment_civireport.assignee_contact_id as civicrm_activity_assignment_assignee_contact_id, activity_target_civireport.target_contact_id as civicrm_activity_target_target_contact_id, case_activity_civireport.case_id as civicrm_case_activity_case_id,
   
    alue_1_Appointment_Information_civireport.CPT_Code as custom_value_1_Appointment_Information_custom_3
   
   
        FROM civicrm_activity activity_civireport
       
             LEFT JOIN civicrm_activity_target  activity_target_civireport
                    ON activity_civireport.id = activity_target_civireport.activity_id
             LEFT JOIN civicrm_activity_assignment activity_assignment_civireport
                    ON activity_civireport.id = activity_assignment_civireport.activity_id
             LEFT JOIN civicrm_contact contact_civireport
                    ON activity_civireport.source_contact_id = contact_civireport.id
             LEFT JOIN civicrm_contact civicrm_contact_target
                    ON activity_target_civireport.target_contact_id = civicrm_contact_target.id
             LEFT JOIN civicrm_contact civicrm_contact_assignee
                    ON activity_assignment_civireport.assignee_contact_id = civicrm_contact_assignee.id
           
             
             LEFT JOIN civicrm_option_value
                    ON ( activity_civireport.activity_type_id = civicrm_option_value.value )
             LEFT JOIN civicrm_option_group
                    ON civicrm_option_group.id = civicrm_option_value.option_group_id
             LEFT JOIN civicrm_case_activity case_activity_civireport
                    ON case_activity_civireport.activity_id = activity_civireport.id
             LEFT JOIN civicrm_case
                    ON case_activity_civireport.case_id = civicrm_case.id
             LEFT JOIN civicrm_case_contact
                    ON civicrm_case_contact.case_id = civicrm_case.id   WHERE civicrm_option_group.name = 'activity_type' AND
                                activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20100401000000 ) AND ( activity_date_time <= 20100630235959 ) AND ( activity_civireport.activity_type_id IN (1) ) AND ( activity_civireport.status_id IN (2) ) GROUP BY activity_civireport.activity_date_time, activity_civireport.id     LIMIT 0, 50 [nativecode=1054 ** Unknown column 'alue_1_Appointment_Information_civireport.CPT_Code' in 'field list']
    [type] => DB_Error
    [user_info] => SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_source_contact_id, contact_civireport.display_name as civicrm_contact_contact_source, civicrm_contact_target.display_name as civicrm_contact_contact_target, activity_civireport.id as civicrm_activity_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.source_contact_id as civicrm_activity_source_contact_id, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_assignment_civireport.assignee_contact_id as civicrm_activity_assignment_assignee_contact_id, activity_target_civireport.target_contact_id as civicrm_activity_target_target_contact_id, case_activity_civireport.case_id as civicrm_case_activity_case_id,
   
    alue_1_Appointment_Information_civireport.CPT_Code as custom_value_1_Appointment_Information_custom_3
   
        FROM civicrm_activity activity_civireport
       
             LEFT JOIN civicrm_activity_target  activity_target_civireport
                    ON activity_civireport.id = activity_target_civireport.activity_id
             LEFT JOIN civicrm_activity_assignment activity_assignment_civireport
                    ON activity_civireport.id = activity_assignment_civireport.activity_id
             LEFT JOIN civicrm_contact contact_civireport
                    ON activity_civireport.source_contact_id = contact_civireport.id
             LEFT JOIN civicrm_contact civicrm_contact_target
                    ON activity_target_civireport.target_contact_id = civicrm_contact_target.id
             LEFT JOIN civicrm_contact civicrm_contact_assignee
                    ON activity_assignment_civireport.assignee_contact_id = civicrm_contact_assignee.id
           
             
             LEFT JOIN civicrm_option_value
                    ON ( activity_civireport.activity_type_id = civicrm_option_value.value )
             LEFT JOIN civicrm_option_group
                    ON civicrm_option_group.id = civicrm_option_value.option_group_id
             LEFT JOIN civicrm_case_activity case_activity_civireport
                    ON case_activity_civireport.activity_id = activity_civireport.id
             LEFT JOIN civicrm_case
                    ON case_activity_civireport.case_id = civicrm_case.id
             LEFT JOIN civicrm_case_contact
                    ON civicrm_case_contact.case_id = civicrm_case.id   WHERE civicrm_option_group.name = 'activity_type' AND
                                activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20100401000000 ) AND ( activity_date_time <= 20100630235959 ) AND ( activity_civireport.activity_type_id IN (1) ) AND ( activity_civireport.status_id IN (2) ) GROUP BY activity_civireport.activity_date_time, activity_civireport.id     LIMIT 0, 50 [nativecode=1054 ** Unknown column 'alue_1_Appointment_Information_civireport.CPT_Code' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT SQL_CALC_FOUND_ROWS contact_civireport.id as civicrm_contact_source_contact_id, contact_civireport.display_name as civicrm_contact_contact_source, civicrm_contact_target.display_name as civicrm_contact_contact_target, activity_civireport.id as civicrm_activity_id, activity_civireport.activity_type_id as civicrm_activity_activity_type_id, activity_civireport.source_contact_id as civicrm_activity_source_contact_id, activity_civireport.activity_date_time as civicrm_activity_activity_date_time, activity_assignment_civireport.assignee_contact_id as civicrm_activity_assignment_assignee_contact_id, activity_target_civireport.target_contact_id as civicrm_activity_target_target_contact_id, case_activity_civireport.case_id as civicrm_case_activity_case_id, alue_1_Appointment_Information_civireport.CPT_Code as custom_value_1_Appointment_Information_custom_3 
        FROM civicrm_activity activity_civireport
       
             LEFT JOIN civicrm_activity_target  activity_target_civireport
                    ON activity_civireport.id = activity_target_civireport.activity_id
             LEFT JOIN civicrm_activity_assignment activity_assignment_civireport
                    ON activity_civireport.id = activity_assignment_civireport.activity_id
             LEFT JOIN civicrm_contact contact_civireport
                    ON activity_civireport.source_contact_id = contact_civireport.id
             LEFT JOIN civicrm_contact civicrm_contact_target
                    ON activity_target_civireport.target_contact_id = civicrm_contact_target.id
             LEFT JOIN civicrm_contact civicrm_contact_assignee
                    ON activity_assignment_civireport.assignee_contact_id = civicrm_contact_assignee.id
           
             
             LEFT JOIN civicrm_option_value
                    ON ( activity_civireport.activity_type_id = civicrm_option_value.value )
             LEFT JOIN civicrm_option_group
                    ON civicrm_option_group.id = civicrm_option_value.option_group_id
             LEFT JOIN civicrm_case_activity case_activity_civireport
                    ON case_activity_civireport.activity_id = activity_civireport.id
             LEFT JOIN civicrm_case
                    ON case_activity_civireport.case_id = civicrm_case.id
             LEFT JOIN civicrm_case_contact
                    ON civicrm_case_contact.case_id = civicrm_case.id   WHERE civicrm_option_group.name = 'activity_type' AND
                                activity_civireport.is_test = 0 AND
                                activity_civireport.is_deleted = 0 AND
                                activity_civireport.is_current_revision = 1 AND ( activity_date_time >= 20100401000000 ) AND ( activity_date_time <= 20100630235959 ) AND ( activity_civireport.activity_type_id IN (1) ) AND ( activity_civireport.status_id IN (2) ) GROUP BY activity_civireport.activity_date_time, activity_civireport.id     LIMIT 0, 50 [nativecode=1054 ** Unknown column 'alue_1_Appointment_Information_civireport.CPT_Code' in 'field list']"]
)[/code]