Personal Insights
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. 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:
- When calling a query. In SQL a place-holder syntax is available
- 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
I have always used Input parameter with single apostrophes than used type casting when and required. Definitely new learning for me.
Thanks,
KD Jain
thanks for this useful blog,
can you give us some example on syntax for when the prompt select to be is empty , is not null, is null.
Thank you
Hi Jafari,
does this: https://answers.sap.com/questions/12728736/web-ide-caluculation-view.html#
answer your question? If not, can you describe with an example what you are trying to achieve?
Best,
Jan
Hi Jan,
thanks for reply,
My case is user in the prompt dialog, instead of using equal operator, uses some thing like empty , is not null, is null.
Then in a syntax like the following, what would be the syntax for (give me the result when my prompt (placeholder is empty) or (placeholder is null) or placeholder is not null)
In other word what would be the syntax for
SELECT * FROM SOMEHANAVIEW ('PLACEHOLDER' = ('$$P_STATIC_LIST_MULTIPLE$$', is Empty)
SELECT * FROM SOMEHANAVIEW ('PLACEHOLDER' = ('$$P_STATIC_LIST_MULTIPLE$$', is Not Null))
Best
Sahar
Hi Sahar,
Assume the front end tool offers a prompt and the end user leaves this prompt empty. In this case the front end tool needs to generate a certain value for the placeholder. In principle the front-end tool can generate any value as long as in the model the input parameter is processed in accordance with this value. For example, if the front end tool chose to send '*' and in the model the input parameter would be tested against '*' all would be fine.
However, this means the model and the front-end-tool behavior have to fit together. As you don't want to have different models for different front-end-tools it certainly makes sense to have a common behavior.
If you look at the documentation for BIMC (https://help.sap.com/viewer/88fe5f56472e40cca6ef3c3dcab4855b/2.0.04/en-US/46292367ad8744628a7ff07a81dac8be.html) it is stated that "In case the parameter is not mandatory and no default value is provided, the calc engine uses the empty string ('') as parameter (default) value"
Thus I would go for (placeholder."$$IP_1$$"=>'') which would translate into a filter like '$$IP_1$$'=''
Did this address your question?
Best,
Jan
I found this placeholder syntax extremely useful today. I had a slightly different problem - was trying to pass the CURRENT_DATE to a HANA view with Input Parameter. However the regular PLACEHOLDER syntax wasn't working.
SELECT *
FROM SOMEHANAVIEW ('PLACEHOLDER' = ('$$VINP_PROMPT$$', CURRENT_DATE))
The Below syntax worked like a charm:
SELECT *
FROM SOMEHANAVIEW
(placeholder."$$VINP_PROMPT$$"=>CURRENT_DATE)
Thanks!
Prashanth D'souza
Thanks Prashanth D'souza for sharing this information! The second syntax should be prefered that allows passing literals and variables
Hi Jan thank you for your info on that placeholder. Can you please help me out with the below query:-
SELECT *
FROM SOMEHANAVIEW
(placeholder."$$VINP_PROMPT$$"=>CURRENT_DATE)
Here => works like = operator that is true but I want to know how we can use > operator.
Instead of equall to can we do greater than function in the above mentioned format if then what should be the syntax.
My goal is to take only those values where date is greater than whatever date I pass in the parameter
Hello Ardeep,
The correct way to achieve results in the scenario you mentioned, would be to get a single input parameter value, and use a model filter with Column1>=$$VINP_PROMPT$$ in the filter expression.
The input parameter acts as a 'lower limit' in this case.
If you are asking whether multiple values(like an array of single values) can be passed, the summary answer is yes, but you need to build your model to work with that kind of input. For E.g., if the value passed is $$X$$ = '1,2,3' your model must be able to parse this and translate it to (COLUMN1 IN ('1','2','3')), but this is not required for most cases.
Hope this helps.
Cheers
Dheepak R