Examples Using Input Parameters with Calculation Views

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. 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.
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 five 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.

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);

Example 1: filter with input parameter on numeric value

We will use input parameter IP_1 to filter for records that have a productRating larger than 2.

As we will use the input parameter to filter on a numeric value the documentation tells us that the calling syntax should be:

(placeholder.”$$IP_1$$”=>2)

i.e., without enclosing the value 2 in apostrophes. This makes intuitively sense as we treat IP_1 as integer and not as string.

In addition, the documentation tells us that the expression during processing should also not include IP_1 in apostrophes:

“productRating” > $$IP_1$$

Again, it makes sense as we are not treating the value 2 as a string (and might want to evaluate, e.g., 1+1):

Here are the steps to model this requirement:

a) Create a Calculation View

b) Add table IPExample as a datasource

c) Map all fields to the output

d) Create direct input parameter IP_1 of type INTEGER (see screenshot below)

e) In the Aggregation Node enter the following filter (a graphical expression editor is also available):

f) Save, build and do a data preview on the view

g) When prompted for a value enter 2

You will now see only records that have a productRating higher than 2:

If you look at the respective SQL you will see that the expected placeholder syntax is used in the data preview query:

SELECT TOP 1000

“date”,

“product”,

SUM(“productRating”) AS “productRating”,

SUM(“amount”) AS “amount”

FROM “INPUTPARAMETERS_HDI_DB_1”.”inputParameters.db::example1″

  (placeholder.”$$IP_1$$”=>2)

GROUP BY

“date”,

“product”;

Example 2: filter with input parameter on string value

We will use IP_1 to filter for product oranges. As we will filter for strings the documentation tells us to use apostrophes around the input parameter when calling the Calculation View:

placeholder.”$$IP_1$$”=>’oranges’

which intuitively makes sense as we are dealing with a string input parameter

Accordingly, the filter expression needs apostrophes as well because there is nothing to be evaluated and the value of the input parameter should be treated as a string:

“product”= ‘$$IP_1$$’

To create this example:

a) Change the input parameter from Example 1 to NVARCHAR(20) (see screenshot below)

b) Change the filter to

“product”= ‘$$IP_1$$’

c) Save, build and preview the Calculation View

d) When asked for a value for IP_1 enter oranges

In the results you will only see records with oranges in column product. The generated query looks like expected:

SELECT TOP 1000

“date”,

“product”,

SUM(“productRating”) AS “productRating”,

SUM(“amount”) AS “amount”

FROM “INPUTPARAMETERS_HDI_DB_1”.”inputParameters.db::example2″

    (placeholder.”$$IP_1$$”=>’oranges’)

GROUP BY “date”, “product”;

Example 3: Filter with input parameter for a certain substring

We will filter for the substring ng (e.g., oranges) in column product using SQL function LOCATE.

To create the example:

a)  Change in Example 2 the filter expression to:

LOCATE(“product”, ‘$$IP_1$$’)

b)  Save, build, and preview the Calculation View. When asked for a value enter ng

In result you will see all records for which the value in column product contains ng:

Based on the past experiences the generated SQL statement should not be surprising:

SELECT TOP 1000

“date”,

“product”,

SUM(“productRating”) AS “productRating”,

SUM(“amount”) AS “amount”

FROM “INPUTPARAMETERS_HDI_DB_1”.”inputParameters.db::example3″

    (placeholder.”$$IP_1$$”=>’ng’)

GROUP BY “date”, “product”;

Example 4: Filter with input parameters for multiple strings

In this example we will filter for apples or oranges. We see that the calling syntax should use escaping apostrophes ”. This means that ‘ does not end the entered string but is interpreted as a ‘ contained in the string:

‘PLACEHOLDER’ = (‘$$var$$’ = ‘ ”oranges”,”apples” ‘)

The processing syntax is this time not using apostrophes as the individual values should be interpreted as strings separated by commas, and thus should be processed as ‘oranges’, ‘apples’ which is a list of string values.

To identify records that match a string in this list of strings we will use the function in which is only available in Column Engine

To reproduce the example:

a) In the filter expression of the model of Example 3 select Column Engine

b) Enter:

in(“product”,$$IP_1$$)

c) Save and build

d) Run the SQL statement from Example 3 but replace the placeholder with the following:

(placeholder.”$$IP_1$$”=>’ ”oranges”,”apples” ‘)

Thus, the SQL statement should look like:

SELECT TOP 1000

“date”,

“product”,

SUM(“productRating”) AS “productRating”,

SUM(“amount”) AS “amount”

