Knowledgebase
[QODBC-Desktop] How to use the QuickBooks Reporting Engine with QODBC
Posted by Juliet on 30 March 2010 08:32 AM

Instructions

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

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

SP_REPORTS Related

SP_REPORTS

     With the release of QODBC v6.00.00.176 and later you can use the new stored procedure SP_REPORTS to list all the available sp_reports in QuickBooks.(Locate VB Demo)

SP_REPORTCOLUMNS

     Can also see all the columns available in a given SP_REPORT by using 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 reports directly into a spreadsheet by using Microsoft Query (shown in this example doing a Balance Sheet Summary report).

 

Stored Procedure SQL Scripts

      These sample Stored Procedure SQL Scripts can be copied (Ctrl-C) and pasted (Ctrl-V) into VB Demo and queried. With your QuickBooks company file open, click on Start and Programs and locate QODBC Driver for QuickBooks program group. Click on the VB Demo icon to launch the Visual Basic Demo program supplied with QODBC. To establish a connection, from the menu bar select Connections -> Add a new connection, select the QuickBooks Data DSN, Click OK and copy (Ctrl-C) and paste (Ctrl-V) into VB Demo any of the following QuickBooks Report SQL Scripts:

       sp_report PhysicalInventoryWorksheet show ItemDesc_Title, ItemVendor_Title, QuantityOnHand_Title, None_Title, Text, Blank, ItemDesc, ItemVendor, QuantityOnHand, None

       sp_report InventoryValuationSummary show ItemDesc_Title, QuantityOnHand_Title, AverageCost_Title, ValueOnHand_Title, PercentOfTotalValue_Title, UnitPrice_Title, RetailValueOnHand_Title, PercentOfTotalRetail_Title, Text, Blank, ItemDesc, QuantityOnHand, AverageCost, ValueOnHand, PercentOfTotalValue, UnitPrice, RetailValueOnHand, PercentOfTotalRetail parameters DateMacro = 'ThisMonthToDate'

       sp_report InventoryStockStatusByVendor show ItemDesc_Title, ReorderPoint_Title, QuantityOnHand_Title, QuantityOnSalesOrder_Title, QuantityAvailable_Title, SuggestedReorder_Title, QuantityOnOrder_Title, EarliestReceiptDate_Title, SalesPerWeek_Title, Text, Blank, ItemDesc, ReorderPoint, QuantityOnHand, QuantityOnSalesOrder, QuantityAvailable, SuggestedReorder, QuantityOnOrder, EarliestReceiptDate, SalesPerWeek parameters DateMacro = 'ThisMonthToDate'

       sp_report InventoryStockStatusByItem show ItemDesc_Title, ItemVendor_Title, ReorderPoint_Title, QuantityOnHand_Title, QuantityOnSalesOrder_Title, QuantityAvailable_Title, SuggestedReorder_Title, QuantityOnOrder_Title, EarliestReceiptDate_Title, SalesPerWeek_Title, Text, Blank, ItemDesc, ItemVendor, ReorderPoint, QuantityOnHand, QuantityOnSalesOrder, QuantityAvailable, SuggestedReorder, QuantityOnOrder, EarliestReceiptDate, SalesPerWeek parameters DateMacro = 'ThisMonthToDate'

       sp_report APAgingDetail show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, DueDate_Title, Aging_Title, OpenBalance_Title, Text, Blank, TxnType, Date, RefNumber, Name, DueDate, Aging, OpenBalance parameters DateMacro = 'Today', AgingAsOf = 'Today'

       sp_report ARAgingDetail show TxnType_Title, Date_Title, RefNumber_Title, PONumber_Title, Name_Title, Terms_Title, DueDate_Title, Aging_Title, OpenBalance_Title, Text, Blank, TxnType, Date, RefNumber, PONumber, Name, Terms, DueDate, Aging, OpenBalance parameters DateMacro = 'Today', AgingAsOf = 'Today'

       sp_report VendorBalanceDetail show TxnType_Title, Date_Title, RefNumber_Title, Account_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Account, Amount, RunningBalance parameters DateMacro = 'All'

       sp_report UnbilledCostsByJob show TxnType_Title, Date_Title, SourceName_Title, Memo_Title, Account_Title, BillingStatus_Title, Amount_Title, Text, Blank, TxnType, Date, SourceName, Memo, Account, BillingStatus, Amount parameters DateMacro = 'All'

       sp_report SalesByRepDetail 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'

       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'

       sp_report PendingSales show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, Account_Title, Amount_Title, Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'TotalOnly'

       sp_report CustomerBalanceDetail show TxnType_Title, Date_Title, RefNumber_Title, Account_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Account, Amount, RunningBalance parameters DateMacro = 'All'

       sp_report UnpaidBillsDetail show TxnType_Title, Date_Title, RefNumber_Title, DueDate_Title, Aging_Title, OpenBalance_Title, Text, Blank, TxnType, Date, RefNumber, DueDate, Aging, OpenBalance parameters DateMacro = 'Today'

       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'

       sp_report CollectionsReport 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', AgingAsOf = 'Today'

       sp_report SalesByItemDetail show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, Name_Title, Quantity_Title, UnitPrice_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Memo, Name, Quantity, UnitPrice, Amount, RunningBalance parameters DateMacro = 'ThisMonthToDate'

       sp_report IncomeTaxDetail show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, Account_Title, Debit_Title, Credit_Title, Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, Debit, Credit parameters DateMacro = 'LastYear'

       sp_report IncomeByCustomerDetail show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, Account_Title, ClearedStatus_Title, SplitAccount_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Memo, Account, ClearedStatus, SplitAccount, Amount, RunningBalance parameters DateMacro = 'ThisYearToDate'

       sp_report EstimatesByJob show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, EstimateActive_Title, Amount_Title, Text, Blank, TxnType, Date, RefNumber, Memo, EstimateActive, Amount parameters DateMacro = 'All'

       sp_report ExpenseByVendorDetail show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, Account_Title, ClearedStatus_Title, SplitAccount_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Memo, Account, ClearedStatus, SplitAccount, Amount, RunningBalance parameters DateMacro = 'ThisYearToDate'

       sp_report EmployeeStateTaxesDetail show SourceName_Title, Date_Title, PayrollItem_Title, IncomeSubjectToTax_Title, WageBase_Title, Amount_Title, Text as "Employee Name", SourceName, Date, PayrollItem, IncomeSubjectToTax, WageBase, Amount parameters DateMacro = 'LastQuarter', IncludeAccounts = 'InUse'

       sp_report PayrollItemDetail show RefNumber_Title, Date_Title, SourceName_Title, PayrollItem_Title, TxnType_Title, WageBase_Title, Amount_Title, Text, Blank, TxnType, Date, RefNumber, SourceName, PayrollItem, WageBase, Amount parameters DateMacro = 'ThisQuarter', IncludeAccounts = 'InUse'

       sp_report PayrollTransactionsByPayee show Date_Title, Name_Title, RefNumber_Title, TxnType_Title, Memo_Title, Account_Title, Amount_Title, Text, TxnType, Date, RefNumber, Name, Memo, Account, Amount parameters DateMacro = 'ThisQuarter', IncludeAccounts = 'InUse', ReportDetailLevelFilter = 'SummaryOnly'

       sp_report TimeByJobDetail show Date_Title, Name_Title, BillingStatus_Title, Duration_Title, Text, Blank, Date, Name, BillingStatus, Duration parameters DateMacro = 'ThisMonthToDate'

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

       sp_report IncomeTaxSummary show Amount_Title, Label, Amount parameters DateMacro = 'LastYear'

       sp_report JobProfitabilitySummary show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly'

       sp_report ItemProfitability show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly'

       sp_report JobProfitabilityDetail show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Abercrombie, Kristy', SummarizeColumnsBy = 'TotalOnly'

       sp_report TrialBalance show Debit_Title, Credit_Title, Label, Debit, Credit parameters DateMacro = 'LastMonth', ReportBasis = 'Cash' (or 'Accrual')

       sp_report SalesTaxLiability show TotalSales_Title, NonTaxableSales_Title, TaxableSales_Title, TaxRate_Title, TaxCollected_Title, SalesTaxPayable_Title, Text, Label, TotalSales, NonTaxableSales, TaxableSales, TaxRate, TaxCollected, SalesTaxPayable parameters DateMacro = 'LastMonth'

       sp_report CustomerBalanceSummary show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report CustomSummary show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'IncomeStatement', SummarizeColumnsBy = 'TotalOnly', Calendar = 'FiscalYear', ReturnRows = 'ActiveOnly', ReturnColumns = 'ActiveOnly'

       sp_report SalesTaxRevenueSummary show AmountPreviousPeriod_Title, AmountPreviousYear_Title, Amount_Title Text, Label, AmountPreviousPeriod, AmountPreviousYear, Amount parameters DateMacro = 'LastMonth', SummarizeColumnsBy = 'SalesTaxCode'

       sp_report SalesTaxRevenueSummary show Amount_Title, Text, Label, Amount parameters DateMacro = 'LastMonth', SummarizeColumnsBy = 'SalesTaxCode'

       sp_report SalesByCustomerSummary show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report IncomeByCustomerSummary show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report SalesByItemSummary show Quantity_Title, Amount_Title, Percent_Title, AveragePrice_Title, Text, Label, Quantity, Amount, Percent, AveragePrice parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report SalesByItemSummary show Quantity_Title, Amount_Title, Percent_Title, AveragePrice_Title, AmountCOGS_Title, AmountAvgCOGS_Title, AmountGrossMargin_Title, PercentGrossMargin_Title, Text, Label, Quantity, Amount, Percent, AveragePrice, AmountCOGS, AmountAvgCOGS, AmountCOGS, AmountGrossMargin, PercentGrossMargin parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report ProfitAndLossBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

       sp_report JobEstimatesVsActualsSummary show AmountEstCost_Title, AmountActualCost_Title, AmountDifferenceCost_Title, AmountEstRevenue_Title, AmountActualRevenue_Title, AmountDifferenceRevenue_Title, Text, Label, AmountEstCost, AmountActualCost, AmountDifferenceCost, AmountEstRevenue, AmountActualRevenue, AmountDifferenceRevenue parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly'

       sp_report JobEstimatesVsActualsDetail show AmountEstCost_Title, AmountActualCost_Title, AmountDifferenceCost_Title, AmountEstRevenue_Title, AmountActualRevenue_Title, AmountDifferenceRevenue_Title, Text, Label, AmountEstCost, AmountActualCost, AmountDifferenceCost, AmountEstRevenue, AmountActualRevenue, AmountDifferenceRevenue parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Cook, Brian', SummarizeColumnsBy = 'TotalOnly'

       sp_report ItemEstimatesVsActuals show AmountEstCost_Title, AmountActualCost_Title, AmountDifferenceCost_Title, AmountEstRevenue_Title, AmountActualRevenue_Title, AmountDifferenceRevenue_Title, Text, Label, AmountEstCost, AmountActualCost, AmountDifferenceCost, AmountEstRevenue, AmountActualRevenue, AmountDifferenceRevenue parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly'

       sp_report BalanceSheetSummary show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report ProfitAndLossByJob show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Customer'

       sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Class'

       sp_report TimeByName show Duration_Title, Text, Label, Duration parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report TimeByJobSummary show Duration_Title, Text, Label, Duration parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report TimeByItem show Duration_Title, Text, Label, Duration parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report PayrollSummary show AmountHours_Title, AmountRate_Title, Amount_Title, Label, AmountHours, AmountRate, Amount parameters DateMacro = 'ThisQuarterToDate'

       sp_report TxnDetailByAccount show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, ClearedStatus_Title, SplitAccount_Title, Debit_Title, Credit_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisMonthToDate'

       sp_report ProfitAndLossDetail show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, ClearedStatus_Title, SplitAccount_Title, Debit_Title, Credit_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisYearToDate'

       sp_report PurchaseByVendorDetail 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'

       sp_report PurchaseByItemDetail show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, SourceName_Title, Quantity_Title, UnitPrice_Title, Amount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Memo, SourceName, Quantity, UnitPrice, Amount, RunningBalance parameters DateMacro = 'ThisMonthToDate'

       sp_report TxnListByDate show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, Account_Title, ClearedStatus_Title, SplitAccount_Title, Debit_Title, Credit_Title, Text, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit parameters DateMacro = 'ThisMonthToDate'

       sp_report AuditTrail show TxnNumber_Title, TxnType_Title, ModifiedTime_Title, LastModifiedBy_Title, Date_Title, Name_Title, Memo_Title, Account_Title, SplitAccount_Title, Debit_Title, Credit_Title, Text, TxnNumber, TxnType, ModifiedTime, LastModifiedBy, Date, Name, Memo, Account, SplitAccount, Debit, Credit parameters DateMacro = 'Today'

       sp_report CheckDetail show TxnType_Title, RefNumber_Title, Date_Title, Name_Title, Item_Title, Account_Title, PaidAmount_Title, OriginalAmount_Title, TxnType, RefNumber, Date, Name, Item, Account, PaidAmount, OriginalAmount parameters DateMacro = 'ThisMonthToDate' [Note: (where ClearedStatus = 0) for uncleared Cheques ]

       sp_report Detail1099 show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, Account_Title, ClearedStatus_Title, SplitAccount_Title, OriginalAmount_Title, PaidAmount_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Memo, Account, ClearedStatus, SplitAccount, OriginalAmount, PaidAmount, RunningBalance parameters DateMacro = 'LastYear'

       sp_report DepositDetail show TxnType_Title, RefNumber_Title, Date_Title, Name_Title, Account_Title, Amount_Title, TxnType, RefNumber, Date, Name, Account, Amount parameters DateMacro = 'ThisMonthToDate'

       sp_report InventoryValuationDetail show TxnType_Title, Date_Title, Name_Title, RefNumber_Title, Quantity_Title, CostPrice_Title, QuantityOnHand_Title, AverageCost_Title, ValueOnHand_Title, Text, Blank, TxnType, Date, Name, RefNumber, Quantity, CostPrice, QuantityOnHand, AverageCost, ValueOnHand parameters DateMacro = 'ThisMonthToDate'

       sp_report JobProgressInvoicesVsEstimates show TxnType_Title, Date_Title, RefNumber_Title, EstimateActive_Title, Amount_Title, ProgressAmount_Title, ProgressPercent_Title, Text, TxnType, Date, RefNumber, EstimateActive, Amount, ProgressAmount, ProgressPercent parameters DateMacro = 'ThisYearToDate'

       sp_report OpenPOs show TxnType_Title, Date_Title, Name_Title, RefNumber_Title, DeliveryDate_Title, Amount_Title, Blank, TxnType, Date, Name, RefNumber, DeliveryDate, Amount parameters DateMacro = 'All'

       sp_report OpenPOsByJob show TxnType_Title, Date_Title, DeliveryDate_Title, RefNumber_Title, SourceName_Title, Item_Title, Quantity_Title, ReceivedQuantity_Title, UnitPrice_Title, Amount_Title, Text, Blank, TxnType, Date, DeliveryDate, RefNumber, SourceName, Item, Quantity, ReceivedQuantity, UnitPrice, Amount parameters DateMacro = 'All'

       sp_report PayrollReviewDetail show Date_Title, ModifiedTime_Title, TaxTableVersion_Title, IncomeSubjectToTax_Title, WageBase_Title, PayrollItem_Title, UserEdit_Title, Amount_Title, CalculatedAmount_Title, AmountDifference_Title, Text, Date, ModifiedTime, TaxTableVersion, IncomeSubjectToTax, WageBase, PayrollItem, UserEdit, Amount, CalculatedAmount, AmountDifference parameters DateMacro = 'ThisYearToDate', IncludeAccounts = 'All'

       sp_report TxnListByVendor show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, Account_Title, ClearedStatus_Title, SplitAccount_Title, Amount_Title, Text, TxnType, Date, RefNumber, Memo, Account, ClearedStatus, SplitAccount, Amount parameters DateMacro = 'ThisMonthToDate'

       sp_report PurchaseByItemSummary show Quantity_Title, Amount_Title, Text, Label, Quantity, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report PayrollLiabilityBalances show Amount_Title, Text, Label, Amount parameter DateMacro = 'ThisYear'

       sp_report ProfitAndLossBudgetOverview show Amount_Title, Text, Label, Amount parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

       sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

       sp_report ProfitAndLossYTDComp show Amount_Title, AmountYTD_Title, Text, Label, Amount, AmountYTD parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report BalanceSheetPrevYearComp show Amount_Title, AmountPreviousYear_Title, AmountPreviousYearChange_Title, PercentChangePreviousYear_Title, Text, Label, Amount, AmountPreviousYear, AmountPreviousYearChange, PercentChangePreviousYear parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report ProfitAndLossPrevYearComp show Amount_Title, AmountPreviousYear_Title, AmountPreviousYearChange_Title, PercentChangePreviousYear_Title, Text, Label, Amount, AmountPreviousYear, AmountPreviousYearChange, PercentChangePreviousYear parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report CustomTxnDetail show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, Account_Title, ClearedStatus_Title, SplitAccount_Title, Debit_Title, Credit_Title, RunningBalance_Title, Text, Blank, TxnType, Date, RefNumber, Name, Memo, Account, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'TotalOnly', OpenBalanceAsOf = 'Today'

       sp_report PayrollTransactionDetail show Date_Title, RefNumber_Title, TxnType_Title, SourceName_Title, PayrollItem_Title, WageBase_Title, Amount_Title, Text, Date, RefNumber, TxnType, SourceName, PayrollItem, WageBase, Amount parameters DateMacro = 'ThisMonth', IncludeAccounts = 'All', ReportDetailLevelFilter = 'AllExceptSummary'

       sp_report MissingChecks show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, Account_Title, SplitAccount_Title, Amount_Title, Text, TxnType, Date, RefNumber, Name, Memo, Account, SplitAccount, Amount parameters DateMacro = 'All', AccountFilterFullNames = 'Checking'

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

       sp_report VendorBalanceSummary show Amount_Title, Label, Amount parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly'

       sp_report SalesByRepSummary show Amount_Title, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report PurchaseByVendorSummary show Amount_Title, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report ExpenseByVendorSummary show Amount_Title, Label, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'TotalOnly'

       sp_report EmployeeEarningsSummary show Amount_Title, Label, Amount parameters DateMacro = 'ThisQuarter'

       sp_report Journal show TxnNumber_Title, TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, Account_Title, Debit_Title, Credit_Title, TxnNumber, TxnType, Date, RefNumber, Name, Memo, Account, Debit, Credit parameters DateMacro = 'ThisMonthToDate'

       sp_report GeneralLedger show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, SplitAccount_Title, Debit_Title, Credit_Title, RunningBalance_Title, Blank, TxnType, Date, RefNumber, Name, Memo, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisMonthToDate'

       sp_report BalanceSheetDetail show TxnType_Title, Date_Title, RefNumber_Title, Name_Title, Memo_Title, ClearedStatus_Title, SplitAccount_Title, Debit_Title, Credit_Title, RunningBalance_Title, Blank, TxnType, Date, RefNumber, Name, Memo, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance parameters DateMacro = 'ThisMonthToDate'

       sp_report TxnListByCustomer show TxnType_Title, Date_Title, RefNumber_Title, Memo_Title, Account_Title, ClearedStatus_Title, SplitAccount_Title, Amount_Title, Text, TxnType, Date, RefNumber, Memo, Account, ClearedStatus, SplitAccount, Amount parameters DateMacro = 'ThisMonthToDate'

       sp_report BalanceSheetBudgetOverview show Amount_Title, Label, Amount parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

       sp_report BalanceSheetBudgetOverview show Amount_Title, Label, Amount parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

       sp_report BalanceSheetBudgetVsActual show Amount_Title, Budget_Title, Difference_Title, Percent_Title, Text, Label, Amount, Budget, Difference, Percent parameters FiscalYear = 2003, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'  

   sp_report LotNumberInStockBySite show Blank, Text, ItemDesc, SerialOrLotNumber parameters DateMacro = 'ThisMonthToDate'        

