Instructions

     QODBC Online accepts SQL commands through the ODBC Interface, then converts those calls to qbXML navigational orders within the QuickBooks Online API and returns recordsets that qualify for the query results. This driver is not a Client/Server product; it navigates the QuickBooks API directly.

     QODBC also supports extracting all the QuickBooks Online Reports through the ODBC Interface. To know the list of Reports available, use the command SP_REPORTS. 

SP_REPORTS Related

SP_REPORTS

     You can use the new stored procedure SP_REPORTS to list all the available sp_reports in QuickBooks.

SP_REPORTCOLUMNS

     You can also see all the columns available in a given SP_REPORT by using the sp_reportcolumns report name

SP_PARAMETERS

     Use SP_PARAMETERS to list all the parameters available for a given SP_REPORT. The VALUES column lists available values that can be used with the parameter.

SP_REPORTS Used with MS Query

     Stored Procedure reports (sp_reports) can also be used by Microsoft Excel (or other MS Office products) to bring any number of QuickBooks Online reports directly into a spreadsheet by using Microsoft Query (shown in this example doing a Balance Sheet Detail report).

Stored Procedure SQL Scripts

      With your QuickBooks Online company file open, click Start and Programs and locate QODBC Driver for the QuickBooks Online program group. Click on the QODBC Online Test Tool icon to launch the program supplied with QODBC. To establish a connection, select the QuickBooks Online Data DSN, Click Connect and copy (Ctrl-C), and paste (Ctrl-V) into QODBC Online Test Tool any of the following QuickBooks Report SQL Scripts: This sample Stored Procedure SQL Scripts can be copied (Ctrl-C) and pasted (Ctrl-V) into QODBC Online Test Tool and queried.

       sp_report APAgingDetail show RowData, Date, TransactionType, DocumentNumber, Vendor, Location, DueDate, PastDue, Amount, OpenBalance
parameters DateFrom={d'2015-01-01'},DateTo={d'2015-11-24'}

       sp_report APAgingDetail parameters DateFrom={d'2015-01-01'},DateTo={d'2015-11-24'}

       sp_report APAgingSummary show Current_Title, Amount_Title, Vendor, Current, Amount parameters DateMacro = 'Today'

       sp_report ARAgingDetail show RowData, Date, TransactionType, DocumentNumber, Client, Location, DueDate, Amount, OpenBalance
parameters DateFrom={d'2015-01-01'},DateTo={d'2015-11-24'}

       sp_report ARAgingSummary show Current_Title, Amount_Title, RowData,Text, Current, Amount parameters DateMacro = 'Today'

       sp_report ARAgingSummary parameters DateMacro = 'Today'

       sp_report BalanceSheetDetail shows RowData, Label, Amount parameters DateMacro = 'ThisMonthToDate'

       sp_report CashFlow show RowData, Label, Time, Amount parameters DateMacro = 'ThisMonthToDate'

       sp_report GeneralLedger shows RowData, Date, TransactionType, DocumentNumber, Name, Memo, Split, Amount, Balance
parameters DateMacro = 'ThisMonthToDate'

       sp_report ProfitAndLossDetail show RowData, TransactionType, DocumentNumber, Name, Memo, Split, Amount, Balance
parameters DateMacro = 'ThisMonthToDate'

       sp_report VendorBalanceDetail show RowData, Date, TransactionType, DocumentNumber, Vendor, Location, DueDate, Amount, OpenBalance, Balance
parameters DateMacro = 'ALL'

       sp_report CustomerBalanceDetail show RowData, Date, TransactionType, DocumentNumber, Location, DueDate, Amount, OpenBalance, Balance
parameters DateFrom={d'2015-01-01'},DateTo={d'2015-11-24'}

       sp_report AccountList show RowData, Account, AccountNumber, Type, Description, Balance parameters ModifiedDateMacro = 'this month'

       sp_report SalesByCustomerSummary show Amount_Title, RowData, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Total'

       sp_report VendorBalanceSummary show RowData, Vendor, Amount parameters DateMacro = 'ALL'

       sp_report CustomerBalanceSummary show Customer, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'Total'

       sp_report ExpenseByVendorSummary show RowData, Vendor, Amount parameters DateMacro = 'ThisYearToDate'

       sp_report IncomeByCustomerSummary show Amount_Title, RowData, Amount parameters DateMacro = 'ThisYearToDate'

       sp_report InventoryValuationSummary show Text, RowData, ProductsAndService, Amount parameters DateMacro = 'ThisYearToDate'

       sp_report ProfitAndLossStandard show RowData,Amount parameters DateMacro = 'ThisYearToDate', ReportBasis='Cash'

       sp_report SalesByItemSummary show RowData,ProductsAndService,Amount parameters DateMacro = 'ThisMonthToDate',SummarizeColumnsBy = 'Total'

       sp_report SalesByDepartmentSummary show Amount_Title, RowData,Department, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Total'

       sp_report SalesByClassSummary show Amount_Title, RowData,Class, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Total'

       sp_report TrialBalance show RowData,Amount parameters DateMacro = 'ThisYearToDate', ReportBasis='Accrual'

Also, Refer

List of Reports available in QODBC Online

How to get information on Tables and Columns

QODBC Stored Procedures Command-List

How to use the VB Demo for testing

VB Demo is missing

Keywords: Report Engine, Sp_reports, QuickBooks Online, QBO, QODBC Online, reports