Implementing Variables and Input Parameters

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Implement variables
  • Define input parameters
  • Map variables and input parameters

Variables

You define a variable in a calculation view when you want to pass dynamic values for filtering onattribute columns. They provide an excellent way of filtering data and there are many options that make implementing variables easy and flexible.

Upon execution of the Calculation View, variables are passed inside the SQL query using a WHERE clause, which is easily understood by most applications that call calculation views. For example:

Code snippet
SELECT <columns> FROM <calculation_view_name> WHERE (("COUNTRY" = 'JP')) GROUP BY <group_by columns>;
Copy code

Creating Variables

You define variables in the Semantics node of a Calculation View, in the Parameters tab.

The View/Table Value Help setting is used to define in which table/view SAP HANA will fetch the data to show in the prompt. The Reference Column defines which (unique) column in this table/view will provide the possible values for the variable.

By default, the View/Table Value Help setting refers to the current calculation view. This means that the values of the specified attribute column, for example CUSTOMER (customer ID), will be scanned in order to propose the various values in the prompt.

A different approach is to define a dedicated source table/view for the variable. For example, you could define the master data table CUSTOMERS as the Value Help source for the variable, and -of course- choose the column from this master data table that matches the column of your calculation view you want to filter on: the Customer ID in our example.

Once the Value Help table and one column are chosen, you assign the APPLY FILTER attribute. That is, you identify which attribute column will be filtered at runtime, based on the variable values chosen by the user.

Note
When the Value Help source table/view is the calculation view itself, the reference column you define is automatically assigned to the APPLY FILTER section as a filter attribute. But you can define another attribute if needed, provided that it is consistent with the reference column.

You will learn more about Value Help later on in this lesson.

The behavior of variables at runtime depends on whether an entry is required for the variable or not. These scenarios can be defined as follows:

  • If a variable is defined as mandatory, the user needs to provide the values, ranges, or intervals at runtime.

  • For non-mandatory variables, if nothing is specified at runtime, all the data for the corresponding attributes is returned by the view without filtering.

Creating Variables (2)

Creating Variables (3)

More than one value can be chosen for a variable when you select the Multiple Entries checkbox.

Note

In the data preview, From and To are displayed in the Variable Values dialog even when a variable has not been defined as range.

Value Help for Variables

When a dialog box appears to the user they must make a selection. However, rather than an empty field appearing and the user having to guess valid values or figure out the format for a value (for example, is the country code UK or GB?), we can have a dialog box populated with the run-time values available. To do this, you make a selection in the setting View/Table for value help. The default entry is the calculation view where the variable is being created. This means that you present all possible values from the column that is assigned to the Attribute setting in the variable definition.

While this might seem like a great idea, remember that the list may be huge and would be difficult for the user to navigate. Imagine presenting a list of every employee in a very large organization? If the list should be restricted to offer limited values (such as employees in your line of business or country) then you should reference an external calculation view or table that exposes a restricted list. It is also good practice, from a performance perspective, to refer to an external calculation view or table. This is because it means you are burdening the main calculation view with the task of providing value help for unfiltered columns.

Value Help Based on Hierarchies

When creating a variable on an attribute that is associated with one or several hierarchies, you can specify one of the hierarchies in the variable definition. With this option, the user can navigate the hierarchy, rather than a flat list, to select the values in the value help. This makes navigation much easier when there are a lot of values; imagine being able to first select your country, then your department, before the list of employees appears? You can use either parent-child or level hierarchies.

The following are some of the basic rules that apply when implementing value help based on hierarchies:

  • If you refer to a parent-child hierarchy, the variable attribute column must be defined as the parent attribute and not the child.

  • If you refer to a level hierarchy, the variable attribute column must be defined at the leaf level; that is, the bottom level.

Use Variables

Input Parameters

You can use another type of artifact to specify values to pass to the calculation view at runtime: an Input Parameter. Compared with a variable, which is only able to filter a data set, an input parameter can fulfill additional requirements.

