Input parameters provide a means to influence query execution with values that are entered at runtime by the end user. In contrast to variables, input parameters can be mapped between different views so that an input parameter from one view is filled with values of the mapped input parameter of another view. In addition, it can be controlled at what node the input parameters are processed which provides precise control about the semantics of the input parameter. This precise control can be particularly beneficial when dealing with hierarchies. See, for example, SAP Note 2453665 for a discussion of the differences between variables and input parameters in the context of hierarchies.

While variables are evaluated using the normal WHERE-filter Syntax, input parameters offer more options and have an own syntax. We will demonstrate this syntax of input parameters with different examples in this blog.

There are two locations where syntax of input parameters comes into play:

1. When calling a query. In SQL a place-holder syntax is available
2. When processing the input parameter in an expression. Here a decision needs to be done whether the input parameter should be evaluated or should be treated as a string.

This documentation list typical combinations of calling and processing syntax.

In a nutshell, when processing the input parameter the parameter can either be evaluated, or treated as a string. For example, a value like ‘1+1’ can be evaluated to 2 or treated as a string. In the first case 2=1+1 would be true while in the second case, when treated as a string, it would be false (or fail as an nonnumeric value).

If evaluation is expected the input parameter should not be enclosed in apostrophes when being processed. If evaluation should not happen and the input parameters should be treated as a string then the input parameter should be enclosed in single apostrophes.

We will illustrate the different use cases with several examples below.

The discussed examples are reduced as much as possible to focus on the input parameter handling. This might make them sometimes unrealistic. Nevertheless, we hope that in this reduced way it will highlight the underlying principles.

All modeling will be done in the new development environment SAP Web IDE for SAP HANA as modeling (and other actions) in SAP HANA Studio have been deprecated (see SAP Note 2465027 for more information on the deprecation and see blog for more information on the migration into the new development environment).

Even though the examples are done in SAP Web IDE for SAP HANA the same principles apply to models developed in SAP HANA Studio. As the syntax is sensitive to apostrophes and double apostrophes we will not use these typographical markers in the description. Instead we will indicate citations in bold. This means every ” or ‘ in the text is meant literally to be used in modeling.

Data used for examples

All examples will only use the data provided by table IPExample

Table IPExample can be created with the following statements:

create column table “IPExample” (“date” date, “product” NVARCHAR(20), “productRating” integer, “amount” integer);

insert into “IPExample” values (‘2017-12-01′,’oranges’,5,10);

insert into “IPExample” values (‘2018-01-01′,’oranges’,5,10);

insert into “IPExample” values (‘2018-02-01′,’oranges’,5,10);

insert into “IPExample” values (‘2018-03-01′,’oranges’,5,10);

insert into “IPExample” values (‘2018-04-01′,’oranges’,5,10);

insert into “IPExample” values (‘2018-05-01′,’oranges’,5,10);

insert into “IPExample” values (‘2017-12-01′,’apples’ ,1,10);

insert into “IPExample” values (‘2018-01-01′,’apples’ ,1,10);

insert into “IPExample” values (‘2018-02-01′,’apples’ ,1,10);

insert into “IPExample” values (‘2018-03-01′,’apples’ ,1,10);

insert into “IPExample” values (‘2018-04-01′,’apples’ ,1,10);

insert into “IPExample” values (‘2018-05-01′,’apples’ ,1,10);

Examples

Example 1 will show how input parameters can be used to filter for a certain numeric value

Example 2 will show how input parameters can be used to filter for a certain string value

Example 3 will show how input parameters can be used to filter for a certain substring

Example 4 will demonstrate how input parameters can be used to filter for multiple strings

Example 5 will demonstrate how an input parameter can be used to sum up values from the beginning of the current year up to a certain date, or up to the current date if no input is provided at runtime

Example 6 will illustrate how an input parameter can be filled by a procedure. In this example the procedure uses another input parameter to determine the output.

Example 7 will exemplify how a user can enter a value that is either used as a filter on a column or that indicates that all records should be displayed