Also Refer

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

(153 vote(s))
Helpful
Not helpful

Comments (20)
Mike Maltby
25 September 2014 03:33 AM
Do you have any instructions on how to use a stored procedure in Access? i.e. I can get the SP to run in VP Demo or Excel Query ok, but I in Access I seem to need a Select statement & reference to the tables. I need to use an SP because it seems to be only way to get at the budget columns.
Jack
25 September 2014 09:00 AM
Hi Mike,

Please refer below mentioned article to create Pass-Through Report using Microsoft Access:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2359/53/how-to-create-pass-through-report-using-microsoft-access-2003
J. Fitz
05 February 2015 10:21 PM
I downloaded the trial and am using the VB Demo to test the sp_report feature. Each time I test a command using the syntax from the examples the error is "Expected lexical element not found: <keyword>. This is a the test I just tried:

sp_report PhysicalInventoryWorksheet show ItemDesc_Title, ItemVendor_Title, QuantityOnHand_Title, None_Title, Text, Blank, ItemDesc, ItemVendor, QuantityOnHand, None
Jack
23 February 2015 01:34 PM
Hi,

I have tested same statement at my end & I am unable reproduce the issue. sp_report query works fine. Generally "lexical element" will occur only if there is some syntax issue in your SQL statement. So I kindly request you to please verify the syntax & try again.

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share QuickBooks Version details: Press f2 on QuickBooks UI & share screenshot.
4) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
5) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
6) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Tony
08 May 2015 05:12 PM
Hi Jack,
Is there any table I can find item inventory information without sp_reports? If yes, I can produce my own inventory report. It seems more convenient.
Thanks.

