CreateProfitAndLossDetailReport
Generates a Profit and Loss Detail 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 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 | Description |
| ReportName | String | The name of the report.
The default value is ProfitAndLossDetailReport. |
| ReportDescription | String | A description for the report. If one is not specified, a description based on the ReportType will be selected. |
| AccountType | String | Account type from which transactions are included in the report. This is a comma separated list (spaces omitted after/before separating comma) including: Bank, AccountsReceivable, OtherCurrentAsset, FixedAsset, OtherAsset, AccountsPayable, CreditCard, OtherCurrentLiability, LongTermLiability, Equity, Income, CostOfGoodsSold, Expense, OtherIncome, OtherExpense, NonPosting. |
| AccountingMethod | String | The accounting method used in the report.
The allowed values are Cash, Accrual. |
| Account | String | Filters report contents to include information for specified accounts. This is a comma separated list (spaces omitted after/before separating comma) of one or more Item IDs. |
| 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. |
| 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. |
| Columns | String | Column types to be shown in the report. A comma separated list of one or more of the following: create_by, create_date, doc_num, last_mod_by, last_mod_date, memo, name, pmt_mthd, split_acc, tx_date, txn_type. Additional columns with tax enabled: tax_code. Additional columns with class tracking enabled: klass_name. Additional columns with location tracking enabled: dept_name. In addition to the above, the following values are supported based on the multicurrency and NonTracking setting for the company - 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 |
| 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, 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 | 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. |
| Employee | String | Filters report contents to include information for specified employees. This is a comma separated list (spaces omitted after/before separating comma) of one or more Item IDs. |
| PaymentMethod | String | Filter report contents based on payment method. This is a comma separated list (spaces omitted after/before separating comma) including: Cash, Check, Dinners Club, AmericanExpress, Discover, MasterCard, Visa. |
| 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. The default value is ascend. |
| 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. |
| 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. |