Knowledgebase
[QODBC-Desktop] Troubleshooting - How can I combine two reports and get the report of transactions of a customer.
Posted by Jack - QODBC Support on 26 December 2017 10:31 AM

Troubleshooting - How can I combine two reports and get the report of transactions of a customer.

Problem Description:

I need a report by particular customer showing the beginning balance, payments made by the customer, invoices with details showing the expense and the ending balance. QuickBooks does not provide enough detail in the customer statements so that they can be read by the parents. I would also like to be able to email the statement to an email address.

Solution:

You need to query different table & report for getting required data. Once you get the data you need to combine all data.

You need to query CustomerBalanceDetail report & InvoiceLine, CreditMemoLine, ReceivePaymentLine, CheckExpenseLine table for particular Customer.

You can use MS Excel for this & can write VBA code in MS Excel which will query above table & report for particular Customer & store result on the separate sheet. Once you get the result on the separate sheet, then you need to combine all sheet data & paste it into the new sheet.

We have created sample MS Excel macro which will fetch required data. We have created total 7 sheets (i.e. Sheet0 to Sheet6). Sheet0 will be input form & Sheet1 to Sheet5 will contain data from CustomerBalanceDetail report & InvoiceLine, CreditMemoLine, ReceivePaymentLine, CheckExpenseLine table for particular Customer and Sheet6 will have combine data of Sheet1 to Sheet5.

Sheet0 will be input form using this you need to enter Customer FullName, StartDate & EndDate & click on "Generate Report" button. In this example, I have used "Abercrombie, Kristy:Remodel Bathroom" as Customer FullName & 1/1/2016 as start date and 12/31/2022 as end date.

On clicking on "Generate Report" button we will query report & table for particular Customer & store result in Sheet1 to Sheet5.

Sheet1 will contain the result of CustomerBalanceDetail report.

Query used for fetching data for Sheet1 is:

sp_report CustomerBalanceDetail show Text, Blank, TxnType as Type, Date, RefNumber as Num, Account,Amount, RunningBalance as Balance parameters DateFrom = {d'2016-01-01'}, DateTo = {d'2022-12-31'}, EntityFilterFullNames= 'Abercrombie, Kristy:Remodel Bathroom'

Sheet2 will contain the result of InvoiceLine report.

Query used for fetching data for Sheet2 is:

select CustomerRefFullName,TxnDate,RefNumber,TermsRefFullName,AppliedAmount,Memo,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount from InvoiceLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and CustomerRefFullName= 'Abercrombie, Kristy:Remodel Bathroom'

Sheet3 will contain the result of CreditMemoLine report.

Query used for fetching data for Sheet3 is:

select CustomerRefFullName,TxnDate,RefNumber,TermsRefFullName,TotalAmount,Memo,CreditMemoLineItemRefFullName,CreditMemoLineDesc,CreditMemoLineQuantity,CreditMemoLineRate,CreditMemoLineAmount from CreditMemoLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and CustomerRefFullName= 'Abercrombie, Kristy:Remodel Bathroom'

Sheet4 will contain the result of ReceivePaymentLine report.

Query used for fetching data for Sheet4 is:

select CustomerRefFullName,TxnDate,RefNumber,'""',TotalAmount,Memo,UnusedPayment,UnusedCredits,AppliedToTxnTxnID,AppliedToTxnPaymentAmount,AppliedToTxnTxnType,AppliedToTxnTxnDate,AppliedToTxnRefNumber,AppliedToTxnBalanceRemaining from ReceivePaymentLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and CustomerRefFullName= 'Abercrombie, Kristy:Remodel Bathroom'

Sheet5 will contain the result of CheckExpenseLine report.

Query used for fetching data for Sheet5 is:

select PayeeEntityRefFullName,TxnDate,RefNumber,'""' ,Amount,Memo from CheckExpenseLine where TxnDate >= {d'2016-01-01'} and TxnDate <= {d'2022-12-31'} and PayeeEntityRefFullName= 'Abercrombie, Kristy:Remodel Bathroom'

Note: The ReceivePaymentLine & CheckExpenseLine table does not have TermsRefFullName field, So to maintain the data structure in Sheet6 which contains combine data I have added '""' in the query of ReceivePaymentLine & CheckExpenseLine.

Now, We have required data in Sheet1 to Sheet5. We will combine these data into Sheet6.

Sheet6 will contain the combined result of Sheet1 to Sheet5.

Please click here for downloading Sample Macro.

Application Source Code:

Private Sub CommandButton1_Click()

Dim cn As Object
Dim rs As Object

Dim strCon As String
Dim strSQL, CustName As String, Date1 As Date, Date2 As Date

Dim mainworkBook As Workbook

Set mainworkBook = ActiveWorkbook

strCon = "DSN=Quickbooks Data;OLE DB Services=-2;"

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
Dim a As String

