Knowledgebase: Sp_commands & Reports
[QODBC-Desktop] How to use the QuickBooks Reporting Engine with QODBC
Posted by Juliet (QODBC Support) on 30 March 2010 08:32 AM
Note: VB Demo is deprecated.

Please refer to How to use the QODBC Test Tool for testing

Instructions

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

     QODBC also supports extracting 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.

     To run the Stored Procedure, click on "Start" and "Programs" and locate QODBC Driver for the QuickBooks program group. Click on the QODBC Test Tool icon to launch the program supplied with QODBC. To establish a connection, select the "QuickBooks Data" DSN from the dropdown list, and Click on "Connect."

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

     In other applications, you can also run sp_report in MS Excel/MS Access. Refer: How to create sp_reports using Microsoft Excel

     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 Start and Programs and locate QODBC Driver for the 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 = 'last quarter, 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 = 2021, 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 = 2021, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

       sp_report ProfitAndLossBudgetPerformance show Amount_Title, Text, Label, Amount parameters FiscalYear = 2021, 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 = 2021, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'

       sp_report BalanceSheetBudgetOverview show Amount_Title, Label, Amount parameters FiscalYear = 2021, 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 = 2021, BudgetCriterion = 'Accounts', SummarizeBudgetColumnsBy = 'Date', SummarizeBudgetRowsBy = 'Account'  

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

Also, Refer

How to use QODBC Support Wizard

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

(153 vote(s))
Helpful
Not helpful

Comments (28)
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.

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 refer below listed article 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 application is 32 bit) or “QuickBooks Data-64bit QRemote” (if the application 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

Ranier
21 May 2017 03:13 PM
Can somebody tell me how to see sp_report in crystal reports thanks in advance.
Jack
22 May 2017 12:15 PM
Hi Ranier,

Please refer below mentioned article for How to use QODBC with Crystal Reports:
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/1565/0/how-to-use-qodbc-with-crystal-reports
Holden
07 August 2017 09:02 PM
Can anyone help me pull multiple Accounts on one report? For example, the below query works with one account but when I add another account (i.e. 'Distributor Sales"), I receive the error "SumamrizeRowsBy is Required" Any ideas? Thanks!

sp_report CustomTxnDetail show TxnType, Date, RefNumber, Name, Memo, Class,
Account, SplitAccount, Debit, Credit
parameters DateFrom = {d'2017-01-01'}, DateTo={d'2017-12-31'}, AccountFilterFullNames = 'WholeSale Sales' 'Distributor Sales', SummarizeRowsBy = 'Account'
Jack
08 August 2017 12:01 PM
Hi Holden,

I would like to share that there is syntax issue in report query between multiple Accounts names there should be comma as below.

AccountFilterFullNames = 'WholeSale Sales', 'Distributor Sales'

Please use below query & check again:
sp_report CustomTxnDetail show TxnType, Date, RefNumber, Name, Memo, Class, Account, SplitAccount, Debit, Credit parameters DateFrom = {d'2017-01-01'}, DateTo={d'2017-12-31'}, AccountFilterFullNames = 'WholeSale Sales', 'Distributor Sales',SummarizeRowsBy = 'Account'

If you are still facing issue, 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 the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Jeffrey Shear
27 October 2017 01:18 AM
Can sp_reports be used directly through MS SQL server if the QODBC connection is a linked server? If yes, is there an article or an example of this?
Jack
27 October 2017 09:03 AM
Hi Jeffrey,

You can execute sp_report from SQL Server using below sample query:

For Example:
select * from openquery (QRemote,'sp_report OpenSalesOrderByCustomer show Blank,TxnType,Date,RefNumber,Memo,Amount,OpenBalance parameters DateFrom = {d''2017-01-01''}, DateTo={d''2017-12-31''}')

Same way you can run other reports. You can change parameter & report columns as per your requirements.

Please refer:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2367/0/how-to-do-inserts-updates-and-deletes-using-openquery-with-linked-ms-sql-servers

If you are still the facing issue, 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 the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Michael
25 January 2018 09:16 PM
Instead of doing DateMacro = 'ThisWeek', is there a way to extract only the last 5 days worth of data?
I know that with tables, I can do:
WHERE TimeModified >= {{ts \\'{0:yyyy-MM-dd} 00:00:00.000\\'}}', Date.AddDays(SliceStart, -5)) but I am wondering if there is a way to do that for reports. Thanks!
Jack
26 January 2018 01:56 PM
Hi Michael,

I would like to share that date function does not support in sp_report query. You can dynamically assign date parameter into the sp_report query. To achieve this, You need to use VBA in excel & write some code which will pass date parameter in sp_report query.

Please refer below mentioned link for How to use Date() And DateAdd() function in QODBC:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2638/

If you are still the facing issue, 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 the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).