Custom Reports - A guide for writing reports on contact and case data

Below is a step by step process for how we created custom reports. Please see the attached php code sample as well as an example screen shot of what the code sample returns.

Section I: Set up file structure

       * The file paths below assume a default system installation *

1. Find an existing report that is similar to what you want yours to look like.
   These can be found under:  C:\xampp\htdocs\drupal\sites\all\modules\civicrm\CRM\Report\Form\
   There are several folders here each containing different types of reports. In here you are looking for a PHP file.
   For example, you click on the Contact folder and grab the report Summary.php. Your full file path is:C:\xampp\htdocs\drupal\sites\all\modules\civicrm\CRM\Report\Form\Contact
   
   
2. Set up your custom PHP file structure.
   In the custom_php directory (C:\xampp\htdocs\custom_php), create a file structure that matches the report file structure from step one: C:\xampp\htdocs\custom_php\CRM\Report\Form\Contact
   
   
3. Copy the PHP file (Summary.php) from:
   C:\xampp\htdocs\drupal\sites\all\modules\civicrm\CRM\Report\Form\Contact
   And paste it to:
   C:\xampp\htdocs\custom_php\CRM\Report\Form\Contact\
   Rename the copied file appropriate to the report you are going to create. For instance, the file for a report that summarizes demographics by area file might be named AreaDemoSummary.php.
   
   
