An input parameter is similar to a variable in that it supports to passing of dynamic values to a calculation view at runtime. But whereas a variable has only one purpose, that is to provide a dynamic filter based on attributes, an input parameter can fulfill additional requirements.
Input parameters can be used in different places to provide dynamic values, in particular, calculated columns, restricted columns and filter expressions. Here are a few examples:
Calculated Column
You want to execute an ad-hoc calculation of the forecast sales for next year, based on the current year and an overall expected sales volume increase - for example, 11%, entered by the user at runtime.
Restricted Column
You want a calculation view to return the sales for a year chosen by the user at runtime - for example, 2019, and the previous year, using two restricted columns.
Filter
You want to filter the data set for sales orders that have a total amount greater than $10.000. The threshold value is entered at runtime.
You want to filter sales data for products that have an name containing the string 'GTR', the string is chosen at runtime by the user.
You want to control specifically where a filter expression based on a user entry is applied inside the calculation scenario. Variables do not let you precisely choose, in the calculation view, to which node the filter is applied. Variables apply filter always on top.
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.
Note
Filtering data by attributes could be achieved using variables or input parameters, It is more straightforward to use variables in this case, because you do not need to write the filter expression. In addition, some front-end clients may not support input parameters, but most will support variables.Input Parameter Types
The following types of Input Parameters are supported:
| Type | Use Cases |
|---|---|
| Direct: Currency | For currency conversion, when you want the end user to specify a source or target currency. |
| Direct: Date | To retrieve data based on a date entered by the end user (or chosen in a calendar type input box). |
| Direct: Unit of Measure | To retrieve data based on a unit of measure choosen by the end user |
| Static List | To provide the end user with a predefined list of values in which he/she chooses one or several items. |
| Column | To provide the end user with a list of values from a column of the calculation view |
| Derived From Table | When you want the end user to have a set list of values from a table (not necessarily included in the view) |
| Derived From Procedure | When you want the parameter value to be passed to the calculation view based on the scalar output of a stored procedure |
| Direct (without semantic type) | When none of the above applies and/or when you want the user to enter a parameter without choosing it from a predefined list. |
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 popup 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 it 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. This 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 multi-value 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 the execution of the calculation view, input parameters are passed inside the SQL query using a PLACEHOLDER clause - for example:
SELECT <columns> FROM <calculation_view_name> (placeholder."$$IP_YEAR$$"=>'2019') GROUP BY <group_by columns>;
Not all applications are able to pass values to the PLACEHOLDER. That is why sometimes you must use variables to achieve the same outcome.
Note
When you have defined an input parameter, you must then 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, which 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 because 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. However, that would mean that apart from the user not having any guidance, he/she 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
If we want the end user to decide whether Gross or Net amount should be shown in a view, the first step is to create an input parameter that will be used in a calculation.
The Input Parameter can be of any suitable type, for example a Static List type.
In this example, the user will be able to choose either "Gross Amount" or "Net Amount".
Default value GROSS will be assigned to the input parameter if the user does not specify anything.
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
The next step is to use the Input Parameter in a Calculated column.
This is done by calling it within single quotes and double dollar signs.
In this example, the input parameter is used in the condition of an IF expression: if('$$GROSS_OR_NET$$'='Gross',"GROSS_AMOUNT","NET_AMOUNT")
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
An Input Parameter type of type "Direct" with a semantic type "Date" can be useful when you want to create calculations based on a date specified by the reporting user.
You can create a date range by creating a pair of input parameters (for example, "Date From" and "Date To").
Note that the Data Type must be set to "DATE".
Using a Calendar Dialog for Date Input Parameters
When using the type "Date" you are making it easier for the end user to select a date by utilizing a calendar dialog for selecting the appropriate date.
In the figure, the user is asked for a single value. Dates can also be selected as ranges.
Note
SAP recommends using input parameters instead of variables whenever possible, because input parameters provide more flexibility and features than variables. However, some front-end tools cannot process input parameters and so variables must be used.