CreateTransactionListReport
Generates a Transaction List report schema file, listing transactions across accounts within a defined period.
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 that is assigned to the transaction list report. This value is used to label and identify the report in generated outputs and downstream integrations.
The default value is TransactionListReport. |
| ReportDescription | String | A descriptive summary of the transaction list report that explains its contents or purpose. If you do not specify one, the system automatically generates a description based on the selected report type. |
| AccountingMethod | String | The accounting method that is applied to the report calculations, such as Cash or Accrual. This method determines how income and expenses are recognized in the report.
The allowed values are Cash, Accrual. |
| APPaid | String | Indicates the accounts payable balance status that is used to filter the report contents. This parameter allows you to restrict the report to a specific payable status.
The allowed values are Paid, Unpaid, All. |
| ARPaid | String | Indicates the accounts receivable paid status that is used to filter the report contents. This parameter allows you to restrict the report to a specific receivable status.
The allowed values are Paid, Unpaid, All. |
| BothAmount | String | Filters the report contents to include transactions for a specified transaction amount or range of amounts. |
| Class | String | Filters the report contents to include transactions for specified classes that are configured in the company file. Provide a comma separated list (with a space after each comma) of one or more Class identifiers (Ids). |
| Cleared | String | Filters the report contents to include transactions with a specified check cleared status.
The allowed values are Cleared, Uncleared, Reconciled, Deposited. |
| Columns | String | Specifies the column types that are displayed in the report. Provide a comma separated list (with a space after each comma) of one or more of the following values: 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. The column account_num is also supported when account numbering is enabled. When location tracking is enabled, the dept_name column is available. Based on the multicurrency setting for the company, the following values are also supported. When multicurrency is disabled, the supported values are debt_amt, credit_amt, nat_open_bal, subt_nat_amount, tax_amount, and net_amount. When multicurrency is enabled, the supported values are 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_amount, foreign_tax_amount, home_net_amount, and foreign_net_amount.
The default value is tx_date,txn_type,doc_num,is_no_post,name,account_name,other_account,subt_nat_amount,subt_nat_home_amount. |
| CreateDateMacro | String | A predefined account creation date range that is applied to the report timeframe. Use this parameter for standard date periods when accounts were created; otherwise specify starting create date and the ending create date for a custom 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 the report contents to include transactions for specified customers. Provide a comma separated list (with a space after each comma) of one or more Customer identifiers (Ids). |
| DateMacro | String | A predefined date range that is automatically applied to the report timeframe, such as Last Month, Last Quarter, or This Year. Use this parameter for standard date periods; otherwise specify the start date and end date for a custom 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 the report contents to include transactions for specified departments that are configured in the company file. Provide a comma separated list (with a space after each comma) of one or more Department identifiers (Ids). |
| DocNum | String | Filters the report contents to include transactions for specified transaction numbers as found in the docnum parameter of the transaction object. |
| DueDateMacro | String | A predefined due date range that is applied to balances included in the report. Use this parameter for standard due date periods; otherwise specify the starting due date and the ending due date for a custom 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 | Specifies the field in the transaction that is used to group results in the report.
The allowed values are Name, Account, Transaction Type, Customer, Vendor, Employee, Location, Payment Method, Day, Week, Month, Quarter, Year, None. |
| Item | String | Filters the report contents to include transactions for specified items. Provide a comma separated list (with a space after each comma) of one or more Item Ids. |
| Memo | String | Filters the report contents to include transactions with specified memo values. Provide one or more comma separated memo Ids. |
| ModDateMacro | String | A predefined account modification date range that is applied to the report timeframe. Use this parameter for standard modification date periods when accounts were changed; otherwise specify the starting modification date and the ending modification date for a custom 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 the report contents based on the payment method that is used in the transactions.
The allowed values are Cash, Credit, Dinners Club, American Express, Discover, Mastercard, Visa. |
| Printed | String | Filters the report contents based on whether checks are printed or not.
The allowed values are All, Printed, To_be_printed. |
| SortBy | String | Specifies the column type that is used to sort report rows. Provide one of the column values specified in the Columns input. |
| SortOrder | String | Specifies the sort order that is applied to the report rows, such as ascending or descending.
The allowed values are ascend, descend. |
| SourceAccountType | String | Specifies the account types from which transactions are included in the report. Provide a comma separated list (with a space after each comma) of one or more of the following values: Bank, AccountsReceivable, OtherCurrentAsset, FixedAsset, OtherAsset, AccountsPayable, CreditCard, OtherCurrentLiability, LongTermLiability, Equity, Income, CostOfGoodsSold, Expense, OtherIncome, OtherExpense, or NonPosting. |
| StartCreateDate | String | The starting date of account creation that is included in the report. This defines the earliest account creation date to include and must be earlier than the ending date specified in the EndCreateDate parameter. |
| EndCreateDate | String | The ending date of account creation that is included in the report. This defines the latest account creation date to include and must be later than the starting date specified in the StartCreateDate parameter. |
| StartDate | String | The starting date of the report. This defines the earliest transaction date to include and must be earlier than the ending date specified in the EndDate parameter. |
| EndDate | String | The ending date of the report. This defines the latest transaction date to include and must be later than the starting date specified in the StartDate parameter. |
| StartDueDate | String | The starting date of receivables that are due and included in the report. This defines the earliest due date to include and must be earlier than the ending date specified in the EndDueDate parameter. |
| EndDueDate | String | The ending date of receivables that are due and included in the report. This defines the latest due date to include and must be later than the starting date specified in the StartDueDate parameter. |
| StartModDate | String | The starting date on which accounts that are included in the report were modified. This defines the earliest modification date to include and must be earlier than the ending date specified in the EndModDate parameter. |
| EndModDate | String | The ending date on which accounts that are included in the report were modified. This defines the latest modification date to include and must be later than the starting date specified in the StartModDate parameter. |
| Term | String | Filters the report contents to include transactions for specified terms. Provide a comma separated list (with a space after each comma) of one or more Term Ids. |
| TransactionType | String | Filters the report contents based on the transaction type that is used in the transactions.
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 the report contents to include transactions for specified vendors. Provide a comma separated list (with a space after each comma) of one or more Vendor Ids. |
| Indentation | String | Indicates whether to add leading spaces to values that are displayed in the report. This field helps visually differentiate groupings and totals within the output for improved readability. |
| WriteToFile | String | Indicates whether the stored procedure writes the output to a file. This input defaults to true. Set it to false to have the procedure write the output to the file stream that you supply or to return the output as file data. |
Result Set Columns
| Name | Type | Description |
| Result | String | Indicates the execution outcome of the stored procedure. Returns a value of Success when the report generation is successful or a value of Failure if it is not. |
| SchemaFile | String | The path or reference to the generated schema file that defines the structure of the returned transaction-list report data. |
| Columns | String | The number of columns that are present in the generated report schema. This number provides a quick count of the report's fields for validation or processing. |
| FileData | String | The Base64-encoded content of the generated report file. This output is returned only when the WriteToFile parameter is set to false and the FileStream parameter is not set, enabling you to decode and save the data programmatically. |