Knowledgebase: Tutorials
[QODBC-Online] How to use the QuickBooks Reporting Engine with QODBC Online
Posted by Jack - QODBC Support on 27 November 2015 10:44 AM


     QODBC Online works by accepting SQL commands through the ODBC Interface, then converting those calls to qbXML navigational commands within the QuickBooks Online API and returning recordsets that qualify for the query results. This driver is not a Client/Server product, and it navigates the QuickBooks API directly.

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



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


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


     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

      This sample Stored Procedure SQL Scripts can be copied (Ctrl-C) and pasted (Ctrl-V) into QODBC Online Test Tool and queried. With your QuickBooks Online company file open, click on Start and Programs and locate QODBC Driver for 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:

       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 show RowData,Label,Amount parameters DateMacro = 'ThisMonthToDate'

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

       sp_report GeneralLedger show 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 = 'ThisMonth'

       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 of 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

(0 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).