CiviCRM – Creating a Custom Report

One of my current projects involves developing and deploying a new membership/contact database and management information system for a large not-for-profit organisation. Having examined various off-the-shelf systems, including HighRise, the decision was made to go with a customised build of CiviCRM instead.

I’m quite familiar with Drupal (the backend framework on which CiviCRM sits), but I hadn’t  got any particular experience with CiviCRM itself. I have to say that, out-of-the-box, Drupal + CiviCRM is an amazingly powerful combination, and it allowed me to build a prototype demonstrating most of the major functions (including importing their existing dataset from a variety of messy Excel spreadsheets) using the built-in options in just a few weeks. However (and, in accordance with the Pareto principle), the last 20% or so of their requirements have taken a little bit longer to fulfil…

Many of these requirements involve developing custom reports. Or, to use specific CiviCRM-speak, developing reports based on custom report templates – i.e. a report based on a customised set of data (customising an existing report by altering say, display fields, filtering or grouping options that are already exposed in the underlying template is easy).

The process for creating a customised report template – a whole new type of report – is as follows:

1. Firstly, ensure that you have configured CiviCRM directory paths for both Custom Templates and Custom PHP. These can be set at Administer –> Configure –> Global Settings –> Directories. I set my paths to point at /sites/default/files/civicrm/custom_templates and /sites/default/files/civicrm/custom_php , respectively.

2. Within the specified custom directories, I then created a directory structure that matched the location of the CiviCRM built-in reports in the \modules\civicrm directory. So, for example, I created a directory structure that looked like:

\sites\default\files\civicrm\custom_templates\CRM\Report\Form

\sites\default\files\civicrm\custom_php\CRM\Report\Form

3. The next step is to create the template file. To create a new standard report, the only thing this template needs to contain is a reference to the standard CiviCRM report form, as follows:

{include file="CRM/Report/Form.tpl"}

That’s it. Save the file in the \sites\default\files\civicrm\custom_templates\CRM\Report\Form directory with a suitable name (the report I’m creating in this case is called TargetActivity.tpl).

4. Next, create the PHP file. This file contains a class that extends the default CRM_Report_Form class, and specifies the query that should be run to populate the report, the columns to display, group by, and sort, for example. The report I’m creating in this case is a target activity report – in other words it shows a list of recent activity (phones calls, emails, letters etc.) together with the target of that activity – who the person being contacted was (the built-in CiviCRM report focusses on the source contact – the person who initiated the phone call, not the target contact). The PHP file I used for this custom report was based on the default Activity.php file, modified as follows:

<?php

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2010
 * $Id$
 *
 */

require_once 'CRM/Report/Form.php';

