Reporting Query Language Syntax
Our custom made Reporting Query Language was designed to be simple to read and write yet powerful enough to run highly detailed reports. We elected to write our own query language as it allowed us to fit the language to the data and assist you with selecting the data you wish to report on.
The syntax for our Reporting Query Language is similar in design to SQL (Structured Query Language) so if you have used it in the past you should be able to easily transition into using our language. Whilst it is useful to know SQL, it is not a requirement as our language has several modifications that make writing queries in it simple.
NOTE: The Reporting Query Language Syntax is not case sensitive. UPPERCASE words in the documentation are keywords and lowercase words in the documentation are fields, statements or expressions.
Syntax Conventions
Table and column names must be commonly substituted into statements. We typically indicate this with type_name. For example, you might see a statement like this:
SELECT column_name FROM table_name BY by_name
This means that if you were to enter a similar statement, you would supply your own information such as:
SELECT revenue FROM sales BY outlet
Keywords, columns and tables are not case-sensitive and may be written in any case. In all of our examples, keywords are uppercase while columns and tables are lowercase.
In syntax descriptions, square brackets ("[" and "]") indicate optional words or clauses. For example, the following statement, `CONSOLIDATED` is optional:
SELECT
column_name
FROM table_name
BY by_name
[CONSOLIDATED]
When options are applicable in a syntax description, the alternatives are separated by vertical bars ("|"). Only one option can be chosen:
SELECT
column_name
[RANGE precision_expression | AGGREGATE precision_expression]
FROM table_name
BY by_name
When choice is required between multiple options, round brackets will be used to indicate required and vertical bars will list the options. One option must be selected:
SELECT
column_name
FROM table_name
BY by_name
(INCLUDE | EXCLUDE | ONLY) (CANCELLED | DELETED)
An ellipsis ("...") indicates that the preceding syntax element may be repeated. In the following example, multiple `select_expression` values may be provided, with each of those after the first preceded by commas:
SELECT select_expression [, select_expression ...]
Introduction
One of the most basic queries would be to display the total revenue made by an Outlet (similar to our sales summary report). You would write the query like this:
SELECT outlet, revenue
FROM sales
BY outlet
This would display a table similar to:
Outlet | Revenue |
Demo Outlet | $200.00 |
Let's go through this query line by line. More information is provided later in this document.
SELECT outlet, revenue
This is a simple Select Statement. This statement chooses which columns you would like to display in the table. A list of applicable columns is available on the left hand side of the Advanced Query Editor (when using a table or larger - it is not visible on mobile devices) and at the bottom of this document. Like SQL, you can perform arithmetic and you can also alias columns by using the AS keyword, e.g. `SELECT outlet, revenue AS "price"`.
FROM sales
This is a From Statement. This statement chooses which "table" we are selecting the data from (i.e. the source of the data).
BY outlet
This is a By Statement. This statement chooses how the data will be grouped when selected. This of this as the "Report Type" dropdown for our built-in reports. The By Statement is specific to our query language and is not available in SQL.
Syntax
The structure of our syntax is as follows:
Select Statement
From Statement
[Precision Statement]
By Statement
[On Statement]
[Where Statement]
[Start Statement]
[End Statement]
[Order Statement]
[Consolidate Statement]
[Status Statement]
[Limit Statement]
[Offset Statement]
The full syntax is as follows:
SELECT select_expression [, select_expression ...]
FROM from_expression [, from_expression]
[RANGE precision_expression | AGGREGATE precision_expression]
BY by_expression
[ON group_expression, ...]
[WHERE where_condition ...]
[START date_expression]
[END date_expression]
[ORDER order_expression, ...]
[CONSOLIDATED | UNCONSOLIDATED]
[status_expression]
[LIMIT limit_expression]
[OFFSET offset_expression]
All clauses used must be given in the exact order shown above.
Description of Clauses
Select Statement
As stated previously, the Select Statement chooses which columns you would like to display in your report. It works in a similar fashion to an SQL Select Statement.
The Select Statement allows you to perform arithmetic (such as 1 + 1, revenue - cost and revenue / cost) and also allows you to alias columns by using the AS keyword. This changes what the column names are and allows you to use complex calculations in future clauses without having the retype the calculation.
SELECT revenue - cost AS "Profit"
FROM sales
BY outlet
Unlike SQL, aliases can be used in Group Expressions.
Each column has a default way of calculating the total and we attempt to provide the best total for the data, but sometimes this isn't always possible. To provide you with a way to customise this you can use the optional TOTAL keyword. When you use this keyword you'll have to provide your own format function for the totals to be formatted. You currently have the following options to use:
- SUM,
- AVERAGE,
- MODE,
- MEDIAN,
- NONE
You can use the total keyword like this:
SELECT revenue - cost AS "profit" TOTAL(AVERAGE)
FROM sales
BY outlet
From Statement
The From Statement works in a similar fashion to SQL, it selects which data source (table in SQL) we are getting the data from. However, we have the ability to select data from multiple tables (instead of using joins in SQL), these are based off the first table which is then joined into the second table.
A typical query with a From Statement look like:
SELECT BY_NAME, revenue
FROM sales
BY outlet
And if you're selecting from multiple tables it looks like:
SELECT BY_NAME, sales.revenue, actions.timestamp
FROM sales, actions
BY outlet
When using multiple tables to select from, you'll need to specify what table you are selecting for all Select Expressions to avoid ambiguity.
At the moment on the following data sources are available to select from, however we are adding more data sources in the future:
- Sales (sales),
- Sales Payments (sales_payments),
- Orders (orders),
- Products (products),
- Actions (actions),
- Revisions (revisions),
- Inventory Log (inventory_log),
- Cash Movements (cash_movements)
The following tables can be used in a multi From Statement:
Primary Table | Secondary Tables |
Sales | Sales Payments, Actions |
Sales Payments | Sales |
Actions | Sales |
Precision Statement
Precision Statements are optional statements that allow you to generate precision reports. These display a single piece of data over a period of time (such as hourly revenue). Because of this, only one column can be used in the Select Statement. Using more than one column will result in an error.
When using a Precision Statement you will need to specify whether the data you are selecting is a Range or an Aggregate and then what level of precision you would like to view.
Range Precision Statement
A Range Precision Statement displays the data at the point of time it occurred. Over the course of two weeks when using the Date precision you will receive a report similar to:
Data | M 1 | T 1 | W 3 | T 4 | F 5 | S 6 | S 7 | M 8 | T 9 | W 10 | T 11 | F 12 | S 13 | S 14 |
Revenue | $1 | $2 | $3 | $4 | $5 | $6 | $1 | $2 | $3 | $4 | $5 | $6 | $7 | $8 |
Aggregate Precision Statement
An Aggregate Precision Statement on the other hand displays data grouped by the level of precision. Over the course of two weeks when using the Day precision (with the same data as above) you will receive a report similar to:
Data | M | T | W | T | F | S | S |
Revenue | $3 | $5 | $7 | $9 | $11 | $13 | $9 |
Precision Levels
The level of precision displays how the data is grouped and what columns are displayed. The available options are:
- Hour
- Date
- Week
- Month
- Year
- Day (Aggregate Only)
- Month Date (Aggregate Only)
- Quarter
Precision Statements can only be used with the following tables:
- Sales,
- Sales Payments,
- Orders
By Statement
The By Statement specifies how you wish to get your rows. This is essentially the same as the "Report Type" dropdown in the normal reports.
At the moment the following options are available:
- Sales
- Outlet
- Product
- Supplier
- Brand
- Category
- Family
- Tag
- Register
- User
- User Role
- Customer
- Customer Group
- Promotion
- Payment Method
- Tax
- Core Supplier
- Last Supplier
- Sales Payments
- Outlet
- Register
- User
- Role
- Customer
- Customer Group
- Payment Method
- Payment Type
- Purchases
- Outlet
- Product
- Supplier
- Brand
- Category
- Family
- Tag
- Transferee
- Products
- Product
- Actions
- Action
- Revisions
- Revision
- Inventory Log
- Product
- Cash Movements
- Outlet
- Register
- User
- Payment Method
On Statement
The On Statement is conceptually similar to SQL's Group By clause. It allows you to group the rows by a certain piece of data.
You're able to specify multiple options which will group your data by the 1st clause and then the 2nd clause until the nth clause.
Unlike SQL, you're also able to group by aliases.
SELECT revenue, ROUND(revenue - cost, 0) as "Rounded Profit"
FROM sales
BY user
ON register, "Rounded Profit"
Please refer to the By Statement to view what options are available to use.
Date Expression
Date Expressions allow you to limit or extends the amount of data you wish to view. You can enter any valid date and time which includes the following formats:
- yyyy-mm-dd
- yyyy-mm-dd hh:mm:ss
- yyyy-mm-ddThh:mm:ssz
- @timestamp
We also understand a lot of "human readable" formats such as:
- yesterday
- noon
- tomorrow
- first day of december 2017
- previous saturday
- last wednesday of december 2017
- monday next week
If you would prefer to write queries more similar to SQL we support arithmetic in Date Expressions. This can be used with one of the above formats or with one of our constants such as:
- TODAY
- ALL_OF_TIME
- CURRENT_TIMESTAMP
When a Date Expression is not included, it defaults to today. Also if you do not include a time with the format it will default to the start or end of that day (when using START or END respectively).
NOTE: We suggest you avoid using a format such as "01/06/2017" as it is ambiguous to our date and time parser. It will not throw an error, instead it will preform a "best guess" at the date. In this case, it would treat the date and time as the American mm/dd/yyyy. Instead you would use the yyyy-mm-dd format to avoid confusion.
Date Statements can not be used with the products table.
Where Statement
Where Statements allow you to constrict the amount of data you receive by applying conditions. You can compare against any of the columns available in the Select Expression. Our Where Statement is essentially the same as SQL's Where Clause
We support the following comparison operators:
- = (Equal To)
- > (More Than)
- < (Less Than)
- >= (More Than or Equal To)
- <= (Less Than or Equal To)
- <> (Not Equal To)
You can compare between columns or between literal values:
WHERE revenue > 100
WHERE revenue <= cost
The only exception to this rule is NULL values. These are values that do not exist. To compare against these values you must use IS NULL or IS NOT NULL:
WHERE user.role IS NOT NULL
We also support multiple conditions by using the AND and OR operators. When using both AND and OR together, the AND takes precedence. To avoid this from happening where you do not want it to, you should use brackets:
WHERE user.role IS NOT NULL AND (revenue < 100 OR revenue < cost)
Order Statement
The Order Statement determine what order the rows will be displayed. This is essentially the same as SQL's Order By Clause. You can specify which column you would like to order the rows by and the direction that they should be ordered (either ASCENDING or DESCENDING - ASC and DESC for short).
You can also use aliases in this statement and have multiple columns that you wish to order by.
ORDER product ASC, revenue DESC
Consolidate Statement
The Consolidate Statement determines if we should group all of the same fields from the By Statement together or whether all sales and orders should be individual rows (by using the CONSOLIDATED and UNCONSOLIDATED keywords respectfully).
When neither CONSOLIDATED or UNCONSOLIDATED keywords are specified, we default the query to CONSOLIDATED.
Status Statement
The Status Statement determines what level of "visibility" is required to display the results. The Status Statement is made up of two parts, the level of visibility (include, exclude or only) and what status to use (cancelled, deleted, voided and aborted). This statement is written like so:
(INCLUDE | EXCLUDE | ONLY) (CANCELLED | DELETED | VOIDED | ABORTED)
- INCLUDE status will include the status type in the results,
- EXCLUDE status will exclude the status type in the results,
- ONLY status will show only the status type in the results
At this point in time, CANCELLED, DELETED, VOIDED and ABORTED all mean the same thing, however in the future this may change so we recommend using the most appropriate status for your query.
The following query will show only cancelled sales in the report:
SELECT
BY_NAME,
revenue,
cost,
timestamp
FROM sales
BY outlet
UNCONSOLIDATED
ONLY CANCELLED
If no Status Statement is present, it will default to EXCLUDE CANCELLED for the sales table and EXCLUDE DELETED for all other tables.
Status Statement can only be used with the following tables:
- Sales
Limit Statement
The Limit Statement allows you to reduce the amount of data retrieved to a certain amount of rows. This clause works the same as MySQL's limit clause (and similar to MSSQL's fetch clause). The Limit Statement is commonly used with the Order Statement and the Offset Statement.
The following query will return the first 20 rows:
SELECT
BY_NAME,
revenue
FROM sales
BY outlet
ORDER revenue DESC
UNCONSOLIDATED
LIMIT 20
Offset Statement
The Offset Statement allows you to skip a certain amount of rows before returning the results. This clause works the same as SQL's offset clause. The Offset Statement is commonly used with the Order Statement and the Limit Statement.
The following query will skip the first 10 rows:
SELECT
BY_NAME,
revenue
FROM sales
BY outlet
ORDER revenue DESC
UNCONSOLIDATED
OFFSET 10
Constants
We use the term Constant to refer to a Constant Function (https://en.wikipedia.org/wiki/Constant_function) that we provide the input for automatically. They allow you to specify a dynamic piece of data that means the same thing across multiple queries. The most commonly used Constant is BY_NAME which allows you to get the name from the primary table.
Constant's are denoted in UPPERCASE.
List of Constants:
Constant | Description |
BY_NAME | The name of the primary table in the By Statement, e.g. `BY products` would display the product name. |
TODAY | The current date, typically used within a Date Expression. |
CURRENT_TIMESTAMP | The current date and time (to the second), typically used within a Date Expression. |
ALL_OF_TIME | Used within a Date Expression to denote that you want to view data over all of time. |
Functions
FORMAT
Formats a number into a string using the format you specify.
Signature:
FORMAT(column, prepend, append, decimal_places, thousands, decimal, drop_decimals)
- column (string): The column to format,
- prepend (string): What to prepend the string with,
- Default: ""
- append (string): What to append the string with,
- Default: ""
- decimal_places (integer): How many decimal places to round to,
- Default: 2
- thousands (string): What character to use as the thousands separator,
- Default: ","
- decimal (string): What character to use as the decimal place,
- Default: "."
- drop_decimals (boolean): Whether decimals should be dropped if the equal zero
- Default: false
Examples:
For the following examples, revenue equals 1234.5678.
FORMAT(revenue) // 1,234.57
FORMAT(revenue, "$", "", 3) // $1,234.568
FORMAT(revenue, "", "€", 6, ".", ",") // 1.234,567800€
For the following examples, revenue equals 11
FORMAT(revenue) // 11.00
FORMAT(revenue, "$", "", 2, ",", ".", true) // $11
Applicable Clauses:
- SELECT
DATE_FORMAT
Formats a date into the format you specify.
Signature:
DATE_FORMAT(column, format)
- column (string): The column to format,
- format (string): The format string to use (see the available formats to use)
Applicable Clauses:
- SELECT,
- ON
PERCENTAGE_FROM_TOTAL
Divide a column back into the total to determine what percentage that row is of the total.
Signature:
PERCENTAGE_FROM_TOTAL(column)
- column (string): The column to use
Examples:
SELECT BY_NAME as name, revenue, PERCENTAGE_FROM_TOTAL(revenue) as revenue_percentage
FROM sales
BY product
NAME | REVENUE | REVENUE_PERCENTAGE |
A | 10 | 16.67% |
B | 20 | 33.33% |
C | 30 | 50% |
TOTAL | 60 | 100% |
Applicable Clauses:
- SELECT
CONCAT
Join multiple strings together to equal a single string.
Signature:
CONCAT(string [, string...])
- string (string): A string to concatenate, can be a column or a literal string
Examples:
For the following examples, first_name equals "foo" and last_name equals "bar"
CONCAT(first_name, last_name) // foobar
CONCAT(first_name, " ", last_name) // foo bar
CONCAT("hello", " ", first_name, " ", last_name) // hello foo bar
Applicable Clauses:
- SELECT
ROUND
Round a number to a certain number of decimal places.
When rounding, we apply the "round half up" rule, when you have a value with a fractional part of .5 or greater is rounded up to the next integer if positive, or down to the next integer if negative (rounded away from zero). A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative (rounded towards zero).
Signature:
ROUND(number, decimal_places)
- number (decimal): The number to round, can be a column or a number literal,
- decimal_places (integer): The number of decimal places to round to, can be a negative to round the number of digits left of the decimal place to round
- Default: 0
Applicable Clauses:
- SELECT
CEILING
Round a number up.
Signature:
CEILING(number)
- number (decimal): The number to round up, can be a column or a number literal
Applicable Clauses:
- SELECT
FLOOR
Round a number down.
Signature:
FLOOR(number)
- number (decimal): The number to round down, can be a column or a number literal
Applicable Clauses:
- SELECT
ABS
Return the absolute value of a number.
Signature:
ABS(number)
- number (decimal): The number to obtain the absolute value for, can be a column or a number literal
Applicable Clauses:
- SELECT
TRUNCATE
Truncate a number to a certain number of decimal places (rounded towards zero).
Signature:
TRUNCATE(number, decimal_places)
- number (decimal): The number to truncate,
- decimal_places (integer): The amount of decimal places to truncate to
- Default: 0
Applicable Clauses:
- SELECT
SUBSTRING
Obtain a string between two positions within a string.
Signature:
SUBSTRING(string, position, length)
- string (string): The string to obtain the substring from, can be a column or a literal string,
- position (integer): The position to start the substring from
- length (integer): The length of the substring to obtain from the position
- Default: The remainder of the string
You can also provide negative values for position to set the start of the substring from the end of the string rather than the start.
Examples:
SUBSTRING("abcdefg", 3) // "defg"
SUBSTRING("abcdefg", 4, 2) // "ef"
SUBSTRING("abcdefg", -3) // "efg"
SUBSTRING("abcdefg", -4, 2) // "de"
Applicable Clauses:
- SELECT
LINK
Create a link for the provided value
Signature:
LINK(display, url)
- display (string): The value to display,
- url (string): The URL to create the link for
Examples:
LINK(invoice_number, CONCAT("/sale/", sale_id)) // "<a href='/sale/11e6...'>123</a>"
Applicable Clauses:
- SELECT