Jack
11 May 2015 09:38 AM
Hi Tony,

You can refer QODBC tables (i.e. Items & other Item Inventory tables.)& check for required data. Please note QODBC will display data in table what it gets from QuickBooks SDK. If some field/data is not available through the SDK, then QODBC could not do it either.

I kindly request you to please refer below mentioned link to get details about the QODBC table & report schema. You will get details about each table/report column, parameters which are supported, including information about whether a column is updatable or insertable and relationship with other tables as well require fields in insert details:

http://www.qodbc.com/schema.htm
Tony Wen
08 May 2015 08:09 PM
Hi Jack,
When I executed this in Excel connection definition as command text,
sp_reportcolumns 'InventoryStockStatusByItem'
It worked well.
But when I used :
sp_report InventoryStockStatusByItem show ItemDesc, QuantityOnHand parameters DateMacro = 'Today'
Excel told me that "unable to return object"
Can I choose some columnnames in this Stored Procedure SQL Scripts?
Thanks.
Jack
11 May 2015 09:25 AM
Hi Tony,

I am unable to reproduce the issue. I can execute same query in MS Excel using MS Query without any issue.

You can choose a column name through SQL Script in sp_report. Please insert column name after "show" which you want to show.

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.

We may need following information, I kindly request you to attach below listed files when replying to the ticket.

