Knowledgebase
[QODBC-Desktop] How to run a Customer Balance Detail Report in QODBC
Posted by Juliet (QODBC Support) on 07 April 2010 06:31 AM

Typical Customer Balance Detail Report in QuickBooks

Report Displayed in QODBC

Stored Procedure Command

      With QODBC the same report can be generated using stored procedure reports like this:

      sp_report CustomerBalanceDetail show Text, Blank, TxnType as Type, Date, RefNumber as Num, Account,
      Amount, RunningBalance as Balance parameters DateMacro = 'All'

Predefined DateMacro Options

      All the predefined DateMacro options available to you are:

|All|Today|ThisWeek|ThisWeekToDate|ThisMonth|ThisMonthToDate|ThisQuarter|ThisQuarterToDate
|ThisYear|ThisYearToDate|Yesterday|LastWeek|LastWeekToDate|LastMonth|LastMonthToDate|LastQuarter
|LastQuarterToDate|LastYear|LastYearToDate|NextWeek|NextFourWeeks|NextMonth|NextQuarter|NextYear|

Display in QODBC

 

Typical Customer Balance Detail Report with Accounts Unpaid in QuickBooks

       Most QuickBooks users prefer to see only the accounts that haven't been paid (and if they have any unapplied credits):

Report Displayed in QODBC

Stored Procedure Command

      With QODBC the same report can be generated using stored procedure reports using the PaidStatus filter like this:

      sp_report CustomerBalanceDetail show RowData, TxnType as Type, Date, RefNumber as Num, Account, Amount
      parameters DateMacro = 'All' where PaidStatus = 'Unpaid'

Display in QODBC

Access Unpaid INVOICEs Directly

      You can also access unpaid INVOICEs directly from the Sales view read only table.

Do a Full Resync

      First do a full resync of your Sales table by running:

      sp_optimizefullsync Sales

Access All Sales Records

     Use the NOSYNC tag to allow you to access all your sales with great speed:

     SELECT CustomerRefFullName as RowData, Type, TxnDate as Date, RefNumber as Num,
     ARAccountRefFullName as Account, Remaining as Amount FROM Sales NOSYNC where IsPaid = 0
     Order by CustomerRefFullName, TxnDate, RefNumber

Include the Credit Memos

       SELECT CustomerRefFullName as RowData, Type, TxnDate as Date, RefNumber as Num,
       ARAccountRefFullName as Account, Remaining as Amount FROM Sales NOSYNC
       where IsPaid = 0 or Type = 'CreditMemo' Order by CustomerRefFullName, TxnDate, RefNumber

(178 vote(s))
Helpful
Not helpful

Comments (1)
Judith
20 September 2012 03:24 PM
Now we know who the sensbile one is here. Great post!
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).