Note: Actually, we don't have a sp_report for the QuickBooks "Open Sales Order by Item" report, which is not available via Intuit SDK. However, I've managed to extract similar data using QODBC.

Typical Open Sales Order by Item Report in QuickBooks

     For example, when using the Rock Castle Construction QuickBooks 2006 sample company file: sample_product-based business.qbw, the report looks like this:

Report Displayed in QODBC

Normal Query

     By using the following select statement:

SELECT SalesOrderLIneItemRefFullName as Type, TxnDate as Date,
DueDate, RefNumber as Num, CustomerRefFullName as Name,
SalesOrderLineQuantity as Qty, SalesOrderLineInvoiced as Invoiced,
SalesOrderLineAmount as Amount FROM SalesOrderLine
where IsFullyInvoiced = FALSE
and SalesOrderLineInvoiced < SalesOrderLineQuantity

Display in QODBC

     What is shown in the above report appears using QODBC:

Query with Date Range

     This can also be made must faster by limiting the date range like this:

SELECT SalesOrderLIneItemRefFullName as Type, TxnDate as Date,
DueDate, RefNumber as Num, CustomerRefFullName as Name,
SalesOrderLineQuantity as Qty, SalesOrderLineInvoiced as Invoiced,
SalesOrderLineAmount as Amount FROM SalesOrderLine
where TxnDate >= {d'2008-01-01'} and IsFullyInvoiced = FALSE
and SalesOrderLineInvoiced < SalesOrderLineQuantity

Normal Issues and Solutions

The resulting Rows are Short of the QuickBooks Report's Rows, and the Total Amounts Don't Match

    Make sure you are running QODBC v6.00.00.155 or later. And try the below instead:

SELECT SalesOrderLIneItemRefFullName as Type, TxnDate as Date,
DueDate, RefNumber as Num, CustomerRefFullName as Name,
SalesOrderLineQuantity as Qty, SalesOrderLineInvoiced as Invoiced,
SalesOrderLineAmount as Amount FROM SalesOrderLine unoptimized
where IsFullyInvoiced = FALSE
and SalesOrderLineInvoiced < SalesOrderLineQuantity

How to add the aging field, the class field, and the memo field to the report

     Well, this will work based on the actual current system date (not the QuickBooks date):

SELECT SalesOrderLIneItemRefFullName as Type, TxnDate as Date, DueDate, RefNumber as Num, CustomerRefFullName as Name, SalesOrderLineQuantity as Qty, SalesOrderLineInvoiced as
Invoiced, SalesOrderLineAmount as Amount, ClassRefFullName as Class, Memo,
({fn CURDATE()}-TxnDate) as Aging FROM SalesOrderLine unoptimized
where IsFullyInvoiced = FALSE and SalesOrderLineInvoiced < SalesOrderLineQuantity

When the sp_report command for the "Open Sales Orders by Item" report could be added

     I've not heard or seen anything about adding sp_report for Open Sales Orders by Customer or Open Sales Orders by Item. This is a limitation of Intuit SDK. But could see also: Open sales orders by Item as a reference.