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

Knowledgebase

(1)Downloads (1016)Tutorials (839)TroubleShooting (24)SQL Language Support (Syntax) (215)QXL Support (513)QODBC (85)QODBC Online (63)QODBC POS (3)QScheduler

Knowledgebase: QODBC

Posted by Jack - QODBC Support on 26 December 2017 10:31 AM

Troubleshooting - How can I combine two reports and get a customer's transactions report?

Problem Description:

I need a report by a 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 parents can read them. I would also like to be able to email the statement to an email address.

Solution:

You need to query different tables & reports to get the required data. Once you get the data, you need to combine all data.

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

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

tatWe have created seven full sheets (i.e., Sheet0 to Sheet6). Sheet0 will be input form & Sheet1 to Sheet5 will contain data from the CustomerBalanceDetail report & InvoiceLine, CreditMemoLine, ReceivePaymentLine, and CheckExpenseLine table for particular Customers, and Sheet6 will combine data from Sheet1 to Sheet5. We have created a sample MS Excel macro that will fetch the required data.

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

On clicking the "Generate Report" button, we will query the report & table for a particular Customer & store results in Sheet1 to Sheet5.

Sheet 1 will contain the result of the CustomerBalanceDetail report.

The 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.'

Sheet 2 will contain the result of the InvoiceLine report.

The 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.'

Sheet 3 will contain the result of the CreditMemoLine report.

The 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.'

Sheet 4 will contain the result of the ReceivePaymentLine report.

The 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.'

Sheet 5 will contain the result of the CheckExpenseLine report.

The 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 the TermsRefFullName field. To maintain the data structure in Sheet6, which contains combined data, I have added '" ' in the query of ReceivePaymentLine & CheckExpenseLine.

Now, We have the required data in Sheet1 to Sheet5. We will combine these data into Sheet 6.

Sheet 6 will contain the combined result of Sheet 1 to Sheet 5.

Please click here to download 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 Reply to comment
Post a new comment Reply to comment

Full Name:
Email:
Comments:

CAPTCHA Verification
CAPTCHA Verification

Please complete the captcha below (we use this to prevent automated submissions).

Help Desk Software by Kayako