0
  • We are using Civi 4.6.18
  • WordPress 4.7.1
  • iATS is our paymentprocessor.
  • All donations use Price Sets so we use Line Items for our reports

Is there a way to get a report that groups Contributions by Deposit and includes the line Item info? I know this is probably an iATS extension issue but I thought I'd ask here in case some one already figured out a system.

We do not export each individual donation to our accounting programming as the Bookkeeping report show it. We just want each deposit with the total line item amounts for that deposit. Civi has the donation details so it is not needed in our accounting as well. Right now we have to pull an iATS report with the deposit details and then compare those donations to Civi to manually tally the Line Item Totals.

Improve this question
2
  • Can you clarify what you mean by 'by Deposit'? Is this an issue because the funds from iATS goes in to your bank as a lump sum on a daily basis or something?
    – petednz - fuzion ♦
    May 28, 2017 at 20:09
  • "By deposit" means how iATS grouped the contributions together and deposited them into our bank account. We do not enter every contribution into our accounting program, we just enter the deposit total broken down by fund (line item financial type). I posted our solution below.
    – Iowa Boy
    May 29, 2017 at 13:39

3 Answers 3

Reset to default
1

This is not an iATS Payments extension issue. Payment Processors like iATS Payments process the total amount - the details of how the total amount was arrived at - are within CiviCRM.

CiviCRM stores the line items in the the civicrm_line_item table it contains an entity reference to the civicrm_contribution table -> which has the trxn_id (Transaction ID).

So if you want to group by lineItems as opposed to by contributions you'll need to combine these two tables - and sum up the lineItems over a specific receive_date range (and/or for specific trxn_id-s).

Improve this answer
3
  • Karin, Is there something about the Transaction ID that would indicate a deposit batch? Receive date alone does not work because that day's donations could be divided by a Visa deposit, a Discover deposit, and ACH. Now that you pointed me in that direction, I see that there may be a pattern to the first 6 digits.
    – Iowa Boy
    Jan 25, 2017 at 2:42
  • Your credit card contributions via iATS aren't "batched", they get transacted immediately. There are two ways to match up things between iATS and CiviCRM - one is via the invoice id, and the other is using the transaction id.
    – Alan Dixon
    Apr 24, 2017 at 21:36
  • The invoice id is unique to the contribution and is passed to iATS as the invoice number, and the transaction id is generated by iATS and passed back to CiviCRM, where it gets stored in a few places, including the contribution table and the financial transaction table. The iATS extension appends a timestamp number to what it gets from iATS so that it is "more" unique. For the most part, each contribution has a single payment associated with it, but you'll see a few entries in the transaction table as CiviCRM does it's bookkeeping along the way.
    – Alan Dixon
    Apr 24, 2017 at 21:36
1

Here is what we have done. This works with iATS and using Price Sets (Line Items). Not sure how other payment processors group their deposits.

Because iATS does deposits based on these 3 divisions--ACH, Discover, MasterCard/Visa. Instead of just having 2 payment instruments Credit Card & ACH, I created a new one "Discover" and changed the current Credit Card to Visa/MasterCard (We also have ACH). iATS deposits the two CC divisions daily and the ACH weekly.

Now we can run Extended reports and filter by Payment Instrument. If you set the date for one week for the ACH and run a report for each day for the Discover and another for MC/Visa, you will get a "deposit" report with line item detail. The one thing that would improve this is if we could sort the two CC reports by Receive Date. Then you could run a date range and sort them by Receive Date and have each deposit in that date range rather than running a separate report for each day.

Hope this helps others.

Improve this answer
1
  • Thanks so much to eileenmcnaughton with Extended Reports!! He added the sort by Receive Date. This makes a very useful Bookkeeping report that groups by deposit.
    – Iowa Boy
    May 29, 2017 at 13:33
0

Please have a look at Fuzion's Extended Reports extension and see if it provides what you need. it offers a number of reports with Line Items.

Improve this answer
1
  • Already using and loving that extension. But there does not seem to be anything related to deposit batches.
    – Iowa Boy
    Jan 24, 2017 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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