ProfitAndLoss
Used to query a Profit and Loss report for a specified date range, providing insights into the organization's income and expenses over the period. It is used to analyze financial performance and profitability.
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 that uniquely identifies each entry in the ProfitAndLossStandard report. This is used for indexing purposes. |
| Label1 | String | The label for the first column in the ProfitAndLossStandard report, typically representing the category or account name. |
| Label2 | String | The value for the first column in the ProfitAndLossStandard report, showing the financial data associated with the corresponding label. |
| Label3 | String | The label for the third column in the ProfitAndLossStandard report, used to categorize another set of financial data. |
| Label4 | String | The value for the third column in the ProfitAndLossStandard report, representing financial data associated with the Label3 category. |
| Label5 | String | The label for the fifth column in the ProfitAndLossStandard report, typically used for another financial category. |
| Label6 | String | The value for the fifth column in the ProfitAndLossStandard report, representing the financial data for Label5. |
| Label7 | String | The label for the seventh column in the ProfitAndLossStandard report. |
| Label8 | String | The value for the seventh column, showing financial data for Label7. |
| Label9 | String | The label for the ninth column in the ProfitAndLossStandard report. |
| Label10 | String | The value for the ninth column, displaying financial data for Label9. |
| Label11 | String | The label for the eleventh column, used to categorize another financial set. |
| Label12 | String | The value for the eleventh column, indicating the financial data associated with Label11. |
| Label13 | String | The value column for the thirteenth label in the ProfitAndLossStandard report, indicating the financial information. |
| AccountId | String | The unique identifier for the account associated with each row in the ProfitAndLossStandard report, helping link the data to specific financial accounts. |
| TenantId | String | The identifier of the tenant to query, used for multi-tenant environments to ensure the correct data is returned for the tenant of interest. |
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 | The start date for filtering the Profit and Loss report. This parameter ensures that only data from this date onwards is included in the report. |
| ToDate | Datetime | The end date for filtering the Profit and Loss report. This defines the date up to which the report data is included. |
| Periods | Int | The number of periods (months, quarters, or years) to look back from the 'ToDate'. This allows for comparing data across multiple periods. |
| Timeframe | String | Defines the size of each period in the Profit and Loss report. Valid values are 'MONTH', 'QUARTER', or 'YEAR', determining how data is grouped. |
| TrackingCategoryID | String | The ID of the tracking category used in the Profit and Loss report. Specifying this parameter will show figures for each option within the category as separate columns. |
| TrackingOptionID | String | The ID for a specific option within a tracking category. When combined with 'TrackingCategoryID', this will return only one column for that option. |
| TrackingCategoryID2 | String | The ID of a second tracking category. If specified, the report will show figures for combinations of options from both tracking categories as separate columns. |
| TrackingOptionID2 | String | The ID of a specific option within the second tracking category. This will return a single column combining this option with options from the first category. |
| PaymentsOnly | Boolean | Set to 'true' to restrict the report to cash transactions only, excluding credit-based transactions. |
| StandardLayout | Boolean | Set to 'true' to apply the standard layout to the report, overriding any custom layouts that may be configured. |