Implementing SQL in Calculation Views

Objective

After completing this lesson, you will be able to implement SQL in a calculation view

SQL Expressions 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.

The Expression Editor

The Expression Editor is used to create the SQL Expression.

You can add any of the proposed components to your expressions:

  • Elements: Columns, Parameters, etc.

    You can also access value help to include specific column values in the expression.

  • Functions

    Functions are displayed in categories (Date, String,...) and you can filter on keywords.

  • Operators

Once you have finished writing the expression, you can check the syntax.

SQL Expression Editor. Select the Component, search or expand the list, click items to add them to the expression and check the syntax.

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.

The Expression Editor lists all the User Defined Functions (either local or from other containers accessed via synonyms). Select one to add it to your expression.

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.

Log in to track your progress & complete quizzes