Quick and dirty CiviEvent reports in Drupal5

Posted: February 4th, 2009 | Author: chyatt | Filed under: Development, Drupal | Tags: civicrm, drupal | 1 Comment »

CiviCRM gets you lots of bang for your buck, especially when you integrate it with Drupal.  In fact, we recently released a CiviEvent/Drupal based project at work and were very pleased with the time to market.  However, we missed a few items during the requirments phase that left our client hanging.  Specifically, we needed to deliver some extra reports that CiviEvent doesn’t provide out-of-the-box.

Seasoned Drupal hackers will be screaming Views at this point.  The catch is that CiviEvents are not stored as Drupal nodes, so views won’t work.  (Note:  If we were using Drupal 6.x, we could use Views 2!)

So I set about researching other solutions.  Here’s a rundown:

• CiviReport – This is the solution recommended by the CiviCRM team.  Custom reports are provided to the end-user via the Eclipse BIRT plugin
• CiviCRM Custom Search – Another solution recommended by the CiviCRM team
• Embedded SQL in a Drupal Page

I loved the simplicity of the 3rd suggestion.  No need for report viewing software.  Canned reports can just be added as Drupal pages on an ongoing basis.  Access to these reports can be provided via Drupal roles.  It seemed to be in keeping with *the Drupal way*.

Here’s my approach:

1. Create a new basic Drupal content type “Canned Report”
2. Use the Content Access module to limit access to this content type (View-only for the roles I care about)
3. Set up a Menu to list each of the “Canned Reports” – display this menu for only the roles I care about
4. Create content -> Canned Report for each of the reports my customer requires

For example:

1. Participants by Event

This report is similar to the one you get when you click CiviEvent Summary in CiviCRM.  However, it lists all events instead of the most recent:

Screenshots:

• Creating the Participants by Event Report
• Viewing the Participants by Event Report

PHP Code:

<?php
$rpt  = "<table class='canned_report'><tr>";
$rpt .= "<th>Event ID</th>";
$rpt .= "<th>Title</th>";
$rpt .= "<th>Start</th>";
$rpt .= "<th>End</th>";
$rpt .= "<th>Max. Participants</th>";
$rpt .= "<th>Total Participants</th></tr>";

$qry  = "select ev.id, ev.title, ev.start_date, ";
$qry .= "ev.end_date, ev.max_participants, ";
$qry .= "count(par.id) 'participants' ";
$qry .= "from civicrm_event as ev ";
$qry .= "left join civicrm_participant as par ";
$qry .= "on ev.id = par.event_id ";
$qry .= "group by ev.id order by ev.start_date";

$rptqry = db_query(db_rewrite_sql($qry));

while ($rptrow = db_fetch_array($rptqry)) {
   $rpt .= "<tr><td>";
   $rpt .= $rptrow["id"] . "</td><td>";
   $rpt .= $rptrow["title"] . "</td><td>";
   $rpt .= $rptrow["start_date"] . "</td><td>";
   $rpt .= $rptrow["end_date"] . "</td><td>";
   $rpt .= $rptrow["max_participants"] . "</td><td>";
   $rpt .= $rptrow["participants"] . "</td></tr>";
}
$rpt .= "</table>";
print $rpt;
?>

2. All Events by Start Date

This report is just a simple list of all CiviEvents ordered by start date.

Screenshots:

• Creating the All Events by Start Date Report
• Viewing the All Events by Start Date Report

PHP Code:

<?php
$rpt  = "<table class='canned_report'><tr>";
$rpt .= "<th>Event ID</th>";
$rpt .= "<th>Title</th>";
$rpt .= "<th>Start Date</th>";
$rpt .= "<th>Start Time</th></tr>";

$qry  = "select ev.id, ev.title, ev.start_date ";
$qry .= "from civicrm_event as ev order by ev.start_date";

$rptqry = db_query(db_rewrite_sql($qry));

while ($rptrow = db_fetch_array($rptqry)) {
   $start_date = explode(" ", $rptrow["start_date"]);
   $rpt .= "<tr><td>";
   $rpt .= $rptrow["id"] . "</td><td>";
   $rpt .= $rptrow["title"] . "</td><td>";
   $rpt .= $start_date[0]  . "</td><td>";
   $rpt .= $start_date[1] . "</td></tr>";
}
$rpt .= "</table>";
print $rpt;
?>

Notice that I included a class=”canned_report” in the table definition in the above examples.  This allows you to easily style the reports using CSS. For our client, I simply chose to let the current Drupal theme handle the styling.

Hat tip

---