Date1 = ThisWorkbook.Sheets("Sheet0").Cells(7, 3).Value
Date2 = ThisWorkbook.Sheets("Sheet0").Cells(8, 3).Value
CustName = ThisWorkbook.Sheets("Sheet0").Cells(6, 3).Value

strSQL = " sp_report CustomerBalanceDetail show Text, Blank, TxnType as Type, Date, RefNumber as Num, Account,Amount, RunningBalance as Balance " & _
"parameters DateFrom = " & fncqbDate(Date1) & ", DateTO = " & fncqbDate(Date2) & ", EntityFilterFullNames=  '" & CustName & "'  "


Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet1").Cells.ClearContents
Sheets("Sheet1").Cells.ClearFormats
Sheet1.Range("A1").CopyFromRecordset rs

rs.Close

strSQL = "select CustomerRefFullName,TxnDate,RefNumber,TermsRefFullName,AppliedAmount,Memo,InvoiceLineItemRefFullName,InvoiceLineDesc,InvoiceLineQuantity,InvoiceLineRate,InvoiceLineAmount from InvoiceLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and CustomerRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet2").Cells.ClearContents
Sheets("Sheet2").Cells.ClearFormats
Sheet2.Range("A1").CopyFromRecordset rs

rs.Close

strSQL = "select CustomerRefFullName,TxnDate,RefNumber,TermsRefFullName,TotalAmount,Memo,CreditMemoLineItemRefFullName,CreditMemoLineDesc,CreditMemoLineQuantity,CreditMemoLineRate,CreditMemoLineAmount from CreditMemoLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and CustomerRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet3").Cells.ClearContents
Sheets("Sheet3").Cells.ClearFormats
Sheet3.Range("A1").CopyFromRecordset rs

rs.Close

strSQL = "select CustomerRefFullName,TxnDate,RefNumber,'""',TotalAmount,Memo,UnusedPayment,UnusedCredits,AppliedToTxnTxnID,AppliedToTxnPaymentAmount,AppliedToTxnTxnType,AppliedToTxnTxnDate,AppliedToTxnRefNumber,AppliedToTxnBalanceRemaining from ReceivePaymentLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and CustomerRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet4").Cells.ClearContents
Sheets("Sheet4").Cells.ClearFormats
Sheet5.Range("A1").CopyFromRecordset rs
rs.Close

strSQL = "select PayeeEntityRefFullName,TxnDate,RefNumber,'""' ,Amount,Memo from CheckExpenseLine where TxnDate >= " & fncqbDate(Date1) & " and TxnDate <= " & fncqbDate(Date2) & " and PayeeEntityRefFullName= '" & CustName & "'"

Set rs = CreateObject("ADODB.RECORDSET")
rs.activeconnection = cn
rs.Open strSQL
Sheets("Sheet5").Cells.ClearContents
Sheets("Sheet5").Cells.ClearFormats
Sheet6.Range("A1").CopyFromRecordset rs

rs.Close
cn.Close

Set cn = Nothing


Sheets("Sheet6").Cells.ClearContents
Sheets("Sheet6").Cells.ClearFormats
ActiveWorkbook.Sheets("Sheet1").Select

  Dim N As Long
  N = Cells(1, 8).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet1").Range("A1:H2")
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 0).Select
  Selection.PasteSpecial Paste:=xlPasteValues

ActiveWorkbook.Sheets("Sheet2").Select

  
  N = ThisWorkbook.Sheets("Sheet2").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet2").Range("A1:N" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet3").Select
  
  N = ThisWorkbook.Sheets("Sheet3").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet3").Range("A1:L" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet4").Select
  
  N = ThisWorkbook.Sheets("Sheet4").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet4").Range("A1:M" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet5").Select
  
  N = ThisWorkbook.Sheets("Sheet5").Cells(1, 1).End(xlDown).Row
  If (N > 100000) Then N = 1
  Set DT = ThisWorkbook.Sheets("Sheet5").Range("A1:E" & N)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


ActiveWorkbook.Sheets("Sheet1").Select

  
  N = ThisWorkbook.Sheets("Sheet1").Cells(1, 8).End(xlDown).Row
  If (N > 4) Then N = N - 2
  Set DT = ThisWorkbook.Sheets("Sheet1").Range("A" & N & ":H" & N + 1)
  DT.Select
  Selection.Copy

  ActiveWorkbook.Sheets("Sheet6").Select
  lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
  ThisWorkbook.Sheets("Sheet6").Range("A" & lMaxRows + 2).Select
  Selection.PasteSpecial Paste:=xlPasteValues


End Sub

Public Function Nz(ByVal Value, Optional ByVal ValueIfNull = "")

    Nz = IIf(IsNull(Value), ValueIfNull, Value)

End Function
Function fncqbDate(myDate As Date) As String

myDate = Nz(myDate, Now)
fncqbDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"

End Function

 

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).