4. Open your newly named file in the custom_php directory (ex. AreaDemoSummary.php) with a plain-text editor.
   At the top of the file there's a line that reads:
   class CRM_Report_Form_Contact_Summary extends CRM_Report_Form {
   Change your class name so that it matches your file as so:
   class CRM_Report_Form_Contact_AreaDemoSummary extends CRM_Report_Form
   
   
5. Find the matching PHP template.
   All templates are stored in:
   C:\xampp\htdocs\drupal\sites\all\modules\civicrm\templates\CRM. Once here, it's easy to find where your template is stored because the file structure mirrors the file structure from steps 1 and 2.
   For the AreaDemoSummary report example, it's located at:    C:\xampp\htdocs\drupal\sites\all\modules\civicrm\templates\CRM\Report\Form\Contact  Copy the Summary.tpl file.
   
   
6. Set up your custom templates directory. The base directory (C:\xampp\htdocs\civicrm_custom) already exists. Again, we need to recreate the file structure for where our custom template is to be saved.
   So create the file structure as:
   C:\xampp\htdocs\civicrm_custom\CRM\Report\Form\Contact
   
   
7. Paste the .tpl file you copied in step 5 here. Rename it to match your PHP file. For the AreaDemoSummary example, the file is renamed to AreaDemoSummary.tpl.
   
   Note: There will only be one line in the .tpl  which looks like this:

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

   
   
8. To add your template, go to the top navigation menu in the application:
   Administer >> CiviReport >> Manage Templates
   Click the button "Register New Report Template." Fill in the necessary values*.
   
   * Note: Just a side note about the URL field, this is what will be appended to /civicrm/report/ in the URL. In the example, this becomes contact/areademosummary, so the full URL when the example report is viewed is: http://<server>/drupal/civicrm/report/contact/areademosummary

* Note: When entering a value in the "class" field, it is very important you follow the format suggested for that field (e.g., CRM_Report_Form_Contribute_[yourClassName]). This gets translated into the actual path to your custom .tpl file (i.e., civicrm_custom/CRM/Report/Form/Contribute/yourClassName.tpl).

1. To verify the report configuration, Navigate to Reports --> Create Reports from Templates.
   Once here, you should see your report. For the AreaDemoSummary example, it appeared under Contact Report Templates. If yours is here, you have done everything correctly.
   
   

Section II: Creating New Reports.

1. To write reports you will need to have basic PHP and mySQL knowledge.  The reports work by creating queries in MySQL and then passing the results of those queries into PHP arrays. We recommend using our finished custom report as a template/place to start.
   
   
2. The SQL Queries. This is the more difficult part due to the database complexities.  We recommend you run the queries in PhpMyAdmin, if using XAMPP, until you get the desired results, i.e. a correct query. First, I will explain a few queries that are already complete and can be reused (A), and then I will explain how to modify the existing queries to get desired results (B).
   
             A. Queries that can be reused:
   
   
   1. Get all case Ids: This query returns all case Ids. This is used for iterating through all cases to grab necessary data later on, such as case names, counts of individuals in each category, and custom data needed. Cases have an option_group_id of 27 within CiviCRM.

      SELECT cov.value FROM civicrm_option_value_en_oo cov WHERE cov.option_group_id=27 ORDER BY cov.value

      
      
      
   2. Get all activity names: This query returns the names of all activities, option_group_id of 2, in the system. This is used for iterating through all activities to grab necessary data later on, such as storing the proper activity names, counts of individuals in each category, and custom data needed.

      SELECT cov.label FROM civicrm_option_value_en_oo cov WHERE cov.option_group_id=2 ORDER BY cov.label

      
      
      
   3. Create temporary table: This query creates a temporary table which is used for storing the results of your insert query (insert query to be discussed later). Be sure to name your temporary table accordingly (not necessary, but for readability).

      CREATE TEMPORARY TABLE IF NOT EXISTS ywcaHealthMembershipDemographics1 (id INT not null auto_increment, case varchar(50) not null, total int not null, membership varchar(50) not null, gender varchar(12) not null, PRIMARY KEY(id))

      
      
      
   4. Final data query to be parsed by PHP: Note, this is just an example and the table name needs to match the temporary table you created. This query returns all of your custom data needed for report, as set up in your temporary table. This will be assigned to an array and then parsed by the PHP code.

      select * from ywcaHealthMembershipDemographics1

      B. Queries that need to be modified:
      
      
   5. Case insert query: This query inserts all of the data found into your temporary table based on the query's SELECT statement. This is the largest query and similar conditions (found in the WHERE clause) will need to be in both of the queries in this section. See below for example of one insert query.
      
      

      INSERT INTO membershipDemographics1 (program, total, membership, gender) SELECT cov.label AS program, Count( DISTINCT contact.id ) AS total, cvyi.ywca_member_746 AS membership, dd.gender_616 AS gender FROM civicrm_case cc, civicrm_contact contact, civicrm_value_demographic_data_20 dd, civicrm_option_value_en_oo cov, civicrm_value_ywca_involvment_15 cvyi, civicrm_case_contact ccc WHERE cc.case_type_id LIKE '%".$value."%' AND cc.id = ccc.case_id AND contact.id = ccc.contact_id AND contact.id = cvyi.entity_id AND contact.id = dd.entity_id AND cov.option_group_id=27 AND cov.value =".$value." AND cc.is_deleted = 0 AND (substring(cc.start_date,6,2) +0)<='$userMonth' AND (substring(cc.start_date,0,4) +0)<='$userYear' GROUP BY cvyi.ywca_member_746, dd.gender_616

      
      
      
   6. Activities insert query: This query inserts all of the activity data found into you temporary table based on the query's SELECT statement. This query will have similar conditions (found in the WHERE clause) that needs to be in both of the queries in this section. See below for example of one insert query. INSERT INTO membershipDemographics1 (program, total, membership, gender)SELECT cov.label AS program, Count( DISTINCT contact.id ) AS total,cvyi.ywca_member_746 AS membership, dd.gender_616 AS genderFROM civicrm_contact contact, civicrm_option_value_en_oo cov, civicrm_activity_target target,civicrm_activity act, civicrm_value_demographic_data_20 dd, civicrm_value_ywca_involvment_15 cvyiWHERE cov.label = '$value2'AND target.activity_id = act.idAND act.activity_type_id = cov.valueAND contact.id = dd.entity_idAND contact.id = cvyi.entity_idAND act.is_current_revision = 1AND act.is_deleted = 0AND target.target_contact_id = contact.idAND (substring(act.activity_date_time,6,2)+0)<='$userMonth'AND (substring(act.activity_date_time,0,4)+0)<='$userYear'GROUP BY cvyi.ywca_member_746, dd.date_of_birth_619
      
      
3. The PHP code: The main algorithm for displaying the data on the report has already been written by us. Below is a basic explanation of the algorithm in the postProcess() function .
   
   
   1. Function __construct(). This function allows you to add report criteria. Some reports we have already included have basic report criteria allowing the user to select the month and year they would like to report on.  We have created other reports with more complex criteria such as user names (and an All option).  This works by writing a query (to select all people in a specific program that we are reporting on for example) and passing the result set array into the criteria field.  We would then add a line to the query to check and make sure the username is equal to the selected user
      
      
   2. Outer while loop. The loop iterates one row at time via fetches from our temporary table. The way they're stored in the temporary table is such that they are ordered, first by case name, then by whatever data you  were trying to get out. There may be multiple rows for any case/activity. See row example in letter c.
      
      
   3. Assign a column header and to temporary array $row. The column header is the column that will actually be displayed on the report. The temporary array $row is cleared before this is done. Within here, $row[$key] is assigned the value from the fetch. Here is an example of $row:Array ( [case] => YWCA Health [total] => 1 [membership] => no [gender] => Male )
      
      
   4. Iterate through the values in $row[$key] using for loop. Since the temporary array contains multiple values, we need to get those values out, and manipulate them depending on the value. The manipulation is based on keys of $row. Another temporary array is used within this portion of code, $caseArray, which stores the totals for a specific case. Because there can be multiple entries in $row for each case/activity, we want to grab all of those values, and total them. These values are stored in our temporary case Array. So when the $key of $row is a new case, this $caseArray is added to our third array, $rows, and then cleared. The array $rows will contain the totals for each case/activity. Each case/activity is only in this array once and the values from this array will be displayed on report.
      
      
   5. Final check to make sure last activity isn't forgotten in report. At the end of our large while loop, we need to be sure not to forget the last activity which is held in the $caseArray. Simply add it to $rows.
      
      
   6. $this->doTemplateAssignment($rows); Assigns the rows from $rows array to their places in the template to be displayed on the report.
      
      
   7. $this->endPostProcess($rows); A required line in CiviCRM. Ends the postProcess() function.