Cmdlets for Xero

Build 22.0.8479

ProfitAndLoss

Query a Profit and Loss report over a date range.

Table-Specific Information

Select

There are a few different ways the range of dates can be specified when using this report:

1. When no parameters are specified, the report is run for the current month:

SELECT * FROM ProfitAndLoss

2. When just FromDate is specified, the report is run from FromDate to today:

SELECT * FROM ProfitAndLoss WHERE FromDate = '2018-01-01'
In this form, FromDate must be within a year of the current date.

3. When FromDate and ToDate are specified, the report is run from FromDate to ToDate:

SELECT * FROM ProfitAndLoss WHERE FromDate = '2018-01-01' AND ToDate = '2018-04-01'
In this form, FromDate must be within a year of ToDate.

4. When Periods and Timeframe are specified, the report is run from today back to the start of the Timeframe:

SELECT * FROM ProfitAndLoss WHERE Periods = 2 AND Timeframe = 'MONTH'

Note: When specifying the 'Month' timeframe, the Xero endpoint may generate a report for the last 30 days from the start of the specified month, even for the months with 31 days. As a workaround, you should specify a 31-day month in the ToDate column as an additional query criteria.

SELECT * FROM ProfitAndLoss WHERE Periods = 11 AND Timeframe = 'MONTH' AND ToDate='2020-12-31'

5. When Periods, Timeframe and ToDate are specified, the report is run from ToDate back to the start of the Timeframe:

SELECT * FROM ProfitAndLoss WHERE Periods = 2 AND Timeframe = 'MONTH' AND ToDate = '2018-04-01'

Columns

Name Type Description
ID [KEY] Integer The row number.
Label1 String The Label column of the ProfitAndLossStandard report.
Label2 String The Value column of the ProfitAndLossStandard report.
Label3 String The Label column of the ProfitAndLossStandard report.
Label4 String The Value column of the ProfitAndLossStandard report.
Label5 String The Label column of the ProfitAndLossStandard report.
Label6 String The Value column of the ProfitAndLossStandard report.
Label7 String The Label column of the ProfitAndLossStandard report.
Label8 String The Value column of the ProfitAndLossStandard report.
Label9 String The Label column of the ProfitAndLossStandard report.
Label10 String The Value column of the ProfitAndLossStandard report.
Label11 String The Label column of the ProfitAndLossStandard report.
Label12 String The Value column of the ProfitAndLossStandard report.
Label13 String The Value column of the ProfitAndLossStandard report.
AccountId String The ID of the row's account from the ProfitAndLossStandard report.
TenantId String The ID of the tenant to query instead of the connection tenant

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。

Name Type Description
FromDate Datetime Show all payable invoices from this date for contact.
ToDate Datetime Show all payable invoices to this date for the contact.
Periods Int The number of periods to go back from ToDate.
Timeframe String How big each period should be (MONTH, QUARTER or YEAR)
TrackingCategoryID String If you specify the trackingCategoryID parameter then the Profit and Loss Report will show figures for each of the options in the category as separate columns.
TrackingOptionID String If you specify this parameter in addition to the trackingCategoryID then just one option will be returned (i.e. 1 column only)
TrackingCategoryID2 String If you specify a second trackingCategoryID parameter then the Profit and Loss Report will show figures for each combination of options from the two categories as separate columns.
TrackingOptionID2 String If you specify this parameter in addition to a second trackingCategoryID then just one option will be returned combined with the option/s from the first tracking category.
PaymentsOnly Boolean Set this to true to get cash transactions only.
StandardLayout Boolean If you set this parameter to true then no custom report layouts will be applied to response.

Copyright (c) 2023 CData Software, Inc. - All rights reserved.
Build 22.0.8479