Input Parameters can also be used when you have scalar or table functions in your Calculation View. The parameters of these functions can be fed with values that you enter at runtime, when querying your calculation view. This requires that you map the parameters, which will be discussed later on in this lesson.

Input Parameter Types

The figure, Input Parameter Types, shows the different types of Input Parameters that can be defined.

The Direct parameter type can be combined with a semantic type such as Date, Currency, or Unit of measure. This means that the value help will be based on these types of values. For example, if you specify Date, then a pop-up calendar will appear for the user prompt. If you specify Currency, then a list of valid currencies will be presented in the value help. This allows us to provide flexible input for the user but also allows us to control the type of values that are allowed.

Currency and Unit of Measure Semantic Types

For the Currency and Unit of measure semantic types, the list of proposed values will be created based on the corresponding reference tables in SAP HANA. This setup requires that the default schema assigned to the view contains the reference tables.

Input parameters support multiple values, which means that, at runtime, the end user has the possibility to provide several values to the parameter. Some examples of use cases include the following:

  • Applying filters of the types List of values and Not in List

  • Expression of calculated columns and expression of filters in projection nodes, provided that the expression requires a multivalue input.

When you define an input parameter of the type Derived from Procedure/Scalar Function, it is possible to map parameters to the input of the scalar function or procedure.

Note

Input parameters of the types Derived from table and Derived from Procedure/Scalar Function do not generate a prompt for the end user (they pass the parameter values directly), except if you select the Input Enabled option. In this case, the values returned by the table, procedure, or scalar function, can be modified by the end user.

Upon execution of the Calculation View, Input Parameters are passed inside the SQL query using a PLACEHOLDER clause. For example:

Code snippet
SELECT <columns> FROM <calculation_view_name> (placeholder."$$IP_YEAR$$"=>'2019') GROUP BY <group_by columns>;
Copy code

Not all applications are able to pass values to the PLACEHOLDER.

Note

Once you have defined an input parameter, you must figure out how to use it in an expression; otherwise it is ignored.

Creating Input Parameters

As discussed already, unlike a variable, an input parameter can be used in a conditional expression. For example, we can use an input parameter to determine which measure should be displayed in a particular column.

To illustrate this, we create a calculated column called AMOUNT that can be filled with either the gross amount or the net amount, depending on the value that the user chooses when querying the view.

In our example, we have chosen to use an input parameter of the type Static List. This means that we predefine the allowed value that can be chosen by the user in a list. This is fine for short lists, but when the list becomes large it becomes cumbersome to manage, as you would have to edit the calculation view and rebuild it each time. Of course you could choose the type Direct, which would mean the user could input anything. But that would mean, apart from the user not having any guidance, the user could also mistype the value, or enter the value in the wrong format (perhaps adding leading zeros when they were not required).

A good solution would be to define the input parameter with the type Column and then, in the View / Table Value Help, enter the name of a table or view where the allowed entries are presented. This also means that this list can be used by multiple input parameters and encourages central maintenance of the consistent, allowed values list.

Input Parameters

An input parameter used within a formula does not necessarily have to be of the type Static List. For example, it can also be a Direct numeric value used in multiplication or any other calculation type.

Calling an Input Parameter in a Calculation

In the example in the figure, Calling an Input Parameter in a Calculation, if the user selects GROSS, the calculated column (of type Measure) will display the GROSS_AMOUNT measure in the AMOUNT column. Any other selection will result in NET_AMOUNT being displayed.

Input Parameter Using Dates

Using a Calendar Dialog for Date Input Parameters

In the figure, Using a Calendar Dialog for Date Input Parameters, the user is asked for a single value. Dates can also be selected as ranges.

Use Input Parameters

Mapping of Variables and Input Parameters

Pushing Down Input Parameters and Variables to Lower Level Calculation Views