1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Alexie
27 June 2015 12:52 AM
Hi,

How can I get a Balance Sheet or Income Statement to go through excel and auto change/populate when i refresh within excel?

Thanks,
Alexie
Jack
09 July 2015 08:21 AM
Hi Alexie,

You can export report in MS Excel & can refresh report by clicking refresh button in excel.

Please refer below mentioned article for exporting reports in MS Excel:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2405/57/how-to-create-sp_reports-using-microsoft-excel-2007
Brian
02 September 2015 10:02 PM
Hi Jack,

Can I add a filter to a query to return data on just one customer? i.e. I would like to run the P&L YTD Comp for just one customer.

Thanks!
Brian
Jack
03 September 2015 12:13 PM
Hi Brian,

You can use report parameter EntityFilterFullNames for filtering report with Entity (i.e. Customer,Vendor,Employee,OtherName).

Please refer below sample query to run report for particular Customer:

sp_report ProfitAndLossYTDComp show Amount_Title, AmountYTD_Title, Text, Label, Amount, AmountYTD parameters EntityFilterFullNames='Kern Lighting Warehouse:Store #45' , DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

You can get QODBC table/report schema by visting below link:
http://www.qodbc.com/schema.htm
Ali
29 September 2015 06:28 AM
We have QB Enterprise 2011 and been using custom field option for some time now. We have a custom field created in item as “Birds” and a number is entered in Invoicing window item line. It shows up in Sales By Customer reports and so on.
We are also using ODBC custom reporting tool with MS Excel.

