Report for Unbatched Contributions

Question

We use Civi accounting batches to export our contributions to an external accounting program. The office manager has asked me to create a report (in Drupal or Civi) that will show all contributions that have not yet been put into an accounting batch. I've made some attempts, but have been unsuccessful. Any help would be greatly appreciated. (Drupal 7; Civi 4.6)

Drupal attempt:

We use CiviCRM Entity in a few different ways, especially in Views. We love the module! However, accounting batch data is not available in the current release, 7.x-2.0-beta3. A number of contribution and financial entities are now available in Views, but as far as I can tell, none of them make data from the Batch table available (either directly through their Fields or indirectly through Relationships).

CiviReport UI attempts:

Of the standard reports, it seems the Contribution Detail and Bookkeeping reports have access to Batch info already included. Of those two, only the Contribution Detail report has 'Batch Name' included in the filters. (The Extended Reports extension didn't seem to have any reports with extra/different options related to Batch data). The filter type is CRM_Report_Form::OP_MULTISELECT so the only two options are "Is one of" and "Is not one of". If I use "Is not one of" and select all of the Batch options, I can get the unbatched contributions, but that doesn't seem realistic, especially as the number of batches continues to grow. Is there a way to automatically select all of the options? Or do I need to dive into the report's code?

Answer 1

I found the solution by changing two lines of PHP for the Contribution Detail report (CRM\Report\Form\Contribute\Detail.php) and putting it in our Custom PHP directory.

The filters section for the batch name field reads by default (Civi 4.6.20) as follows:

  $this->_columns['civicrm_batch']['filters']['bid'] = array(
    'name' => 'id',
    'title' => ts('Batch Name'),
    'type' => CRM_Utils_Type::T_INT,
    'operatorType' => CRM_Report_Form::OP_MULTISELECT,
    'options' => $this->_allBatches,
  );

By changing the operator type and commenting out the options, I was able to get a widget with an "Is empty (Null)" option (thanks to the wiki, I knew to look in CRM_Report_Form for the possible values.) My code now reads:

  $this->_columns['civicrm_batch']['filters']['bid'] = array(
    'name' => 'id',
    'title' => ts('Batch Name'),
    'type' => CRM_Utils_Type::T_INT,
    'operatorType' => CRM_Report_Form::OP_INT,
    //'options' => $this->_allBatches,
  );