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 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
| 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
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| 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. |