CreateProfitAndLossDetailReport
Generates a Profit and Loss Detail report schema file, listing individual income and expense transactions to support profit and loss analysis.
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 and NonTracking. The available columns will differ depending on whether multicurrency/NonTracking is enabled or disabled. Below are the columns supported for each state: multicurrency enabled or disabled.
Multicurrency disabled and NonTracking disabled: subt_nat_amount, rbal_nat_amount
Multicurrency disabled and NonTracking enabled: subt_nat_amount_nt, rbal_nat_amount_nt
Multicurrency enabled and NonTracking disabled: subt_nat_home_amount, rbal_nat_home_amount
Multicurrency enabled and NonTracking enabled: subt_nat_home_amount_nt, rbal_nat_home_amount_nt
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 CreateProfitAndLossDetailReport
To return BASE64 encoded data in FileData column.
EXECUTE CreateProfitAndLossDetailReport WriteToFile = 'False'
Input
| Name | Type | Accepts Output Streams | Description |
| ReportName | String | False | The name of the report that is to be generated. This name identifies which report definition QuickBooks Online should use.
The default value is ProfitAndLossDetailReport. |
| ReportDescription | String | False | A description for the report. If the report description is not specified, QuickBooks Online selects a description based on the report type. |
| AccountType | String | False | Filters the report contents to include transactions from one or more specified account types. Provide a comma-separated list (with one space after each comma) of the account types including Bank, AccountsReceivable, OtherCurrentAsset, FixedAsset, OtherAsset, AccountsPayable, CreditCard, OtherCurrentLiability, LongTermLiability, Equity, Income, CostOfGoodsSold, Expense, OtherIncome, OtherExpense, or NonPosting. |
| AccountingMethod | String | False | The accounting method that is used in the report (for example, Cash or Accrual).
The allowed values are Cash, Accrual. |
| Account | String | False | Filters the report contents to include information for one or more specified accounts. Provide a comma-separated list (with one space after each comma) of Account identifiers (Ids). |
| SourceAccountType | String | False | Filters the report contents to include transactions from one or more specified source account types. Provide a comma-separated list (with one space after each comma) of account types including Bank, AccountsReceivable, OtherCurrentAsset, FixedAsset, OtherAsset, AccountsPayable, CreditCard, OtherCurrentLiability, LongTermLiability, Equity, Income, CostOfGoodsSold, Expense, OtherIncome, OtherExpense, or NonPosting. |
| Class | String | False | Filters the report contents to include information for one or more specified classes when the company file is configured to use classes. Provide a comma-separated list (with one space after each comma) of Class Ids. |
| Columns | String | False | The column types that are to be shown in the report. Provide a comma-separated list (with one space after each comma) of one or more of the following types: create_by, create_date, doc_num, last_mod_by, last_mod_date, memo, name, pmt_mthd, split_acc, tx_date, txn_type. When tax is enabled, the additional column tax_code is available. When class tracking is enabled, the additional column klass_name is enabled. When location tracking is enabled, the column dept_name is available. In addition, the following values are supported based on the multicurrency and NonTracking settings for the company. When multicurrency is disabled and NonTracking is disabled, the supported values are subt_nat_amount and rbal_nat_amount. When multicurrency is disabled and NonTracking is enabled, the supported values are subt_nat_amount_nt and rbal_nat_amount_nt. When multicurrency is enabled and NonTracking is disabled, the supported values are subt_nat_home_amount and rbal_nat_home_amount. When multicurrency is enabled and NonTracking is enabled, the supported values are subt_nat_home_amount_nt and rbal_nat_home_amount_nt. |
| Customer | String | False | Filters the report contents to include information for one or more specified customers. Provide a comma-separated list (with one space after each comma) of Customer Ids. |
| DateMacro | String | False | A predefined date range that is used to automatically populate the StartDate and EndDate inputs. Use this input when you want the report to cover a standard reporting range without specifying dates manually.
The allowed values are Today, Yesterday, This Week, This Week-to-date, Last Week, Last Week-to-date, Next Week, Next 4 Weeks, This Month, This Month-to-date, Last Month, Last Month-to-date, Next Month, This Fiscal Quarter, This Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year, Last Fiscal Year-to-date, Next Fiscal Year. |
| Department | String | False | Filters the report contents to include information for one or more specified departments when the company file is configured to use departments. Provide a comma-separated list (with one space after each comma) of Department Ids. |
| Employee | String | False | Filters the report contents to include information for one or more specified employees. Provide a comma-separated list (with one space after each comma) of Employee Ids. |
| PaymentMethod | String | False | Filters the report contents based on one or more specified payment methods. Provide a comma-separated list (with one space after each comma) of payment methods, including Cash, Check, Dinners Club, AmericanExpress, Discover, MasterCard, or Visa. |
| SortBy | String | False | The column that is used to sort report rows. Specify one of the columns from the Columns input to control the sort order. |
| SortOrder | String | False | The sort order for the report rows (for example, Ascending or Descending).
The allowed values are ascend, descend. The default value is ascend. |
| StartDate | String | False | The starting date of the report. This date must be earlier than the ending date. |
| EndDate | String | False | The ending date of the report. This date must be later than the starting date. |
| Vendor | String | False | Filters the report contents to include information for one or more specified vendors. Provide a comma-separated list (with one space after each comma) of Vendor Ids. |
| IncludeUnrealizedGainOrLoss | String | False | Specifies whether unrealized gains and losses are included in the report. Supported values are true or false. This input is supported only in Canada-based accounts. |
| Indentation | String | False | Indicates whether spaces are added at the start of values to visually identify totals and grouped values in the report output. |
| FileStream | String | True | An instance of an output stream where file data is written. This FileStream input is used only when the WriteToFile parameter is set to false. |
| WriteToFile | String | False | 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 whether the stored-procedure execution completed successfully or failed. The stored procedure returns the Result output so that calling applications can check the execution status. |
| SchemaFile | String | The generated schema file that is produced by the stored procedure. The schema file contains the structure of the report. |
| Columns | String | The number of columns that are found in the generated report. This output helps applications validate and map returned data. |
| FileData | String | The Base64-encoded downloaded file content. The FileData output is returned only when the WriteToFile parameter is set to false and the FileStream parameter is not set. |