CreateTransactionListReport
Generates a Transaction List report schema file.
Stored Procedure Specific Information
QuickBooks Online allows only a small subset of columns to be used in the Exec query. All the columns are optional for this Stored Procedure.
This report supports multicurrency. The available columns will differ depending on whether multicurrency is enabled or disabled. Below are the columns supported for each state: multicurrency enabled or disabled.
Multicurrency disabled: debt_amt, credit_amt, nat_open_bal, subt_nat_amount, tax_amount, net_amount.
Multicurrency enabled: debt_home_amt, credit_home_amt, currency, exch_rate, nat_home_open_bal, nat_foreign_open_bal, subt_nat_home_amount, nat_foreign_amount, home_tax_amountm foreign_tax_amount, home_net_amount, foreign_net_amount.
To determine if the authenticated account supports multicurrency, check with the following statement:
SELECT CurrencyPrefs_MultiCurrencyEnabled FROM Preferences;
For example:
To create the RSD file, it will be generated based on the value of the Location connection property.
EXECUTE CreateTransactionListReport
To return BASE64 encoded data in FileData column.
EXECUTE CreateTransactionListReport WriteToFile = 'False'
Input
| Name | Type | Description |
| ReportName | String | The name of the report.
The default value is TransactionListReport. |
| ReportDescription | String | A description for the report. If one is not specified, a description based on the ReportType will be selected. |
| AccountingMethod | String | The accounting method used in the report.
The allowed values are Cash, Accrual. |
| APPaid | String | Status of the balance.
The allowed values are Paid, Unpaid, All. |
| ARPaid | String | The accounts receivable paid status.
The allowed values are Paid, Unpaid, All. |
| BothAmount | String | Filters report contents to include information for specified transaction amount. |
| Class | String | Filters report contents to include information for specified classes if so configured in the company file. This is a comma separated list (spaces omitted after/before separating comma) of one or more Class IDs. |
| Cleared | String | Filters report contents to include information for specified check status.
The allowed values are Cleared, Uncleared, Reconciled, Deposited. |
| Columns | String | Column types to be shown in the report. A comma separated list of one or more of the following: account_name, create_by, create_date, cust_msg, dued_date, doc_num, inv_date, is_ap_paid, is_cleared, is_no_post, last_mod_by, memo, name, other_account, pmt_mthd, printed, sales_cust1, sales_cust2, sales_cust3, term_name, tracking_num, tx_date, txn_type. Additional columns when account numbering enabled: account_num. Additional columns when location tracking enabled: dept_name. In addition to the above, the following values are supported based on the multicurrency setting for the company. Multicurrency disabled: debt_amt, credit_amt, nat_open_bal, subt_nat_amount, tax_amount, net_amount. Multicurrency enabled: debt_home_amt, credit_home_amt, currency, exch_rate, nat_home_open_bal, nat_foreign_open_bal, subt_nat_home_amount, nat_foreign_amount, home_tax_amountm foreign_tax_amount, home_net_amount, foreign_net_amount. Default= tx_date, txn_type, doc_num, is_no_post, name, account_name, other_account, subt_nat_amount |
| CreateDateMacro | String | A predefined account create date range. Use this if you want the report to cover a standard date range when accounts were created. Otherwise, use the StartCreateDate and EndCreateDate to cover an explicit range.
The allowed values are Today, Yesterday, This Week, Last Week, This Week-to-date, Last Week-to-date, Next Week, Next 4 Weeks, This Month, Last Month, This Month-to-date, Last Month-to-date, Next Month, This Fiscal Quarter, Last Fiscal Quarter, This Fiscal Quarter-to-date, Last Fiscal Quarter-to-date, Next Fiscal Quarter, This Fiscal Year, Last Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year-to-date, Next Fiscal Year. |
| Customer | String | Filters report contents to include information for specified customers. This is a comma separated list (spaces omitted after/before separating comma) of one or more Customer IDs. |
| DateMacro | String | A predefined date range. Use this if you want the report to cover a standard report date range. Otherwise, use the StartDate and EndDate to cover an explicit range.
The allowed values are Today, Yesterday, This Week, Last Week, This Week-to-date, Last Week-to-date, Next Week, Next 4 Weeks, This Month, Last Month, This Month-to-date, Last Month-to-date, Next Month, This Fiscal Quarter, Last Fiscal Quarter, This Fiscal Quarter-to-date, Last Fiscal Quarter-to-date, Next Fiscal Quarter, This Fiscal Year, Last Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year-to-date, Next Fiscal Year. |
| Department | String | Filters report contents to include information for specified departments if so configured in the company file. This is a comma separated list (spaces omitted after/before separating comma) of one or more Department IDs. |
| DocNum | String | Filters report contents to include information for specified transaction number, as found in the docnum parameter of the transaction object |
| DueDateMacro | String | A predefined date range of due dates for balances to include in the report. Otherwise, use the StartDueDate and EndDueDate to cover an explicit range.
The allowed values are Today, Yesterday, This Week, Last Week, This Week-to-date, Last Week-to-date, Next Week, Next 4 Weeks, This Month, Last Month, This Month-to-date, Last Month-to-date, Next Month, This Fiscal Quarter, Last Fiscal Quarter, This Fiscal Quarter-to-date, Last Fiscal Quarter-to-date, Next Fiscal Quarter, This Fiscal Year, Last Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year-to-date, Next Fiscal Year. |
| GroupBy | String | The field in the transaction by which to group results.
The allowed values are Name, Account, Transaction Type, Customer, Vendor, Employee, Location, Payment Method, Day, Week, Month, Quarter, Year, None. |
| Item | String | Filters report contents to include information for specified items. This is a comma separated list (spaces omitted after/before separating comma) of one or more Item IDs. |
| Memo | String | Filters report contents to include information for specified memo. One or more comma separated memo IDs |
| ModDateMacro | String | A predefined report account modification date range. Use if you want the report to cover a standard report date range when accounts were modified; otherwise, use the StartModDate and EndModDate to cover an explicit report date range.
The allowed values are Today, Yesterday, This Week, Last Week, This Week-to-date, Last Week-to-date, Next Week, Next 4 Weeks, This Month, Last Month, This Month-to-date, Last Month-to-date, Next Month, This Fiscal Quarter, Last Fiscal Quarter, This Fiscal Quarter-to-date, Last Fiscal Quarter-to-date, Next Fiscal Quarter, This Fiscal Year, Last Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year-to-date, Next Fiscal Year. |
| PaymentMethod | String | Filters report contents based on payment method.
The allowed values are Cash, Credit, Dinners Club, American Express, Discover, Mastercard, Visa. |
| Printed | String | Filters report contents based on whether checks are printed or not.
The allowed values are All, Printed, To_be_printed. |
| SortBy | String | The column type used in sorting report rows. Specify one of the Columns from the Columns input. |
| SortOrder | String | The sort ordering.
The allowed values are ascend, descend. |
| SourceAccountType | String | Account type from which transactions are included in the report. This is a comma separated list (spaces omitted after/before separating comma) and may include: Bank,AccountsReceivable,OtherCurrentAsset,FixedAsset,OtherAsset,AccountsPayable,CreditCard,OtherCurrentLiability,LongTermLiability,Equity,Income,CostOfGoodsSold,Expense,OtherIncome,OtherExpense,NonPosting. |
| StartCreateDate | String | The startcreatedate of account creation to include in the report. StartCreateDate must be less than EndCreateDate. |
| EndCreateDate | String | The endcreatedate account creation to include in the report. EndCreateDate must be greater than StartCreateDate. |
| StartDate | String | The startdate of the report. StartDate must be less than EndDate. |
| EndDate | String | The enddate of the report. EndDate must be greater than StartDate. |
| StartDueDate | String | The startdate of which receivables are due. StartDueDate must be less than EndDueDate. |
| EndDueDate | String | The enddate of which recievables are due. EndDueDate must be greater than StartDueDate. |
| StartModDate | String | The startdate of which accounts were modified. StartModDate must be less than EndModDate. |
| EndModDate | String | The enddate of which accounts were modified. EndModDate must be greater than StartModDate. |
| Term | String | Filters report contents to include information for specified terms. This is a comma separated list (spaces omitted after/before separating comma) of one or more Term IDs. |
| TransactionType | String | Filters report based on transaction type.
The allowed values are CreditCardCharge, Check, Invoice, ReceivePayment, JournalEntry, Bill, CreditCardCredit, VendorCredit, Credit, BillPaymentCheck, BillPaymentCreditCard, Charge, Transfer, Deposit, Statement, BillableCharge, TimeActivity, CashPurchase, SalesReceipt, CreditMemo, CreditRefund, Estimate, InventoryQuantityAdjustment, PurchaseOrder, GlobalTaxPayment, GlobalTaxAdjustment, Service Tax Refund, Service Tax Gross Adjustment, Service Tax Reversal, Service Tax Defer, Service Tax Partial Utilisation. |
| Vendor | String | Filters report contents to include information for specified vendors. This is a comma separated list (spaces omitted after/before separating comma) of one or more Vendor IDs. |
| Indentation | String | Add spaces at the start of values to visually identify totals and group values. |
| WriteToFile | String | Whether to write to an output file or not. Defaults to true, must be set to false to write to FileStream or FileData. |
Result Set Columns
| Name | Type | Description |
| Result | String | Success or Failure. |
| SchemaFile | String | The generated schema file. |
| Columns | String | The number of columns found. |
| FileData | String | The BASE64 downloaded file content. Only returned if WriteToFile set to false and FileStream is not set. |