6

Is there support for filtering contribution reports by payment processor?

We have credit card (Authorize.NET) and PayPal (new). How can I report on just PayPal transactions?

The only difference I see is in the transaction ID format. Both payment methods say 'credit card' on the contribution.

5

There are no such reports in CiviCRM that will filter Contribution by Payment Processor. Payment Processor and Payment Method have different meaning. Payment method can be used by N processor. Here are some solutions

  1. Going forward have your system to use unique payment method for a payment processor with same name and then you can filter the report using Payment method. So that in future it can be tracked easily using payment method. However, for historical data I won't recommend you to update the Payment method since you will need to update in two tables i.e civicrm_contribution.payment_instrument_id and civicrm_financial_trxn.payment_instrument_id. Changes to civicrm_financial_trxn might impact on Bookkeeping entries for those which are exported in batch and closed for accounting period.

  2. Build a custom report for contribution having Payment processor as filter and/or column.

  3. Use AlterReportVar hook to add filter and/or column for existing contribution reports and build query accordingly.

The basic query to retrieve contribution done using payment processor is

SELECT cc.id FROM civicrm_contribution cc
  INNER JOIN civicrm_entity_financial_trxn ceft
    ON ceft.entity_id = cc.id
      AND ceft.entity_table = 'civicrm_contribution'
  INNER JOIN civicrm_financial_trxn cft 
    ON cft.id = ceft.financial_trxn_id
WHERE cft.payment_processor_id = 1; 
3

I had a client today with the same issue, so I added this functionality. Thanks to Pradeep's answer I was able to implement this in about 10 minutes.

Version 4.4+ of the Extended Report extension contains this improvement. All reports that can use financial transactions - e.g. "Bookkeeping Report with Extra Fields" - can now include, filter, sort and group by payment processor.

1

Change the Name so you can differentiate them. Got to Administer > CiviContribute > Payment Processors. You can click edit and change the name of your PayPal credit card processor to PP Credit Card (or whatever you want). This will work for going forward. You will have to update the contribution table to change the historical contributions.

enter image description here

enter image description here

enter image description here

  • This will not work. The payment processors are already named differently and you cannot search/report on transactions for a given payment processor. – Justin Moore Jan 2 '18 at 13:21
  • This works for us. I do not have 2 Processors but we do have 2 credit card options and we use native reports to view them. But the results would be the same as I show. I added screenshots to my answer showing it working. – Iowa Boy Jan 3 '18 at 16:16
  • I'm guessing right now if you Go to Administer > CiviContribute > Payment Processors you will see Credit Card twice, one with PayPal and one with AuthorizeNet as the Processor Type. If you edit these and rename them, I'm almost certain it will rename all of the historical transactions in your data base. You may want to rename your ACH so they are also different. You might not be pulling reports directly by Payment Processor but you can select the Payment Method/Payment Type (depending on which report you are using) to view the transactions for the Payment Processor of your choice. – Iowa Boy Jan 3 '18 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.