A typical Customer Balance Detail Report looks like this:
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'
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|
Most QuickBooks users prefer to see only the accounts that haven't been paid (and if they have any unapplied credits):
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'
You can also access unpaid INVOICEs directly from the Sales view read only table. First do a full resync of your Sales table by running:
sp_optimizefullsync Sales
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
or to include the Credit Memos use:
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
|