Typical Open Invoices 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 OpenInvoices show Text, Row data as Type, Blank, TxnType, Date, RefNumber as Num, PONumber,
      Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today'

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

Access Unpaid INVOICEs Directly

      You can also access unpaid INVOICEs directly using the Invoice table and the IsPaid filter like this:

      select CustomerRefFullName, TxnDate as Date, RefNumber as Num, PONumber, TermsRefFullName as Terms,
      DueDate, BalanceRemaining as OpenBalance from Invoice
      where IsPaid = 0 order by CustomerRefFullName, TxnDate

Optimizer Related

      Depending on your QODBC optimizer settings, you might need to do the below instead to see the proper open balance in real-time:

      select CustomerRefFullName, TxnDate as Date, RefNumber as Num, PONumber, TermsRefFullName as Terms,
      DueDate, BalanceRemaining as OpenBalance from Invoice call direct
      where IsPaid = 0 order by CustomerRefFullName, TxnDate

See also: How to run a Customer Balance Detail Report in QODBC