Filters:Type 3- SAP HANA Input Parameters
Input parameters are the magical underappreciated providers of flexibility in SAP HANA. They are somewhat similar to variables but are available at every node in the view. Input parameters are of great importance and have multiple applications but here we learn how to use it as a dynamic filter at the lowest node.
Input Parameters explained with an example
Let’s say we need to add a filter on field GRUPP from VBAK table. When the user selects a value during data preview, this filter should be applied to Projection_1 and thus only the reduced data set should undergo further calculations. This is the requirement. In this case variable can’t be used as we need to apply this dynamic filter at a lower node level and not at the top level.
First things first- Let’s add one more field GRUPP to the view by propagating it to semantics and then activating the view again.
Now, single click on the VBAK node if you already haven’t and then on the right side, you would see a folder for Input Parameters as shown below. Right click on it and select “New” to create a new one.
1
1
The below window appears which is similar to our variable creation screen. Give it a name and a description.
Notice that there is no attribute to bind this value to here. Input parameters can be created independent of fields unlike variables. Picture them as a value that can be used anywhere and this value being dynamic, can be entered at run-time by the user. Provide a datatype and length of this input parameter. Press OK.
Notice that an input parameter has been created and appears in the folder as shown below. Now, double click on the “Expression” under filters.
This opens up the expression editor. You can see here that the static filter that we applied earlier also appears here. We created it by simply right clicking the VBTYP field and assigning a filter value = ‘C’ there but due to that the system auto-creates the corresponding filter expression code in here. Notice that it is greyed out because there was no manual coding of filters yet. But with curious developers like us, we have to explore the options that lie ahead! Press the Edit button marked below.
1
HANA throws us a warning that from now on, filters for this projection can only be maintained as an expression code. This means that no more rights click + apply filter functionalities will work in this node. Every time you need a filter (even a static one) you would have to add a small bit of code here. We do this because Input Parameters can only be added as filter via the expression editor. Press OK to move ahead.
This opens up the expression editor for editing. Place an AND operator after the existing filter to tell HANA that you are adding a second condition here.
After the AND, add an opening brace of a bracket and double click on the GRUPP field as shown below.
This adds the GRUPP field into the expression editor. Now, we need to tell HANA that the filter is GRUPP = The input parameter. Place an equal sign and double click on the parameter name as shown below.
Close the bracket after the expression has been written as shown. Notice that the input parameter is always represented as covered by single quotes and double dollar signs on either side whenever used in a code.
Press OK, save and activate the view. Click on data preview to bring up the selection screen where along with the V_VKORG variable, you now have the P_GRUPP input parameter. In this case, I provide it the value 1200.
The data preview opens up and it looks like GRUPP has only the 1200 values.
Just to be sure, let’s check the “Distinct Values” tab. Dragging and dropping GRUPP here confirms that the only values it has are all ‘1200’.
Thus, we successfully used input parameters as filters.
There are also further usages of Input parameters which we would learn as we progress. One step at a time.
Major differences between Input parameters and variables in SAP HANA
The differences are:
- Variables apply filter after execution of all nodes till the semantics (at the top level) whereas Input parameters can apply filters at any projection level.
- Variables are bound to attributes/specific fields whereas an input parameter is independent of any field in the view.
- Variables have a sole purpose of filtering data whereas filtering is only one of the reasons to use an input parameter.
Thank you for reading this tutorial. Please help this website grow by sharing the document on social media using the share buttons below and subscribe to our newsletter for the latest updates.