class CRM_Report_Form_TargetActivity extends CRM_Report_Form {

protected $_emailField         = false;
 protected $_customGroupExtends = array( 'Activity' );

function __construct( ) {
 $config = CRM_Core_Config::singleton( );
 $campaignEnabled = in_array( "CiviCampaign", $config->enableComponents );
 $this->_columns = array(
 'civicrm_contact'      =>
 array( 'dao'     => 'CRM_Contact_DAO_Contact',
 'fields'  =>
 array(
 'contact_target'   =>
 array( 'name'      => 'display_name' ,
 'title'     => ts( 'Target Contact Name' ),
 'alias'     => 'civicrm_contact_target',
 'default'   => true,
 'required'   => true,  ),
 'contact_source'    =>
 array( 'name'      => 'display_name' ,
 'title'     => ts( 'Source Contact Name' ),
 'alias'     => 'contact_civireport',
 'no_repeat' => true ),
 ),
 'filters' =>
 array( 'contact_source'   =>
 array('name'       => 'sort_name' ,
 'alias'      => 'contact_civireport',
 'title'      => ts( 'Source Contact Name' ),
 'operator'   => 'like',
 'type'       => CRM_Report_Form::OP_STRING ),
 'contact_target'    =>
 array( 'name'      => 'sort_name' ,
 'alias'     => 'civicrm_contact_target',
 'title'     => ts( 'Target Contact Name' ),
 'operator'  => 'like',
 'type'      => CRM_Report_Form::OP_STRING  ) ),
 'grouping' => 'contact-fields',
 ),

 'civicrm_email'         =>
 array( 'dao'     => 'CRM_Core_DAO_Email',
 'fields'  =>
 array(
 'contact_target_email'   =>
 array( 'name'      => 'email' ,
 'title'     => ts( 'Target Contact Email' ),
 'alias'     => 'civicrm_email_target', ),
 ),
 ),

 'civicrm_activity'      =>
 array( 'dao'     => 'CRM_Activity_DAO_Activity',
 'fields'  =>
 array(  'id'                =>
 array( 'no_display' => true,
 'required'   => true
 ),
 'activity_type_id'  =>
 array( 'title'      => ts( 'Activity Type' ),
 'default'    => true,
 'type'       =>  CRM_Utils_Type::T_STRING
 ),
 'activity_subject'  =>
 array( 'title'      => ts('Subject'),
 'default'    => true,
 ),
 'source_contact_id' =>
 array( 'no_display' => true ,
 'required'   => true , ),
 'activity_date_time'=>
 array( 'title'      => ts( 'Activity Date'),
 'default'    => true ),
 'status_id'         =>
 array( 'title'      => ts( 'Activity Status' ),
 'default'    => true ,
 'type'       =>  CRM_Utils_Type::T_STRING ), ),
 'filters' =>
 array( 'activity_date_time'  =>
 array( 'default'      => 'this.month',
 'operatorType' => CRM_Report_Form::OP_DATE),
 'activity_subject'    =>
 array( 'title'        => ts( 'Activity Subject' ) ),
 'activity_type_id'    =>
 array( 'title'        => ts( 'Activity Type' ),
 'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 'options'      => CRM_Core_PseudoConstant::activityType( true, false, false, 'label', true ), ),
 'status_id'           =>
 array( 'title'        => ts( 'Activity Status' ),
 'operatorType' => CRM_Report_Form::OP_MULTISELECT,
 'options'      => CRM_Core_PseudoConstant::activityStatus(), ),
 ),
 'group_bys' =>
 array( 'source_contact_id'  =>
 array('title'    => ts( 'Source Contact' ),
 'default'  => true ),
 'activity_date_time' =>
 array( 'title'   => ts( 'Activity Date' ) ),
 'activity_type_id'   =>
 array( 'title'   => ts( 'Activity Type' ) ),
 ),
 'grouping' => 'activity-fields',
 'alias'    => 'activity'

 ),

 'civicrm_activity_assignment'      =>
 array( 'dao'     => 'CRM_Activity_DAO_ActivityAssignment',
 'fields'  =>
 array(
 'assignee_contact_id' =>
 array( 'no_display' => true,
 'required'   => true ), ),
 'alias'   => 'activity_assignment'
 ),
 'civicrm_activity_target'        =>
 array( 'dao'     => 'CRM_Activity_DAO_ActivityTarget',
 'fields'  =>
 array(
 'target_contact_id' =>
 array( 'no_display' => true,
 'required'   => true ), ),
 'alias'   => 'activity_target'
 ),
 'civicrm_case_activity'        =>
 array( 'dao'     => 'CRM_Case_DAO_CaseActivity',
 'fields'  =>
 array(
 'case_id' =>
 array( 'name'       => 'case_id',
 'no_display' => true,
 'required'   => true,
 ),),
 'alias'   => 'case_activity'
 ),

 );

parent::__construct( );
 }

function select( ) {
 $select = array( );
 $this->_columnHeaders = array( );
 foreach ( $this->_columns as $tableName => $table ) {
 if ( array_key_exists('fields', $table) ) {
 foreach ( $table['fields'] as $fieldName => $field ) {
 if ( CRM_Utils_Array::value( 'required', $field ) ||
 CRM_Utils_Array::value( $fieldName, $this->_params['fields'] ) ) {
 if ( $tableName == 'civicrm_email' ) {
 $this->_emailField = true;
 }

$select[] = "{$field['dbAlias']} as {$tableName}_{$fieldName}";
 $this->_columnHeaders["{$tableName}_{$fieldName}"]['type']  = CRM_Utils_Array::value( 'type', $field );
 $this->_columnHeaders["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array::value( 'title', $field );
 $this->_columnHeaders["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array::value( 'no_display', $field );
 }
 }
 }
 }

$this->_select = "SELECT " . implode( ', ', $select ) . " ";

}

function from( ) {

$this->_from = "
 FROM civicrm_activity {$this->_aliases['civicrm_activity']}

 LEFT JOIN civicrm_activity_target  {$this->_aliases['civicrm_activity_target']}
 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_target']}.activity_id
 LEFT JOIN civicrm_activity_assignment {$this->_aliases['civicrm_activity_assignment']}
 ON {$this->_aliases['civicrm_activity']}.id = {$this->_aliases['civicrm_activity_assignment']}.activity_id
 LEFT JOIN civicrm_contact contact_civireport
 ON {$this->_aliases['civicrm_activity']}.source_contact_id = contact_civireport.id
 LEFT JOIN civicrm_contact civicrm_contact_target
 ON {$this->_aliases['civicrm_activity_target']}.target_contact_id = civicrm_contact_target.id
 LEFT JOIN civicrm_contact civicrm_contact_assignee
 ON {$this->_aliases['civicrm_activity_assignment']}.assignee_contact_id = civicrm_contact_assignee.id

 {$this->_aclFrom}
 LEFT JOIN civicrm_option_value
 ON ( {$this->_aliases['civicrm_activity']}.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 = {$this->_aliases['civicrm_activity']}.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 ";

 if ( $this->_emailField ) {
 $this->_from .= "
 LEFT JOIN civicrm_email civicrm_email_source
 ON {$this->_aliases['civicrm_activity']}.source_contact_id = civicrm_email_source.contact_id AND
 civicrm_email_source.is_primary = 1

LEFT JOIN civicrm_email civicrm_email_target
 ON {$this->_aliases['civicrm_activity_target']}.target_contact_id = civicrm_email_target.contact_id AND
 civicrm_email_target.is_primary = 1

LEFT JOIN civicrm_email civicrm_email_assignee
 ON {$this->_aliases['civicrm_activity_assignment']}.assignee_contact_id = civicrm_email_assignee.contact_id AND
 civicrm_email_assignee.is_primary = 1 ";
 }
 }

function where( ) {
 $this->_where = " WHERE civicrm_option_group.name = 'activity_type' AND
 {$this->_aliases['civicrm_activity']}.is_test = 0 AND
 {$this->_aliases['civicrm_activity']}.is_deleted = 0 AND
 {$this->_aliases['civicrm_activity']}.is_current_revision = 1";

 $clauses = array( );
 foreach ( $this->_columns as $tableName => $table ) {
 if ( array_key_exists('filters', $table) ) {

foreach ( $table['filters'] as $fieldName => $field ) {
 $clause = null;
 if ( CRM_Utils_Array::value( 'type', $field ) & CRM_Utils_Type::T_DATE ) {
 $relative = CRM_Utils_Array::value( "{$fieldName}_relative", $this->_params );
 $from     = CRM_Utils_Array::value( "{$fieldName}_from"    , $this->_params );
 $to       = CRM_Utils_Array::value( "{$fieldName}_to"      , $this->_params );

 $clause = $this->dateClause( $field['name'], $relative, $from, $to, $field['type'] );
 } else {
 $op = CRM_Utils_Array::value( "{$fieldName}_op", $this->_params );
 if ( $op ) {
 $clause =
 $this->whereClause( $field,
 $op,
 CRM_Utils_Array::value( "{$fieldName}_value", $this->_params ),
 CRM_Utils_Array::value( "{$fieldName}_min", $this->_params ),
 CRM_Utils_Array::value( "{$fieldName}_max", $this->_params ) );
 }
 }

 if ( ! empty( $clause ) ) {
 $clauses[] = $clause;
 }
 }
 }
 }

if ( empty( $clauses ) ) {
 $this->_where .= " ";
 } else {
 $this->_where .= " AND " . implode( ' AND ', $clauses );
 }

 if ( $this->_aclWhere ) {
 $this->_where .= " AND {$this->_aclWhere} ";
 }
 }

function groupBy( ) {
 $this->_groupBy   = array();
 if ( ! empty($this->_params['group_bys']) ) {
 foreach ( $this->_columns as $tableName => $table ) {
 if ( ! empty($table['group_bys']) ) {
 foreach ( $table['group_bys'] as $fieldName => $field ) {
 if ( CRM_Utils_Array::value( $fieldName, $this->_params['group_bys'] ) ) {
 $this->_groupBy[] = $field['dbAlias'];
 }
 }
 }
 }
 }
 $this->_groupBy[] = "{$this->_aliases['civicrm_activity']}.id";
 $this->_groupBy   = "GROUP BY " . implode( ', ', $this->_groupBy ) . " ";
 }

function buildACLClause( $tableAlias ) {
 //override for ACL( Since Cotact may be source
 //contact/assignee or target also it may be null )

 require_once 'CRM/Core/Permission.php';
 require_once 'CRM/Contact/BAO/Contact/Permission.php';
 if ( CRM_Core_Permission::check( 'view all contacts' ) ) {
 $this->_aclFrom = $this->_aclWhere = null;
 return;
 }

 $session = CRM_Core_Session::singleton( );
 $contactID =  $session->get( 'userID' );
 if ( ! $contactID ) {
 $contactID = 0;
 }
 $contactID = CRM_Utils_Type::escape( $contactID, 'Integer' );

 CRM_Contact_BAO_Contact_Permission::cache( $contactID );
 $clauses = array();
 foreach( $tableAlias as $k => $alias ) {
 $clauses[] = " INNER JOIN civicrm_acl_contact_cache aclContactCache_{$k} ON ( {$alias}.id = aclContactCache_{$k}.contact_id OR {$alias}.id IS NULL ) AND aclContactCache_{$k}.user_id = $contactID ";
 }

 $this->_aclFrom  = implode(" ", $clauses );
 $this->_aclWhere = null;
 }
 function postProcess( ) {

 $this->buildACLClause( array( 'contact_civireport' , 'civicrm_contact_target', 'civicrm_contact_assignee' ) );
 parent::postProcess();
 }

function alterDisplay( &$rows ) {
 // custom code to alter rows

 $entryFound     = false;
 $activityType   = CRM_Core_PseudoConstant::activityType( true, true, false, 'label', true );
 $activityStatus = CRM_Core_PseudoConstant::activityStatus();
 $viewLinks      = false;

require_once 'CRM/Core/Permission.php';
 if ( CRM_Core_Permission::check( 'access CiviCRM' ) ) {
 $viewLinks  = true;
 $onHover    = ts('View Contact Summary for this Contact');
 $onHoverAct = ts('View Activity Record');
 }
 foreach ( $rows as $rowNum => $row ) {

 if ( array_key_exists('civicrm_contact_contact_source', $row ) ) {
 if ( $value = $row['civicrm_contact_source_contact_id'] ) {
 if ( $viewLinks ) {
 $url = CRM_Utils_System::url( "civicrm/contact/view"  ,
 'reset=1&cid=' . $value ,
 $this->_absoluteUrl );
 $rows[$rowNum]['civicrm_contact_contact_source_link' ] = $url;
 $rows[$rowNum]['civicrm_contact_contact_source_hover'] = $onHover;
 }
 $entryFound = true;
 }
 }
 if ( array_key_exists( 'civicrm_contact_contact_assignee', $row ) &&
 $row['civicrm_activity_assignment_assignee_contact_id'] ) {
 $assignee = array( );
 //retrieve all contact assignees and build list with links
 require_once 'CRM/Activity/BAO/ActivityAssignment.php';
 $activity_assignment_ids = CRM_Activity_BAO_ActivityAssignment::getAssigneeNames( $row['civicrm_activity_id'], false, true );
 foreach ( $activity_assignment_ids as $cid => $assignee_name ) {
 if ( $viewLinks ) {
 $url = CRM_Utils_System::url( "civicrm/contact/view", 'reset=1&cid=' . $cid, $this->_absoluteUrl );
 $assignee[] = '<a title="'.$onHover.'" href="'.$url.'">'.$assignee_name.'</a>';
 } else {
 $assignee[] = $assignee_name;
 }
 }
 $rows[$rowNum]['civicrm_contact_contact_assignee'] = implode( '; ', $assignee );
 $entryFound = true;
 }

if ( array_key_exists('civicrm_activity_activity_type_id', $row ) ) {
 if ( $value = $row['civicrm_activity_activity_type_id'] ) {
 $rows[$rowNum]['civicrm_activity_activity_type_id'] = $activityType[$value];
 if ( $viewLinks ) {
 // case activities get a special view link
 if ( $rows[$rowNum]['civicrm_case_activity_case_id'] ) {
 $url = CRM_Utils_System::url( "civicrm/case/activity/view"  ,
 'reset=1&cid=' . $rows[$rowNum]['civicrm_contact_source_contact_id'] .
 '&aid=' . $rows[$rowNum]['civicrm_activity_id'] . '&caseID=' . $rows[$rowNum]['civicrm_case_activity_case_id'],
 $this->_absoluteUrl );
 } else {
 $url = CRM_Utils_System::url( "civicrm/contact/view/activity"  ,
 'action=view&reset=1&cid=' . $rows[$rowNum]['civicrm_contact_source_contact_id'] .
 '&id=' . $rows[$rowNum]['civicrm_activity_id'] . '&atype=' . $value ,
 $this->_absoluteUrl );
 }
 $rows[$rowNum]['civicrm_activity_activity_type_id_link'] = $url;
 $rows[$rowNum]['civicrm_activity_activity_type_id_hover'] = $onHoverAct;
 }
 $entryFound = true;
 }
 }

 if ( array_key_exists('civicrm_activity_status_id', $row ) ) {
 if ( $value = $row['civicrm_activity_status_id'] ) {
 $rows[$rowNum]['civicrm_activity_status_id'] = $activityStatus[$value];
 $entryFound = true;
 }
 }

 if ( !$entryFound ) {
 break;
 }
 }
 }
}

Name the file with the same name as the template file, and save it in the corresponding location in the custom_php directory. In my case, I saved the file as \sites\default\files\civicrm\custom_php\CRM\Report\Form\TargetActivity.php

5. Now that the necessary PHP and Template files have been created, the report can be registered using the CiviCRM interface. Go to Administer –> CiviReport –> Manage Templates and click on Register New Report Template. Name and describe your report, and make sure you supply the correct classname as you defined in the PHP file above. I completed the values as below:

image

6. Now that the new report template has been registered, you can create individual reports based on that template as usual, by going to Reports –> Create Report from Template. Individual reports based on the template can be added to the navigation dashboard alongside default CiviCRM reports:

image

This entry was posted in Drupal and tagged , . Bookmark the permalink.

4 Responses to CiviCRM – Creating a Custom Report

  1. Nice clear write-up! We have developed a mechanism for sharing “extensions” (new reports, custom searches, payment processors), and we will be launching a new “directory” where folks can register extensions as part of the upcoming 3.4 /4.0 release (beta1 out tomorrow). Would be great if you could package up your new report and share it as an extension! Keep an eye out on the CiviCRM blog (http://civicrm.org/blog) for a formal announcement in the next few weeks.

  2. Martyn Faller says:

    Useful to see a ‘worked example’. Even better if you could group the results by Target (rather than just source). I can’t quite see how to do it myself though…

    Thanks

  3. Utchay Endre says:

    This is a great help for me!
    A little bug fix: in lines 339, 375 and 380 “civicrm_contact_source_contact_id” should be replaced by: “civicrm_activity_source_contact_id”
    according to https://fisheye2.atlassian.com/changelog/CiviCRM?cs=33079
    Thank you very much indeed!

  4. Cat Paquin says:

    Thank you for posting this example! It really helps to step through what works for someone else.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s