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:

  1. Drag and drop the required field into the Rows or Columns section.
  2. Usage of RAW Function in Helical Insight

  3. Right-click the field and select Function.
  4. Usage of RAW Function in Helical Insight

  5. Select RAW.
  6. Usage of RAW Function in Helical Insight

  7. 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'))

Usage of RAW Function in Helical Insight

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)
)

Usage of RAW Function in Helical Insight

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.

Usage of RAW Function in Helical Insight

Example Output:

Usage of RAW Function in Helical Insight

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.

Usage of RAW Function in Helical Insight

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.”

Usage of RAW Function in Helical Insight

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/.

Leave a Reply

Helical Insight’s self-service capabilities is one to reckon with. It allows you to simply drag and drop columns, add filters, apply aggregate functions if required, and create reports and dashboards on the fly. For advanced users, the self-service component has ability to add javascript, HTML, HTML5, CSS, CSS3 and AJAX. These customizations allow you to create dynamic reports and dashboards. You can also add new charts inside the self-service component, add new kind of aggregate functions and customize it using our APIs.
Helical Insight’s self-service capabilities is one to reckon with. It allows you to simply drag and drop columns, add filters, apply aggregate functions if required, and create reports and dashboards on the fly. For advanced users, the self-service component has ability to add javascript, HTML, HTML5, CSS, CSS3 and AJAX. These customizations allow you to create dynamic reports and dashboards. You can also add new charts inside the self-service component, add new kind of aggregate functions and customize it using our APIs.
Helical Insight, via simple browser based interface of Canned Reporting module, also allows to create pixel perfect printer friendly document kind of reports also like Invoice, P&L Statement, Balance sheet etc.
Helical Insight, via simple browser based interface of Canned Reporting module, also allows to create pixel perfect printer friendly document kind of reports also like Invoice, P&L Statement, Balance sheet etc.
If you have a product, built on any platform like Dot Net or Java or PHP or Ruby, you can easily embed Helical Insight within it using iFrames or webservices, for quick value add through instant visualization of data.
If you have a product, built on any platform like Dot Net or Java or PHP or Ruby, you can easily embed Helical Insight within it using iFrames or webservices, for quick value add through instant visualization of data.
Being a 100% browser-based BI tool, you can connect with your database and analyse across any location and device. There is no need to download or install heavy memory-consuming developer tools – All you need is a Browser application! We are battle-tested on most of the commonly used browsers.
Being a 100% browser-based BI tool, you can connect with your database and analyse across any location and device. There is no need to download or install heavy memory-consuming developer tools – All you need is a Browser application! We are battle-tested on most of the commonly used browsers.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
We have organization level security where the Superadmin can create, delete and modify roles. Dashboards and reports can be added to that organization. This ensures multitenancy.
A first-of-its-kind Open-Source BI framework, Helical Insight is completely API-driven. This allows you to add functionalities, including but not limited to adding a new exporting type, new datasource type, core functionality expansion, new charting in adhoc etc., at any place whenever you wish, using your own in-house developers.
A first-of-its-kind Open-Source BI framework, Helical Insight is completely API-driven. This allows you to add functionalities, including but not limited to adding a new exporting type, new datasource type, core functionality expansion, new charting in adhoc etc., at any place whenever you wish, using your own in-house developers.
It handles huge volumes of data effectively. Caching, Pagination, Load-Balancing and In-Memory not only provides you with amazing experience, but also and does not burden the database server more than required. Further effective use of computing power gives best performance and complex calculations even on the big data even with smaller machines for your personal use. Filtering, Sorting, Cube Analysis, Inter Panel Communication on the dashboards all at lightning speed. Thereby, making best open-source Business Intelligence solution in the market.
It handles huge volumes of data effectively. Caching, Pagination, Load-Balancing and In-Memory not only provides you with amazing experience, but also and does not burden the database server more than required. Further effective use of computing power gives best performance and complex calculations even on the big data even with smaller machines for your personal use. Filtering, Sorting, Cube Analysis, Inter Panel Communication on the dashboards all at lightning speed. Thereby, making best open-source Business Intelligence solution in the market.
With advance NLP algorithm, business users simply ask questions like, “show me sales of last quarter”, “average monthly sales of my products”. Let the application give the power to users without knowledge of query language or underlying data architecture
With advance NLP algorithm, business users simply ask questions like, “show me sales of last quarter”, “average monthly sales of my products”. Let the application give the power to users without knowledge of query language or underlying data architecture
Our application is compatible with almost all databases, be it RDBMS, or columnar database, or even flat files like spreadsheets or csv files. You can even connect to your own custom database via JDBC connection. Further, our database connection can be switched dynamically based on logged in users or its organization or other parameters. So, all your clients can use the same reports and dashboards without worrying about any data security breech.
Our application is compatible with almost all databases, be it RDBMS, or columnar database, or even flat files like spreadsheets or csv files. You can even connect to your own custom database via JDBC connection. Further, our database connection can be switched dynamically based on logged in users or its organization or other parameters. So, all your clients can use the same reports and dashboards without worrying about any data security breech.
Our application can be installed on an in-house server where you have full control of your data and its security. Or on cloud where it is accessible to larger audience without overheads and maintenance of the servers. One solution that works for all.
Our application can be installed on an in-house server where you have full control of your data and its security. Or on cloud where it is accessible to larger audience without overheads and maintenance of the servers. One solution that works for all.
Different companies have different business processes that the existing BI tools do not encompass. Helical Insight permits you to design your own workflows and specify what functional module of BI gets triggered
Different companies have different business processes that the existing BI tools do not encompass. Helical Insight permits you to design your own workflows and specify what functional module of BI gets triggered