Coding Custom Report

Question

I'm having some trouble coding a custom report. I found the following tutorial which was helpful, but I'm having trouble with the actual coding as I am still new to civiCRM developing, and I haven't figured out all of the civiCRM classes. https://alastaira.wordpress.com/2011/03/17/civicrm-creating-a-custom-report/

Essentially, I want a report that does exactly what the Bookkeeping report does, except at the bottom I want a summation of the amount grouped by the financial account code.

I have copied the Bookkeeping.tpl and Bookkeping.php to new files. Yet, I'm having some troubles on how exactly to change the statistics section of the php file to reflect my desired changes.

public function statistics(&$rows) {
$statistics = parent::statistics($rows);
$tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution');
$select = "SELECT {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this->_aliases['civicrm_contribution']}.currency,
           CASE
             WHEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
             THEN {$this->_aliases['civicrm_entity_financial_trxn']}_item.amount
             ELSE {$this->_aliases['civicrm_entity_financial_trxn']}.amount
           END as amount
";

$tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
              {$select} {$this->_from} {$this->_where} {$this->_groupBy} ";
CRM_Core_DAO::executeQuery($tempQuery);

$sql = "SELECT COUNT(trxnID) as count, SUM(amount) as amount, currency
        FROM {$tempTableName}
        GROUP BY currency";
$dao = CRM_Core_DAO::executeQuery($sql);
$amount = $avg = array();
while ($dao->fetch()) {
  $amount[] = CRM_Utils_Money::format($dao->amount, $dao->currency);
  $avg[] = CRM_Utils_Money::format(round(($dao->amount /
    $dao->count), 2), $dao->currency);
}

$statistics['counts']['amount'] = array(
  'value' => implode(', ', $amount),
  'title' => 'Total Amount',
  'type' => CRM_Utils_Type::T_STRING,
);
$statistics['counts']['avg'] = array(
  'value' => implode(', ', $avg),
  'title' => 'Average',
  'type' => CRM_Utils_Type::T_STRING,
);
return $statistics;
}

I see in the code that the $sql is grouped by currency, so I essentially just need to change it so the $sql instead is grouped by financial code. However, I don't know the class name for the financial code to place in the following line of code to replace currency:

$select = "SELECT {$this->_aliases['civicrm_contribution']}.id, {$this->_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this->_aliases['civicrm_contribution']}.currency,

Answer 1

I suggest you install the extended reports extension and see if one of those reports does what you want. The extension provides many reports and is well maintained.

If it doesn't provide what you want, perhaps reviewing the code of extended reports that do group by financial account code will help you find the answer to your problem. (Sorry I am not a coder, so can't provide any help with that.)

Also are you sure you need the information on the individual transactions and the account code totals in the same report. My understanding is that often it easier to do one or the other on a given report, rather than both. That is why there are summary and detail pairs of reports. (eg for Donor Details and Event Income).

Answer 2

For myself, I've always found making reports a bloody nightmare!

Far easier is to build an extension that spews out the data. Unless you 100% definitely need the flexibility the form provides and you have a great example to work from, I'd recommend extension development.