Filters:Type 2- SAP HANA Variables
Welcome to the second part of the graphical view filters tutorial. In this one, we try to understand how SAP HANA Variables can help us create dynamic filters to allow more flexibility in development.
If a dynamic filter needs to be applied after all results have been calculated (top-level filter) , SAP HANA variables are the answer. By dynamic filter, I mean that when executed, this view would ask the user for filter values. In this case, the executing user has full control of the filter being applied.
SAP HANA Variables in an example
To create a variable, click on the Semantics node and then select the Parameters/Variables Tab and then click on the green plus button to create a new SAP HANA variable.
The new window pops up as ween below. Here, you need to provide a name, label ( = description), the attribute name on which filter needs to be applied on.
In the Selection Type drop down, have different options like:
• Single Value – to filter and view data based on a single attribute value
• Interval – to filter and view a specific set of data
• Range – to filter and view data based on the conditions that involve operators such as “=”(equal to), “>” (greater than), and so on
You can specify whether single or multiple values are allowed for the variable using Multiple Entries checkbox and also, mark it as mandatory.
You can set a default value for the variable that should be considered if no value at run-time is provided in the form of a constant or as an expression.
Let’s say we require that the user should be able to provide multiple entries of value to VKORG field that comes from table VBAK. As seen below, if a field has not been selected from the table, it will not be available to act as an attribute for creating a new variable. This happens because SAP HANA variables are created on the top level of the node and at the top, only selected fields exist.
This is not a big problem. Let’s go and add VKORG to the flow. As you saw in the previous tutorials, we need to add this field in the lowest node and keep selecting it in every node as we go up the flow for it to reach the final node. To do this quickly, we use a shortcut. Right click on VKORG field in the lowest node and select “Propagate to Semantics” from the context menu. It adds it to all the layers above it in a single shot (I wish finishing this website was this easy … ).
You will get an information message stating the nodes where the field has been added. Press OK.
Save and activate this view. Now, come back to the Semantics node and click on the plus button to try creating the variable again as we did a few moments ago.
Fill up the values as I did below. I named the variable V_VKORG (The naming convention might vary in your project). I used the same description. Attribute is the field on which we need the filter applied. In this case, we choose VKORG as the attribute. In the selection type, we choose single. This means that the input would be in single value(s) and not in ranges or intervals.
I have also marked “Multiple entries” meaning that the user can enter multiple single values while executing the view. “Is Mandatory” being enabled means that the user has to enter a value to be able to run this view. Leaving the field blank won’t be an option.
Also, when the selection screen pops up asking the user to enter a value for VKORG, we want it to show the value 1000 by default which the user can choose to change. Such values can be set in the Default value field. Press Ok after filling the required values.
As seen below, a new variable has been created in the Semantics node now. Activate the view and then do a data preview.
Testing the SAP HANA Variable
The data preview this time brings up a window asking you to enter a value for the variable V_VKORG. Also there is a plus symbol where you can add more values to the filter (since we selected “multiple values” in the variable properties). Let’s try adding one more variable. Press the green plus button as marked below.
As seen below, a new row got added where I applied another SAP HANA variable value which is equal to 2000. This filter now means that all output values are now filtered to only provide rows which have VKORG = 1000 or 2000.
Press OK after entering these filters.
In the data preview, go to the “Distinct Values” tab. You can see below that the only values of VKORG in data now are 1000 and 2000. This means that our SAP HANA variable filter worked perfectly well.
Let’s repeat this data preview once and try to execute this calculation view without any variable values as shown below. This prompts an error message telling the user that the variable is mandatory and cannot be skipped. This confirms the mandatory setting that we made while creating this SAP HANA variable.
An important property that you might have observed from the execution of SAP HANA variables is that the filter is applied after the view finishes its calculations. For example, lets say a view pulls up 1 million records and you now apply a variable on it at the top, which causes it to return only 1 record on execution. The filter was only applied after the 1 million records were already processed and was filtered just before the output.
Thank you for reading this tutorial. Read the next tutorial to understand Input Parameters – an even more powerful dynamic input feature.
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.
<<Previous Tutorial Next Tutorial>>