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:
- When no parameters are specified, the report spans the current month:
SELECT * FROM ProfitAndLoss
- 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.
- 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.
- When Periods and Timeframe are specified, the report spans backwards from the current date through the specified number of periods:
SELECT * FROM ProfitAndLoss WHERE Periods = 2 AND Timeframe = 'MONTH'
Note: Periods accepts an integer value between 1 and 11.
- 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.
- When Periods, Timeframe, FromDate, and ToDate are all specified, the report spans the defined date range divided into the specified number of periods, using the specified date range as the base period and calculating prior periods from there. This is the recommended approach for retrieving monthly data for years other than the current year:
SELECT * FROM ProfitAndLoss WHERE Periods = 11 AND Timeframe = 'MONTH' AND FromDate = '2025-12-01' AND ToDate = '2025-12-31'
Note: When using the 'MONTH' timeframe, the specified date range applies to each prior period. If your base range covers 30 days, each previous period will also only include the first 30 days. To ensure all periods return a full month of data, use a month with 31 days as your base range.
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 [KEY] | 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 more granular control over the data 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. |