What I would like to know is, how the v_cf_item table is linked with other tables such as v_txn_invoice_line to build joins ? basically to get Sales by Customers details along with associated “Birds” field data's

Thank you
Jack
29 September 2015 08:21 AM
Hi Ali,

You’re using Intuit Custom Reporting for QuickBooks Enterprise and Not QODBC. I kindly request you to remove any connection you are using with has Sybase SQL Anywhere and use QODBC.

FYI : You need to connect to "QuickBooks Data" DSN (from system/machine dsn) not to any file dsn ( Do not try connecting to File DSN, which is next to your company file with the name COMPANYFILENAME.dsn). I kindly suggest you to refer below articles for how to use QODBC:


http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2512

Please note: you need select “QuickBooks Data” (if the applications is 32 bit) or “QuickBooks Data-64bit QRemote” (if the applications is 64 bit) when you choose data source instead of select other data source.
Laura Grimes
16 February 2016 04:34 PM
I'm trying to pull several reports using the SQL statements provided. The problem I have is trying to get a different date range. For example, the "Budgets->Profit & Loss Budget Performance" SQL statements specifies a year. If I insert 2016, then it pulls YTD actual data. How do I change the YTD to end on a specific date. For example, if it is February 16, I don't want it to pull YTD Feb 16, 2016 data. I would like it to pull YTD (Jan 31, 2016) data. How do I modify the SQL statement for this range? I've tried several changes with no success.
Jack
17 February 2016 06:45 AM
Hi Laura,

