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

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.