Implementing SQL in Calculation Views

Expressions in calculation views are written in SQL. You write expressions in the following:

• Calculated columns

• Filters

• Restricted columns

In addition, SQL can be used to generate values for input parameters. For example, SQL can determine the top 5 customers of the current month and pass these to the input parameter of the calculation view to be used as a filter. This is a very powerful feature because input parameters are used throughout calculation views to provide dynamic values to filters, ranking thresholds, pre-filling user prompts, calculations, and so on.

One of the key benefits of using SQL is that it provides a long list of ready-made functions. These functions can provide significant additional options when you are trying to develop some logic where data must be manipulated. For example, SQL provides many string manipulation functions that are useful for re-formatting a field, or extracting some characters from it. In addition, there are many predefined functions available that can help you to calculate using dates. An example of this is finding the number of days between two dates.

Note

SQL expressions in calculation views uses plain SQL and not the syntax of the SAP enhanced version, SQLScript.

From QRC 1/2023 onwards, it is possible to include User-Defined Functions in the expression of Calculated Columns and Filters. This feature can help in scenarios where you want to reuse logic in different calculation views or pre-process values of input parameters before using them in expressions.

You can access both User-Defined Functions created inside your local container, and the ones from an external HDI container that are made available using a synonym.

As in the case for expression components from the other tabs, you can choose an object from the UDFs tab to add it to the Expression pane.