Introduction
Starting with version 7.1.0.375 RC2, the open-source BI platform Helical Insight has introduced a new option called Usage of RAW Function in Reports.
Helical Insight provides a predefined list of UI-driven functions. However, some functions supported by databases may not be available in this list. In certain cases, users may also want to create or use custom functions that are not available as prebuilt functions in Helical Insight.
Explanation
The RAW function solves this limitation by allowing users to write and execute native SQL functions directly in their reports.
For example, if PostgreSQL, MySQL, or Oracle supports a function that is not available in Helical Insight’s predefined Functions list, users can still use it through RAW.
General Syntax:
RAW(DATABASE_FUNCTION(column_name))
Key Benefits
- Use database functions that are not available in Helical Insight.
- Use any other column, even if it is not part of the report definition (i.e., the field is not present in Rows or Columns). Fully qualified column references can be used.
- Perform advanced calculations using SQL.
- Use multiple columns in a single expression.
- Access database-specific functions and features.
Use Case 1: Using Functions Not Available in Helical Insight
Consider a scenario where the required database function is not available in Helical Insight’s predefined function list.
Users can still use that function through RAW.
Follow these steps:
- Drag and drop the required field into the Rows or Columns section.
- Right-click the field and select Function.
- Select RAW.
- Enter the required database function.



Example:
In the following example, a PostgreSQL expression is written inside the RAW function:
RAW(REGEXP_REPLACE(TRIM(REGEXP_REPLACE("location", '[0-9]', '', 'g')),'\s+','','g'))

This expression removes all numeric characters and spaces from the location column.
Example Output:
| Original Value | Result After Applying RAW |
|---|---|
| New York123 | NewYork |
| Los Angeles45 | LosAngeles |
| San Francisco12 | SanFrancisco |
Click Save to view the result.
This allows users to use additional database functions without depending on Helical Insight’s predefined function list.
Use Case 2: Performing Advanced Calculations
RAW can also be used for calculations involving aggregate and window functions. Generally, we might have used a custom column to perform these calculations, but now it is possible to achieve this using RAW as well.
For example, consider calculating the percentage contribution of each travel cost category against the total travel cost.
RAW Expression:
RAW(
SUM(travel_details.travel_cost) * 100 /
NULLIF(SUM(SUM(travel_details.travel_cost)) OVER (), 0)
)

How It Works:
- SUM(travel_details.travel_cost): Calculates the travel cost for each category.
- SUM(SUM(travel_details.travel_cost)) OVER (): Calculates the overall travel cost.
- NULLIF(…, 0): Prevents division by zero.
- Multiplication by 100: Converts the result into a percentage.
Formula:
Percentage = (Category Travel Cost / Total Travel Cost) × 100
Enter the expression using the RAW function and click Save to view the calculated result.
Use Case 3: Using Columns from Different Tables
RAW also allows users to use multiple columns from different tables in a single database function.
For example, consider combining these fields:
- booking_platform from travel_details
- client_name from meeting_details
- employee_name from employee_details
RAW Expression:
RAW(
CONCAT(
"travel_details"."booking_platform",
"meeting_details"."client_name",
"employee_details"."employee_name"
)
)
Here, the PostgreSQL CONCAT() function combines the three column values into a single string.

Example Output:

If required, separators can also be added between the column values to make the output easier to read.
RAW Expression with Separators:
RAW(
CONCAT(
"travel_details"."booking_platform", ' - ',
"meeting_details"."client_name", ' - ',
"employee_details"."employee_name"
)
)
Note: The referenced tables must already be available in the underlying query through appropriate joins. RAW does not automatically create joins. Further, whether to use double quotes (“) or backticks (`) depends on the reporting database being used, i.e., the SQL dialect.
Native Query Indicator and Warning
After applying RAW, Helical Insight displays an icon on the field to indicate that a native SQL expression is being used.

Further, when users navigate to Report Metadata from the Settings tab, the following warning is displayed:
“This report uses Native Queries. Before changing the report Metadata, ensure the Native Queries are updated accordingly to avoid breaking the report.”

This is important because if you are using native SQL queries and switch from one database to another, the report may break if the new database does not support the native SQL syntax used in the existing expressions.
Important Points to Remember
- Use functions supported by the underlying database.
- Follow the SQL syntax required by your database.
- Ensure the referenced columns and tables are available in the report query.
- Define the required joins before using columns from multiple tables.
- Review RAW expressions whenever report metadata changes.
- Incorrect SQL expressions may result in query execution errors.
Need Help with Helical Insight?
For more information or technical assistance, reach out to us at support@helicalinsight.com.
You can also ask questions and connect with the Helical Insight community through our forum: https://forum.helicalinsight.com/.