FROM “INPUTPARAMETERS_HDI_DB_1”.”inputParameters.db::example4″

          (placeholder.”$$IP_1$$”=>’ ”oranges”,”apples” ‘)

GROUP BY “date”, “product”;

How the input parameters should be filled depends on the intended meaning. To signal to the data preview and front-end tools that you are expecting multiple entries you can tick the respective option Multiple Entries of the input parameter:

In the data preview dialog you would then first enter two input lines and then fill in the values:

Alternatively, you can create the SQL statement manually.

As a result of running the SQL statement you will see all records with oranges, or apples, in column product:

Example 5: Use Input Parameters to calculate year-to-date with flexible input

The goal is to calculate the sum of amount for all records from beginning of the current year up to a certain date that can be entered at runtime. If no value is provided the current date minus one day will be used. As of this writing current date was 2018-08-03.

This goal will be achieved by using restricted columns. We will explain later, why we use restricted columns this time and not simply another filter.

We will use year(“date”) = year(current_date) AND “date” < to_date($$IP_1$$) as the restriction.

The first term selects all records of the current year and the second term all records with column date before the entered date. [As a side note: when using the restricted column on a large number of records you probably would  like to avoid applying year() at query execution time to the date values for filtering. For performance reasons you could store the year information separately in a column already when loading the table.]

In addition, we will set the default value of IP_1 to add_days(to_date(current_date),-1)

The default value will therefore take the current date and subtract one day from it. Given this default parameter definition IP_1 needs evaluation and should not be treated as a string.

To achieve this behavior:

a) Remove the filter from Example 4

b) Create restricted column RES_1 that uses expression:

year(“date”) = year(current_date) AND “date” < to_date($$IP_1$$)

c) Change the default value of IP_1 to:

add_days(to_date(current_date),-1)

d) Save, build, and preview the Calculation View. If you keep the default value for the input parameter and don’t enter an own value you will see the data of this year up till yesterday in column RES_1:

You only see a value in column RES_1 if the expression in the restricted column is fulfilled for this record. If the expression is not fulfilled NULL is listed in column RES_1.

You can also modify the value assigned to the input parameter:

SELECT TOP 1000

“date”,

“product”,

SUM(“productRating”) AS “productRating”,

SUM(“amount”) AS “amount”,

SUM(“RES_1”) AS “RES_1”

FROM “INPUTPARAMETERS_HDI_DB_1”.”inputParameters.db::example5″

(placeholder.”$$IP_1$$”=>’add_days(to_date(current_date),-95)’)

GROUP BY “date”, “product”;

This statement will select all days in 2018 before 2018-04-30 for restricted column RES_1 (assuming 2018-08-03 as the current date):

You can also enter a specific date. However, because in the expression IP_1 is not enclosed in apostrophes (the earlier examples required IP_1 to be evaluated) you need to enclose the value itself in apostrophes to ensure that it is treated as a string:

SELECT TOP 1000

“date”,

“product”,

SUM(“productRating”) AS “productRating”,

SUM(“amount”) AS “amount”,

SUM(“RES_1”) AS “RES_1”

FROM “INPUTPARAMETERS_HDI_DB_1”.”inputParameters.db::example5″

(placeholder.”$$IP_1$$”=>‘ ”2018-04-01” ‘)

GROUP BY “date”, “product”;

This will show all records before 2018-04-01 but still in year 2018 in restricted column RES_1.

Finally, you probably would like to sum up amount per unique entry in column product according to the filter in the restricted column. To do so you could use the following query:

SELECT

“product”,

SUM(“amount”) AS “amount”,

SUM(“RES_1”) AS “RES_1”

FROM “INPUTPARAMETERS_HDI_DB_1″.”inputParameters.db::example5″

(placeholder.”$$IP_1$$”=>’ ”2018-04-01” ‘)

GROUP BY  “product”;

Column RES_1 displays the summed-up values of records that fulfill the restricted column expression.

In this specific case you could have achieved the same with filters and without restricted columns. The reason why restricted columns are used in this example is that typically you want to have different evaluation periods next to each other in your output. This could be easily achieved with an additional restricted column that filters, e.g., for the values of the previous year up to a year before the input parameter value, e.g.,

year(“date”) = year(current_date)-1 AND “date” < add_years(to_date($$IP_1$$),-1))

If you added the following records to your table:

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

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

depending on your current date you should only see the second entry with date 2017-08-01 in your sum of your second restricted column LastYearToDatePreviousYear:

If you wanted to achieve this output you could enter the fixed value 2018-08-02 when executing the query.