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 spans the current month:

SELECT * FROM ProfitAndLoss

2. When just FromDate is specified, the report spans from FromDate to the current date:

SELECT * FROM ProfitAndLoss WHERE FromDate = '2018-01-01'

Note: 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'

Note: FromDate must be within a year of ToDate.

4. When Periods and Timeframe are specified, the report spans from the current date to the start of the specified Timeframe:

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

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

SELECT * FROM ProfitAndLoss WHERE Periods = 11 AND Timeframe = 'MONTH' AND FromDate= '2023-10-01' AND ToDate= '2023-10-31'

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

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

Note: The report start date is set as the first of the current month. ToDate must be set as a later date, within 365 days of the first of the current month.

Columns

Pseudo-Columns

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