You can use DateFrom & DateTo parameters for running report for the specific period.

For Example:

sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters DateFrom = {d'2018-01-01'}, DateTo= {d'2018-12-31'}, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

I kindly request you to please refer below mentioned link to get details about the QODBC table & report schema as well as relation between tables. You will also get details about each report column, parameters which are supported:
http://www.qodbc.com/schema.htm
John McDonald
14 April 2016 07:38 PM
I am trying to pull detailed transactions down from QBO to Access. All I get back from the query are totals from the time period. How do I query a detailed transactions to display?
Jack
18 April 2016 07:10 AM
Hi John,

I would like to share that tables, table columns, reports, report columns & report parameters in QuickBooks Desktop & Online are not same. QODBC Online will display reports/tables/fields whatever available through QuickBooks Online API.

QODBC Online will display reports/tables/fields whatever available through QuickBooks Online API.

If any reports/tables/fields is not available through QuickBooks Online API, then QODBC Online could not do it either.

You can get QODBC Online table schema from below link:
http://doc.qodbc.com/qodbc/online/index.php

Please refer below mentioned link for How to use the QuickBooks Reporting Engine with QODBC Online:
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2712

If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need following information, I kindly request you to attach below listed files when replying to the ticket.

1) Screenshot of QODBC Online Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Online Setup Screen -- > Messages -- > Review QODBC Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
SueAnn Allen
19 April 2017 02:14 PM
Why when I run the sp_report InventoryValuationSummary the column ItemDesc is blank? I know there is data there...
Jack
20 April 2017 07:05 AM
Hi,

I'm sorry to inform you that the Item Description field in InventoryValuationSummary 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.

Please refer:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2717

Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).