CiviCRM is a full featured database right out of the box, and has lots of ways to extend it. For example, you can create extra set of fields for certain types of contacts, and these can even have a one-to-many relationship. Here's how I ran a report on from this extended dataset.

My example is taken from a charity client that provides physio to children. Children are the focus, but being children, the organisation deals mainly with their families/guardians. A field set was created to store the information required on children, which includes name and date of birth.

My client wants to make sure they remember children's birthdays, and asked for a report that showed up-coming birthdays.

A simple request.

CiviCRM comes with loads of ways to report on data, but as is so often the case with "simple requests", this was going to need some custom coding. Now if you had a table called parents and a table called children, this would be trivial in SQL:

SELECT c.dob, c.name, p.name
  FROM children c, parents p
 WHERE c.parent_id = p.id
 ORDER BY (dates excluding year, 
           showing this month 
           first - see code below!);

But if you don't know the table names, or the fieldnames within the table, it's more tricky. Add a hugely complex SQL abstraction layer and you get to something as obfuscated as seen below. The learning for me was about how CiviCRM handles and stores custom data.

How to we bring in the custom field sets columns?

protected $_customGroupExtends = array( 'Individual'  );

This property tells CRM_Report_Form to work in all the SQL joins necessary for all custom groups.

CiviCRM assigns an alias to all fields, which can be accessed through the _columns property. For example, the civicrm_contact.id field will be known by the value of:

{$this->_columns['civicrm_contact']['fields']['id']['dbAlias']}

So even when the table and field names are obvious, you need to access them this way. Now the fun starts with the custom field sets' (custom groups') fields.

When you create a custom field set a new table is created to hold the data. To find the name of the table you can either take a peek at the tables in your CiviCRM database, or use a lookup function (see my other posting: How to get table info on a custom group).

Using alterDisplay() to re-work the recordset before presentation

Screenshot of finished report

I could get most of what I wanted through from the database query, but I really wanted the child's name to link nicely through to their guardian's contact record. For this purpose I included this contact Id in the SELECT clause, then used alterDisplay to replace the child's name field with HTML for the link. alterDisplay() also removes the fetched Id field as nobody needs to see that.

Using the report

Once the code was written, I registered the report type at civicrm/admin/report/register, then created a report, ticking the allow-dashboard box, and finally, added it to the dashboard. I have experienced some odd problems with using it on the dashboard, but these go away by clicking the Refresh Dashboard Data button.

Further/preliminary reading: Note: I have not covered the foundation stuff here - what files go where. Take a look at the CiviCRM Wiki (4.3 version) [4.0 version] for a primer on that.

Here's my code

This file is /path/to/my/civicrm/customisations/CRM/Report/Form/Contact/ChildBirthdays.php

<?php

/**
 * {GPL copyright info goes here}
 * Nb. this report was originally based on a core CiviCRM report.
 *     There may be some unnecessary code left over in it.
 * 
 * @package CRM
 * @author Rich Lott / Artful Robot
 */

require_once 'CRM/Report/Form.php';

class CRM_Report_Form_Contact_ChildBirthdays extends CRM_Report_Form 
{
  protected $_customGroupExtends = array( 'Individual'  );

  function __construct( ) 
  {
    $this->_columns = 
      array( 
        'civicrm_contact' => array( 
            'dao'       => 'CRM_Contact_DAO_Contact',
            'fields'    => array( 
              'display_name' => array( 
                'title'     => ts( 'Contact Name' ),
                'required'  => true,
                'no_repeat' => true ),
              'id'           => array( 
                'no_display'=> true,
                'required'  => true ),),)
      );

    parent::__construct( );
  }

  function buildQuery()
  {
    $sql = parent::buildQuery();
    return $sql;
  } 

  function preProcess( ) {
    parent::preProcess( );
  }

  function select( ) 
  {
    $this->_select =
      "SELECT {$this->_columns['civicrm_contact']['fields']['id']['dbAlias']} 
         AS guardian_id, 
        {$this->_columns['civicrm_value_child_2']['fields']['custom_2']['dbAlias']} 
         AS child_name, 
        DATE_FORMAT(
         {$this->_columns['civicrm_value_child_2']['fields']['custom_3']['dbAlias']},
         '%D %b') AS child_dob ";

    $this->_columnHeaders = array(
      'guardian_id' => array('type'=>0,'title'=> 'Id'),
      'child_name'  => array('type'=>0,'title'=>'Child'),
      'child_dob'   => array('type'=>0,'title'=>'Birthday')
    );
    $this->_columns['civicrm_contact']['fields']['id']['no_display'] = true;
  } 

 function from( ) {
    $this->_from = "
      FROM civicrm_contact 
        {$this->_aliases['civicrm_contact']} 
        {$this->_aclFrom}";
  }

  function where( ) 
  {
    parent::where();
    $this->_where .= " AND value_child_2_civireport.child_s_dob_3 IS NOT NULL
      AND COALESCE(value_child_2_civireport.child_deceased_21,0) = 0";
  }

  function groupBy( ) {
    $this->_groupBy = '';
  }


  function orderBy( ) 
  {
    $this->_orderBy = "ORDER BY 
        (MONTH(value_child_2_civireport.child_s_dob_3)
           - MONTH(CURRENT_DATE) + 12) mod 12,
        DAYOFMONTH(value_child_2_civireport.child_s_dob_3)";
  } 
  static function formRule( $fields, $files, $self ) {
    $errors = $grouping = array( );
    return $errors;
  }

  function postProcess( ) 
  {
    $this->beginPostProcess( );

    // get the acl clauses built before we assemble the query
    $this->buildACLClause( $this->_aliases['civicrm_contact'] );

    $sql  = $this->buildQuery( true );

    $rows = $graphRows = array();
    $this->buildRows ( $sql, $rows );

    $this->formatDisplay( $rows );
    $this->doTemplateAssignment( $rows );
    $this->endPostProcess( $rows ); 
  }

  function alterDisplay( &$rows ) 
  {
    // custom code to alter rows
    $entryFound = false;
    foreach ( $rows as $rowNum => $row ) {
      // make count columns point to contact
      // convert display name to links
      if ( array_key_exists('child_name', $row) && 
        array_key_exists('guardian_id', $row) ) 
      {
        // Generate URL
        $url = CRM_Utils_System::url( "civicrm/contact/view",
          'reset=1&cid=' . $row['guardian_id'],
          $this->_absoluteUrl );

        // alter content
        $rows[$rowNum]['child_name' ] = 
            "<a href='$url' title=\"See child's guardian's record\" >"
            . htmlspecialchars($row['child_name']) . "</a>";

        // we don't need this anymore
        unset($rows[$rowNum]['guardian_id']);
        $entryFound = true;
      }
      // we don't need the user to see this column 
      unset($this->_columnHeaders['guardian_id']);

      // skip looking further in rows, if first row itself doesn't 
      // have the column we need
      if ( !$entryFound ) break;
    }
  }
}