Answers.Body: <p>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.</p>
  
  <p>Because iATS does deposits based on these 3 divisions--ACH, Discover, MasterCard/Visa. Instead of just having 2 payment instruments Credit Card &amp; 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.</p>
  
  <p>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.</p>
  
  <p>Hope this helps others.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-29T13:33:58.530Z
Answers.Text: 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.
Answers.UserDisplayName: Iowa Boy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-05-28T14:43:59.627Z
Answers.LastActivityDate: 2017-05-28T14:43:59.627Z
Answers.UserDisplayName: Iowa Boy
Answers.Body: <p>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.</p>
  
  <p>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). </p>
  
  <p>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). </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-25T02:42:59.113Z
Answers.Text: 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.
Answers.UserDisplayName: Iowa Boy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-04-24T21:36:10.923Z
Answers.Text: 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.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-04-24T21:36:16.420Z
Answers.Text: 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.
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-24T17:12:45.223Z
Answers.LastActivityDate: 2017-01-24T17:12:45.223Z
Answers.UserDisplayName: KarinG - Semper IT
Answers.Body: <p>Please have a look at Fuzion's <a href="https://github.com/eileenmcnaughton/nz.co.fuzion.extendedreport" rel="nofollow noreferrer">Extended Reports</a> extension and see if it provides what you need. it offers a number of reports with Line Items.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-24T03:34:28.140Z
Answers.Text: Already using and loving that extension. But there does not seem to be anything related to deposit batches.
Answers.UserDisplayName: Iowa Boy
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-01-24T01:17:57.880Z
Answers.LastActivityDate: 2017-01-24T01:17:57.880Z
Answers.UserDisplayName: petednz - fuzion
Body: <ul>
  <li>We are using Civi 4.6.18 </li>
  <li>WordPress 4.7.1  </li>
  <li>iATS is our paymentprocessor.  </li>
  <li>All donations use Price Sets so we use Line Items for our reports</li>
  </ul>
  
  <p>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.</p>
  
  <p>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. </p>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-05-28T20:09:54.997Z
Comments.Text: 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?
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-05-29T13:39:24.023Z
Comments.Text: "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.
Comments.UserDisplayName: Iowa Boy
Tags: <reports><iats><bookkeeping>
Title: Bookkeeping report that includes grouping by deposit and Line Item Totals
ThreadText: # Bookkeeping report that includes grouping by deposit and Line Item Totals
  
  (Asked by: username_0 on 2017-01-23)
  
  * 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.
  
  **Tags:** reports, iats, bookkeeping
  
  > **Comments:**
  > **username_1**: 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?  
  > **username_0**: "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.  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_0 on 2017-05-28. Score: 1 | Accepted answer
  
  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.
  
  > **Comments:**
  > **username_0**: 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.  
  
  ---
  
  ### Answer by username_2 on 2017-01-24. Score: 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).
  
  > **Comments:**
  > **username_0**: 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.  
  > **username_3**: 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.  
  > **username_3**: 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.  
  
  ---
  
  ### Answer by username_1 on 2017-01-24. Score: 0
  
  Please have a look at username_1's [Extended Reports](https://github.com/eileenmcnaughton/nz.co.username_1.extendedreport) extension and see if it provides what you need. it offers a number of reports with Line Items.
  
  > **Comments:**
  > **username_0**: Already using and loving that extension. But there does not seem to be anything related to deposit batches.  
  
  ---
  
  