Knowledgebase: Sp_commands & Reports
[QODBC-Desktop] How to run an Open Invoices Report in QODBC
Posted by brad waddell on 12 March 2009 05:21 PM

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

(167 vote(s))
Helpful
Not helpful

Comments (2)
John Taylor
22 October 2015 09:24 PM
Hi,

Is there a way to pull in the description for the invoice in the 'Open Invoice Report'? Or would you know the table that the description line is stored?

I am trying to create an A/R Aging Detail that includes the description of the invoice to the right of Name and left of Memo.
Jack
27 October 2015 12:32 PM
Hi John,

I would like to inform you that QODBC can only show those columns and fields which are available in QuickBooks Report Screen and available via SDK.

For any additional data, you may query other tables additionally and merge the result (cannot link or have relationship on the result of sp_report and a table/function/report).

Also, I would like to share you that the QuickBooks SDK does provides output of the report but does not provide information on how those data are derived or which tables are used in those reports.

QuickBooks SDK does not expose information regarding how the report is built, what are the tables used or logic part and thus it is not available in QODBC.

All QODBC do is request for the Report information from QuickBooks SDK and QuickBooks is the one processing it and sending the output. QODBC formats that output to data table format.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).