Data types for building widgets
When writing SQL queries, it is important to understand the type of value returned from ClickHouse, as this determines how the data will be interpreted and how it can be displayed and formatted for the end user.
Important: certain data types can be obtained from a query in three ways.
- Simple data selection: directly selecting values from the #tasks or #events tables;
- Data casting: explicit casting of data to the required type in the SQL query itself.
- Data aggregation: performed using aggregate functions such as COUNT, SUM, AVG, and others.
Data types for SQL queries
Numeric type
Numeric types are used for calculations, aggregations, and building the Y-axis (values) on graphs.
Numeric data types in Pyrus are represented in ClickHouse by the following types: Int8, Int16, Int32, UInt32, Int64, Nullable(Int64), UInt64, Float64, Nullable(Float64).
Money type field
Example: A sales form contains a Money type field with the code ${Money}, where managers enter the value of a closed deal. We need to determine how many open tasks are associated with each deal amount.
SELECT
${Money} AS "Total of deals",
COUNT(task_id) AS "Number of tasks"
FROM #tasks
WHERE is_closed = false
GROUP BY ${Money}
ORDER BY ${Money}
LIMIT 100;
Pyrus Tip: instead of the field code ${Money}, other numeric type fields can be used, while strictly numeric values will be displayed on the graph, such as ${Number}, ${Contact}, responsible_id, step, and others.
Contact type field
Example: A Customer Request Processing form contains a Contact type field with the code ${Responsible}, specifying the employee assigned to handle the request. We want to view employee workloads: how many active requests are assigned to each person.
A "Contact"-type field returns a user ID. To display the employee's name instead of a number, we use the formatting parameter #person_full_name.
SELECT
${Responsible} AS "#person_full_name('Responsible')",
COUNT(task_id) AS "Number of tasks"
FROM #tasks
WHERE is_closed = false
GROUP BY ${Responsible}
ORDER BY COUNT(task_id) DESC
LIMIT 100;
Pyrus Tip: Instead of the field code ${Responsible}, you can use system fields such as responsible_id (current assignee), creator_id (task creator), and others.
Choice type field
Example: A Help Desk form contains a Choice type field with the code ${Channel}, where the operator indicates the source of the request: email, phone call, chat, or messenger. We want to see which channels generate the most open requests.
By default, all values in a Choice type field are numeric (option IDs). To display channel names instead of numbers in the report, we use the alias #choice_name, where Channel of contact is the column name in the table and ${Channel} is the Choice type field code from the form.
SELECT
${Channel} AS "#choice_name(${Channel},'Channel of contact')",
COUNT(task_id) AS "Number of tasks"
FROM #tasks
WHERE is_closed = false
GROUP BY ${Channel}
ORDER BY COUNT(task_id) DESC
LIMIT 100;
Pyrus Note: The indices [N] and [*] allow you to access specific field values: [N] refers to the Nth selected item, while [*] refers to all elements in the array.
Form type field
Example: The Projects form contains a Form type field with the code ${Form}, used to link a task from the Contracts form to the project. We want to see which contracts are associated with the highest number of active projects.
By default, the values of a Form type field are numeric task IDs. To display the contract name and a link to it instead of the ID, we use the #task alias, where Title is the column header and ${Form} is the code of the Form type field.
SELECT
${Form} AS "#task('Title')",
COUNT(task_id) AS "Number of tasks"
FROM #tasks
WHERE is_closed = false
GROUP BY ${Form}
ORDER BY COUNT(task_id) DESC
LIMIT 100;
Pyrus Note: The [N] and [*] indices allow you to reference specific field values: [N] refers to the Nth selected task, while [*] refers to all tasks.
Date and Time
The Date and Time format is used to build a time series chart.
In Pyrus, Date and Time are represented in ClickHouse by the following types: DateTime64(3, 'UTC'), Nullable(DateTime64(9, 'UTC').
Example: let's see how open and closed tasks are distributed for values of the Date and Time type field within a specified time interval.
The report should display all tasks that have a filled field with the code ${Date} and specify the Start date and End date of the event or process.
SELECT
DATE(${Date}) AS "User date",
COUNT(task_id) FILTER (WHERE is_closed = false) AS "Open tasks",
COUNT(task_id) FILTER (WHERE is_closed = true) AS "Closed tasks"
FROM #tasks
WHERE ${Date} >= @period_start AND ${Date} <= @period_end
GROUP BY DATE(${Date})
ORDER BY DATE(${Date})
LIMIT 100;
Pyrus Tip: Instead of the field code ${Date}, you can use the codes of other fields of type Date and Time, for example due_date, create_date, or the code of a field of type Time.
Logical type
Used for forming logical selections based on query conditions. The logical data type is represented in ClickHouse by the types Bool and Nullable(Bool).
Check mark type field
Example: we will sort tasks by the selected value in the Checkbox field, we will output the value of the Check mark field. ${Checkbox} — the code of the Check mark field in the Pyrus form.
SELECT
${Checkbox} AS "Checkbox value",
COUNT(task_id) AS "Number of tasks"
FROM #tasks
WHERE is_closed = false
GROUP BY ${Checkbox}
ORDER BY COUNT(task_id) DESC
LIMIT 100;
Text type
Used for naming data on charts or for displaying values in tables.
The text data type is represented in ClickHouse by the Array(Nullable(String)) type.
Text type field
Example: The Help Desk form has two text fields: ${Subject} for a brief description of the request by the sender, and ${Message} for a detailed explanation of the problem the support team needs to resolve. We want to display a list of recent requests, including their subjects and message text.
A Text type field returns the entered text.
SELECT
task_id AS "#task('Task')",
${Subject} AS "Subject",
${Message} AS "Message"
FROM #tasks
WHERE is_closed = false
AND ${Subject} IS NOT NULL
ORDER BY create_date DESC
LIMIT 20;
Phone type field
Example: The Help Desk form contains a Phone type field with the code ${Phone}, where the sender's phone number is entered. We want to display a list of recent requests along with the phone numbers.
A Phone type field returns the entered number as text.
SELECT
task_id AS "#task('Task')",
${Phone} AS "Phone"
FROM #tasks
WHERE is_closed = false
AND ${Phone} IS NOT NULL
ORDER BY create_date DESC
LIMIT 20;
Email type field
Example: The Help Desk form contains an Email type field with the code ${Email}, which contains the sender's address. If necessary, multiple addresses separated by commas can be entered into this field (for example, if the email was sent from multiple addresses or forwarded). We want the report to display both the first address in the list and the full list of addresses.
An Email type field returns the entered email address as text.
SELECT
task_id AS "#task('Task')",
${Email} AS "First address",
${Email[*]} AS "All addresses"
FROM #tasks
WHERE is_closed = false
AND ${Email} IS NOT NULL
ORDER BY create_date DESC
LIMIT 20;
Catalog type
Example: let’s calculate the number of open tasks by the HR department so that they are grouped by categories depending on the type of signed documents (according to the values of the Catalog type field). We also want the report to display the document name — the text value of the selected field.
In the report based on the SQL query, fields of the Catalog type return text values of the selected row from the reference for each task. We will extract values from the third column of the reference with the code ${Catalog}. This column lists the types of personnel documents.
SELECT
${Catalog[3]} AS "Document type",
COUNT(task_id) AS "Documents to sign"
FROM #tasks
WHERE is_closed = false
GROUP BY ${Catalog[3]}
ORDER BY COUNT(task_id) DESC
LIMIT 100;
Tip: if the field in the task is not filled, the query may return null; if you do not want to consider tasks where this field is not filled, add to the query WHERE ${Field} IS NOT NULL.
Indices [N] and [*]: [N] refers to the value of the N-th column of the first selected item, while [*] returns an array of all selected items, where each item is an array of its column values (${Catalog[*]}).
Overview of display types
After you have ensured that the SQL query returns all necessary data in the correct format and quantity, you can configure its display by selecting a widget type.
Important: the choice of chart type determines which data formats (Number, Date and Time, Boolean) will be expected from your SQL query for the axes of the chart to work correctly.
Table (default format)
The table is the default format and the most versatile, as it displays all returned data without the aggregation characteristic of charts.
Data requirements
Any data types are allowed. Each named variable (column) in your query
(SELECT Col_A, Col_B)will create a separate column in the displayed table.
Configuring table display
- Renaming data: use aliases in
SELECT AS. - Grouping: allows simulating a hierarchical representation of data.
Pie chart
A pie chart visualizes the proportional distribution of a single numeric metric across several discrete categories.
Data requirements
Value column: must contain data of type Number, with all values greater than zero.
Name column: can be represented by any data type.
Display configuration
You must manually specify which column from the query results corresponds to Value (sector size) and which corresponds to Name.
Hovering over a sector displays its name and exact numeric value.
Pyrus Tip: if you want the widget to display only the pie chart, check the Hide Table checkbox.
Bar chart
Used for direct comparison of discrete data. Supports displaying multiple data series.
Data requirements
Value column(s) (Y Axis): data type - Number. Multiple datasets can be selected.
Name column (X Axis): can be represented by any data type.
Display configuration
You must manually specify which column from the query results corresponds to the Value (column height) and which corresponds to the Name.
When hovering the cursor, the column name and selected values are displayed.
Pyrus Tip: use the Hide table setting to hide the display of the data table.
Time series
The time series displays a sequence of values tied to the Date. This is a key tool for trend analysis.
Data requirements
Time column (X Axis). The expected data format is Date and time.
Values column (Y Axis). The expected data format is Number.
Display configuration
You must manually specify which column from the query results corresponds to time and which corresponds to value.
When hovering the cursor, the selected values on both axes are displayed.
Pyrus Tip: use the Hide table checkbox to hide the display of the data table.
Axis formatting types
How the report data will look on the chart depends on the type of data you selected in the query.
Numeric types
When displaying numeric data types, the following formatting options are available:
- default: uses the parameters set in the form field;
- percentages: for displaying shares;
- minutes / seconds: for convenient display of time intervals;
- money: displayed according to the currency specified in the Money field in the form.
Date and Time
This type of formatting determines which part of the timestamp will be shown on the chart or in the table.
- Date and time: full timestamp (DD.MM.YYYY HH:MM).
- Date: only calendar date (DD.MM.YYYY).
- Time: only time of day (HH:MM).
Formatting for other data types is not provided.