Minimum Version Required: 22.01
Pre-Conditions
User must have the FusionEdit permission to be able to edit a Fusion report and the expressions inside of it.
It is recommended to have a good understanding of SQL and basic technical skills before using Fusion Expressions.
Steps
Disclaimer: Fusion expressions are advanced functionality that involves writing SQL and a more technical skill set.
Encompass Support may not be able to help build or troubleshoot your custom SQL in Fusion expressions.
When building a Fusion report, Encompass offers several places that you can use an 'Expression' instead of the standard drop-down of fields to select from.
These expressions are custom SQL statements that are placed into the final report query, in various places based on where you add the expression in Fusion Edit.
Where can expressions be used?
Answer: Anywhere in Fusion Edit you see thefxsymbol, clicking on this symbol toggles between a standard fusion field and an expression.
Building Expressions
After clicking the fx symbol to toggle a field to an expression, click on the icon on the right side to open the expression builder:
It is recommended to always test the expression and ensure the SQL written is valid and will work on the report.
If a report is run with invalid SQL, the entire report will fail.
Aggregate Functions
Aggregate functions are a special type of function that operate over multiple rows in the underlying data.
A simple example is SUM(), which will add together all of the values into a single number.
These functions cannot be present in the 'Parent/Child/Detail' section of the report in expressions.
They can be present in the 'Values' section of the report, but if they are added then the default aggregate function on the right side must be removed.
Further Information
More can about available functions, how they are used, and general SQL information can be be found on the Snowflake Documentation.
Fields And Definitions
Fusion Expression: Section of the fusion edit engine that can be modified to contain a custom SQL statement
Snowflake: The underlying data warehouse that Encompass utilizes for Fusion reports. The SQL written in expressions must be in Snowflake compatible format
Best Practices
Always test your expression and ensure the SQL written is valid and will work on the report. If a report is run with invalid SQL, the entire report will fail.
Utilize the 'Run Test' tab at the top of Fusion Edit to test all expressions on the report at once.
Remember to remove the aggregate option if you are adding an expression in the 'Values' section of the report, and your expression already includes a SQL aggregate function inside of it