In many cases, calculation views use other calculation views as data sources. This is not necessarily confined to two levels; we can go on and layer the calculation views to create a stacked model. When you execute a calculation view in which variables or input parameters are defined, it is possible to pass their values (entered by the end user at runtime) to the lower level calculation views. In fact, the input parameters and variables at the lower levels are usually ignored unless you define input parameters and variables at the top level and map them to the input parameters and variables in the lower levels. This is called parameter mapping and is an important feature of SAP HANA calculation view modeling.

To enable parameter mapping, you must use the Input Parameter/Variables Mapping feature. You can find this feature in the Parameterstab in the calculation view.

When you open the mapping pane you must first select the type of mapping you want to work with, using the drop-down selector.

Note
From SAP HANA QRC 04/2021 onwards, it is possible to map input parameters from SQL views. This allows a better control of filters in scenarios where SQL views are consumed by your calculation views.

Parameter Mapping Types

There are four types of parameter mapping and you choose the type from the Manage Mapping pane.

Once you make your type selection, you will then see, on the left side, the input parameters and variables that are defined in the calculation views from all lower layers in the stack, which are related to the mapping type you selected. On the right side, you will see the input parameters and variables that are defined in the current calculation view (the one you are editing).

Note
You can only map Variables to Variables and Input Parameters to Input Parameters. Cross-Mapping (such as an Input Parameter to a Variable) is not possible.

You simply drag a line between the left and right side to map them. There is also an auto-map feature which means that if the names are the same, the mapping is done with a single click. The auto-map feature generates the input parameters or variables for the current view with the same name as the source variables and also maps them. This means that you don't have to manually create the input parameters or variables in the current view.

Pushing filters down to the source views using parameter mapping is a common scenario. To enable this, choose the type Data Sources from the drop–down list in the Manage Mapping dialog.

Hint
Mapping parameters of the current view to the parameters of the underlying data sources moves the filters down to the underlying data sources during runtime, which reduces the amount of data transferred across them. This is a great way to improve performance.

Another common scenario is when you want to push parameters down from the main calculation view to a calculated column in a lower view to support a calculation. Again, this would be the type Data Sources.

Note that In the type of mapping Data Source, you only map input parameters to input parameters. In other words, a Variable defined in an underlying Calculation View cannot be mapped to a Variable defined in the current Calculation View. However, it is possible to access these variables from the Extract Semantics feature and copy them to the current view. To do that, you right-click the data source in the calculation scenario and choose Extract Semantics. Then choose the Variables tab and select the ones you want to copy to the semantics of your Calculation View.

Mapping for External Value Help Views

Another important use case for mapping input parameters and variables is to enable dynamic value help views.

When you define input parameters and variables, the default data source that generates the value help list is taken from the calculation view itself. So, essentially you are getting an unrestricted list of all possible values to choose from. However, you can also redirect the value help to use a list from another table or view. The main reason we do this is to expose a restricted value help list.

This is also good practice for performance because the value help is not competing with the main calculation view for data. For example, you could create a calculation view on a table that contains all possible cities. Here, your calculation view would include a fixed filter expression that restricts the cities to a specific country. This means that the value help list presents only cities of a specific country to the user.

What if you wanted to change the country? You could go back to the calculation view and change the fixed filter expression, but this would be inefficient.

Cascading Prompts Architecture

What we should do is replace the fixed value in the filter expression with a variable based on country. Then, we should map this variable to a variable we define in the main calculation view for the country. This means that when a user is prompted for a country in the main view, the value chosen is passed through the mapping to the value help calculation view, so that the cities are filtered by the country that was chosen. The list of cities is then presented as the value help for the cities column. This is also known as cascading prompts. Cascading is not restricted to two levels; you can also cascade prompts across multiple levels. For example you could prompt for Continent, which then restricts the list of Countries, which in turn restricts the list of Cities, and so on.

To implement value help parameter mapping, you must select the option Views for value help for variables/input parameters from the drop–down list in the Manage Mapping dialog.

Note
An external view based on a hierarchy could also be considered as a value help cascading solution, and might be more visually appealing to the user.

Save progress to your learning plan by logging in or creating an account