Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
 Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : How do I use the QuickBooks Reporting Engine with QODBC? I've heard something about sp_report ?Search Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC Reporting Forum

 New Topic 
 
 Post Reply 
[1]  
 How do I use the QuickBooks Reporting Engine with QODBC? I've heard something about sp_report ? 
 Author   Message 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-02-21 14:02:49
This FAQ is QODBC Stored Procedure Reports 101:

QODBC works by accepting SQL commands through the ODBC Interface, then converting those calls to qbXML navigational commands within the QuickBooks SDK and returning record sets 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 printing of all the QuickBooks Reports through the ODBC Interface. I've been slowly building up the range of QuickBooks reports available in a range of reports found if you search for: "How do I run a".

With the release of QODBC v6.00.00.176 and later you can now use the new stored procedure SP_REPORTS to list all the available sp_reports.

And see all the columns available in a given SP_REPORT by using sp_reportcolumns reportname



As well as 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.

Stored Procedure reports (sp_reports) can also be used by Microsoft Excel 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).

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-02-21 14:09:55

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' (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'
 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2006-02-21 14:11:21
COMMON sp_report PARAMETERS PARAMETER VALUES
AccountFilterType =
DateFrom = {d'yyyy-mm-dd'}
DateTo = {d'yyyy-mm-dd'}
DateMacro =
ItemFilterType =
ModifedDateMacro =
TxnFilterTypes =
SummarizeColumnsBy = (Locate the sp_report for the correct parameter values to use)
BUDGET ONLY sp_report PARAMETERS
FiscalYear = Integer Year (Example: 2003)
BudgetCriterion =
SummarizeBudgetColumnsBy =
SummarizeBudgetRowsBy =

Note: The Stored Procedure SQL Scripts above are used in the testing of QuickBooks 2005 USA Edition. Differences may apply to other regions.

 

  Top 
  k4955 
  
 Group: Members 
 Posts: 2 
 Joined: 2007-11-09 
 Profile
 Posted : 2007-11-09 07:50:03
sp_report ProfitAndLossByClass show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisYearToDate', SummarizeColumnsBy = 'Class'


Is there anyway to change the DateMacro="ThisPeriodToDate"? 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-11-09 08:09:53

No, the only predefined DateMacro options available to you are:
|All|Today|ThisWeek|ThisWeekToDate|ThisMonth|ThisMonthToDate|ThisQuarter|ThisQuarterToDate
|ThisYear|ThisYearToDate|Yesterday|LastWeek|LastWeekToDate|LastMonth|LastMonthToDate|LastQuarter
|LastQuarterToDate|LastYear|LastYearToDate|NextWeek|NextFourWeeks|NextMonth|NextQuarter|NextYear|

See: Profit & Loss Summary Report by Class for more.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to