Tips and tricks for creating custom report templates

[Mark Tompsett]

Having now written my first custom report template from scratch I decided to share the points I learned from the experience as tips and tricks that others may benefit from.

First point to make is that when considering tables to report from, you can report from views as well as tables in the the database.  I created a few views in the database to make the reporting easier, as some of the ways I needed the data to be summarised required some tricky SQL, and I may need that summarised data in more than one place (ie not just for this report template) so creating a few views seemed a sensible thing to do.
(In case you're wondering why it can be useful to have hidden columns in reports, they come in very useful for constructing URLs - see below.)

A quirk of CiviReport that I came across was that in one of my views I had a column called 'activity_id' and tried to include it in my report but as a hidden column..

Code: [Select]

'fields' => array(
'activity_id' => array(
'required'   => true,
'no_display' => true,
),.. but despite the 'no_display' being true, the column was displayed in any case, so I changed the name of the column to 'id' and it then did not display.  I guess this may be a bug in CiviReport but this is the simple workaround.

The most useful tips and tricks I can offer are in the formatting of reports, in the alterDisplay() function.
For instance, you may want your dates displayed in a particular way (by defauilt date/times ar displayed as eg '2012-01-23 12:34:56') but I wanted them displayed in dd-mmm-yyyy format (eg '23-Jan-2012') so..

Code: [Select]

if ( array_key_exists('v_contacts_created_created_date', $row) ) {
$value = $rows[$rowNum]['v_contacts_created_created_date'];
$rows[$rowNum]['v_contacts_created_created_date'] = CRM_Utils_Date::customFormat($value, '%d-%b-%Y');
$entryFound = true;
}.. is an example of this.

I wanted to display the Contact Type but not in text eg 'Individual', 'Organization', etc., but as an icon, and I found this simple trick for doing so..

Code: [Select]

if ( array_key_exists('civicrm_contact_contact_type', $row) ) {
$value = $rows[$rowNum]['civicrm_contact_contact_type'];
$rows[$rowNum]['civicrm_contact_contact_type'] = '<span class="icon crm-icon ' . $value . '-icon"></span>';
$entryFound = true;
}
To construct a URL, in this case to a Contact record, use a technique like this ..

Code: [Select]

if ( array_key_exists('civicrm_contact_display_name', $row) && array_key_exists('civicrm_contact_id', $row) ) {
                $url = CRM_Utils_System::url( 'civicrm/contact/view', 'reset=1&cid=' . $row['civicrm_contact_id'],
$this->_absoluteUrl );
                $rows[$rowNum]['civicrm_contact_display_name_link' ] = $url;
                $rows[$rowNum]['civicrm_contact_display_name_hover'] = ts("View details for this Contact.");
                $entryFound = true;
            }

I had postcodes for my Contacts and I wanted a simple way to show them on a map directly from the repotr, so here is what I did...

Code: [Select]

if ( array_key_exists('v_primary_address_postal_code', $row) ) {
$value = $rows[$rowNum]['v_primary_address_postal_code'];
if ( $value != '' ) {
$rows[$rowNum]['v_primary_address_postal_code' ] = '<a href="http://maps.google.com/maps?q=' . str_replace(' ', '+', $value)
. '" title="View Google Maps for this post code (opens in new browser window)" target="_blank">' . $value . '</a>';
}
$entryFound = true;
}
You can see that I created a view called 'v_primary_address' because it was going to be useful in many places, not just this report, and I have adopted the naming convention  of starting all my view names 'v_' to distinguish them.  You can also see that the Google Maps display of the postcode opens in a new browser window.

Lastly, you may know of some of these resources already, but if you don't here are some links to some resources that I found useful and instructive when writing my first custom report template from scratch...

• http://wiki.civicrm.org/confluence/display/CRMDOC40/CiviReport+structure+and+customization - this is really useful but it omits to mention that you can have an orderBy function, although it does mention that you can have an array of orderBys;
• http://alastaira.wordpress.com/2011/03/17/civicrm-creating-a-custom-report/ - from Alastair Aitchison
• http://artfulrobot.com/civicrm-custom-birthdays-report - from Rich Lott
• http://phpxref.com/xref/civicrm/nav.html?CRM/Utils/index.html - maybe a little out of date as it relates to CiviCRM version 1.4.5222 (Generated: Sun Jul 20 16:50:16 2008) but very useful anyway

If you found this post useful, please [applaud] me!   

[Eileen]

clap clap :-)

I've written quite a few & have increasingly moved to using a very building block approach - so each alterDisplay action I use has a separate function making it easy to re-use in other reports.

ie. I would potentially add your icon code into a function

alterContactType() & add it to the Form_Extended function being discussed here

http://issues.civicrm.org/jira/browse/CRM-9501

[Mark Tompsett]

Thanks Eileen, I'm flattered 

Where would I find this CRM_Report_Form_Extended class?
In which release of CiviCRM might it appear?

Mark 

[Eileen]

It's still in the idea phase - but if you look at the JIRA issue in question you can see the code under the 'Fisheye' tab

[Mark Tompsett]

Please be aware that the trick I described for formatting dates using

Code: [Select]

CRM_Utils_Date::customFormat may only work if the report is from a view of the database, rather than a table.   
As and when I find a more comprehensive solution to that I will post it here.

Mark