Answers.Body: <p>I suggest you install the <a href="https://civicrm.org/extensions/extended-reports" rel="nofollow">extended reports extension</a> and see if one of those reports does what you want. The extension provides many reports and is well maintained.</p>
  
  <p>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.)</p>
  
  <p>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).</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-06T03:54:45.363Z
Answers.LastActivityDate: 2016-03-06T03:54:45.363Z
Answers.UserDisplayName: JoAnne
Answers.Body: <p>For myself, I've always found making reports a bloody nightmare!</p>
  
  <p>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.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-03-06T15:07:47.663Z
Answers.LastActivityDate: 2016-03-06T15:07:47.663Z
Answers.UserDisplayName: JohnFF
Body: <p>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. 
  <a href="https://alastaira.wordpress.com/2011/03/17/civicrm-creating-a-custom-report/" rel="nofollow">https://alastaira.wordpress.com/2011/03/17/civicrm-creating-a-custom-report/</a></p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <pre><code>public function statistics(&amp;$rows) {
  $statistics = parent::statistics($rows);
  $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_contribution');
  $select = "SELECT {$this-&gt;_aliases['civicrm_contribution']}.id, {$this-&gt;_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this-&gt;_aliases['civicrm_contribution']}.currency,
             CASE
               WHEN {$this-&gt;_aliases['civicrm_entity_financial_trxn']}_item.entity_id IS NOT NULL
               THEN {$this-&gt;_aliases['civicrm_entity_financial_trxn']}_item.amount
               ELSE {$this-&gt;_aliases['civicrm_entity_financial_trxn']}.amount
             END as amount
  ";
  
  $tempQuery = "CREATE TEMPORARY TABLE {$tempTableName} CHARACTER SET utf8 COLLATE utf8_unicode_ci AS
                {$select} {$this-&gt;_from} {$this-&gt;_where} {$this-&gt;_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-&gt;fetch()) {
    $amount[] = CRM_Utils_Money::format($dao-&gt;amount, $dao-&gt;currency);
    $avg[] = CRM_Utils_Money::format(round(($dao-&gt;amount /
      $dao-&gt;count), 2), $dao-&gt;currency);
  }
  
  $statistics['counts']['amount'] = array(
    'value' =&gt; implode(', ', $amount),
    'title' =&gt; 'Total Amount',
    'type' =&gt; CRM_Utils_Type::T_STRING,
  );
  $statistics['counts']['avg'] = array(
    'value' =&gt; implode(', ', $avg),
    'title' =&gt; 'Average',
    'type' =&gt; CRM_Utils_Type::T_STRING,
  );
  return $statistics;
  }
  </code></pre>
  
  <p>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: </p>
  
  <pre><code>$select = "SELECT {$this-&gt;_aliases['civicrm_contribution']}.id, {$this-&gt;_aliases['civicrm_entity_financial_trxn']}.id as trxnID, {$this-&gt;_aliases['civicrm_contribution']}.currency,
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-03-03T23:11:22.270Z
Comments.Text: I suggest you install the [extended reports extension](https://civicrm.org/extensions/extended-reports) and see if one of those reports provides what you want. The extension provides many reports and is well maintained.
Comments.UserDisplayName: JoAnne
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-03-06T01:39:28.203Z
Comments.Text: @JoAnne - I think this merits being an answer.
Comments.UserDisplayName: Coleman
Tags: <civireport><php>
Title: Coding Custom Report
ThreadText: # Coding Custom Report
  
  (Asked by: username_0 on 2016-03-03)
  
  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,
  
  **Tags:** civireport, php
  
  > **Comments:**
  > **username_1**: I suggest you install the [extended reports extension](https://civicrm.org/extensions/extended-reports) and see if one of those reports provides what you want. The extension provides many reports and is well maintained.  
  > **username_2**: @username_1 - I think this merits being an answer.  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2016-03-06. Score: 2 | Accepted answer
  
  I suggest you install the [extended reports extension](https://civicrm.org/extensions/extended-reports) 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 by username_3 on 2016-03-06. Score: 0
  
  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.
  
  
  ---
  
  