[QODBC-ALL] Troubleshooting - Does sp_report supports Like query
Posted by Jack - QODBC Support on 27 September 2017 10:06 AM
Troubleshooting - Does sp_report support Like query
Problem Description 1:
Does sp_report support "Like" in the parameter?
For example, sp_report CustomTxnDetail shows Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters AccountFullName Like '%Chase Credit Card%,' SummarizeRowsBy = 'TotalOnly.'
You can use like in sp_report as below:
For example, sp_report CustomTxnDetail shows Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters SummarizeRowsBy = 'TotalOnly' where AccountFullName Like '%Chase Credit Card%.'
Problem Description 2:
On some sp_reports like OpenInvoices or SalesByCustomerDetail, I don't see where I can put the parameter for the sales rep.
Which one is the parameter for the Sales Rep?
The SalesRep filter in OpenInvoices and SalesByCustomerDetail report is not available through the Intuit SDK, so they are not available through QODBC.
QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in SDK, QODBC could not do it either.
But, You can use the available column name in the sp_report by specifying with a where clause for filtering report data as below.
For Example, the SalesRep column is available in the OpenInvoices and SalesByCustomerDetail report, So you can use it in the sp_report query for filtering report data as below:
sp_report OpenInvoices show TxnType_Title, Date_Title, RefNumber_Title, PONumber_Title, Terms_Title, DueDate_Title, Aging_Title, OpenBalance_Title, Text, Blank, TxnType, Date, RefNumber, PONumber, Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today' where SalesRep = 'John Doe.'
sp_report SalesByCustomerDetail show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, Name_Title, Item_Title, Quantity_Title, UnitPrice_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Memo, Name, Item, Quantity, UnitPrice, Amount, RunningBalance parameters DateMacro = 'ThisMonthToDate' where SalesRep = 'John Doe.'