SAP HANA Scripted Calculation View input parameters
Welcome again to our next tutorial. You’ve already learn about dynamic filtering on the output of a calculation view using variables in our previous tutorial. This tutorial deals with another type of dynamic filtering – SAP HANA Scripted Calculation view input parameters. These dynamic filters, based on user input, allow filtering to be done inside the core logic of the HANA SQL Script.
Creating an SAP HANA Scripted view input parameter
We continue with the state of code we have from our previous tutorial.
Click on your script_view node to open up the code. To create a new input parameter, right click on the “Input Parameters” folder and click “New” from the context menu.
This opens up the below pop-up window. Primarily, the data required is a name and label for this parameter and its datatype.
The name and label has been filled up as P_DOC_TYPE since we will try to use this as a filter on P_DOC_TYPE in this view. The datatype of this input parameter has been also entered as NVARCHAR string of length 10 characters.
Please note that unlike variables, Input parameters are not linked to any fields while being created. Although the variable name is P_DOC_TYPE, it could be used in any place where you require an input parameter of the same datatype. Doing that would be not recommended though, since naming the variable something and using it somewhere else could be confusing for someone else who reads the code.
Press OK after filling the information as below.
This brings us back to the code. Earlier, we had hard coded the filter for document type field AUART to the value ‘TA’ but now we remove that and instead of it, place the input parameter. Notice that the colon symbol precedes the input parameter name whenever used in an SAP HANA SQL Script code. So the input parameter P_DOC_TYPE will be written as :P_DOC_TYPE in the code as shown below.
Now what the input parameter does in the below case is that it tells the HANA system that the AUART field has a filter on it which will be provided at runtime by the user. Notice that only input parameters can provide this dynamic filtering at the code level.
Due to this, data can be filtered before the join executes and precious memory is not wasted in retrieving data which is not required.
Save and activate the view.
Once done, execute the data preview. This would again bring up the pop up asking for values of the Material Variable we created earlier and also the new P_DOC_TYPE input parameter.
Since the material variable was not mandatory, we leave it empty for now and fill in P_DOC_TYPE with the value ‘TA’.
As seen below, the distinct values tab confirms that only the value TA is present for filed DOC_TYPE.
This concludes our tutorial on SAP HANA Scripted calculation view input parameters. Thank you for reading. I hope it was easy to comprehend.
Be sure to help this website grow by using the share buttons to spread this post on social media. This will also keep me motivated to write more.
HI Shyam, Nice blogs
Could u please write some blog YTD, QTD, MTD and ageing in real time that will be very nice looking forward for ur new blogs this is very very nice
Thanks Kumar. I am writing down all user requests and will try to write something as soon as I can.
In this case, there is no mapping required. It is a simple scenario and the pop-up comes up automatically.
hi ,
I have created a scripted calculation view on the sales table with the following select statement
========
SELECT “PRODUCT_ID”, SUM(“SALES_AMOUNT”) TOT_SALES_AMOUNT
FROM “_SYS_BIC”.”PTER_S11/SALESDATA” SD
where SD.”PRODUCT_ID” = :ABC
GROUP BY “PRODUCT_ID” ;
======================
where :ABC is the input para meter . This scripted calculation view is working as expected.
I want to achieve the same functionality using Graphical calculation view.
therefore I created the graphical calculation view.
these are the steps i did in graphical calculation view
1) drag a projection into the scenario area
2) out of the 3 columns of the sales table move product_id and sales_amt to the output area
3) link the projection to aggregation
4) created an input parameter (direct & data type is integer)
5) activated view
But when i do data preview . i am supposed to get the input para meter window but instead .. i am getting all the data .
not sure why i am not getting the parameter input window . please help.
please note that when i migrate the same scripted calculation view to Graphical calculation view using the migrate option then that views works …but when i try to manually create the Graphical calculation view via above steps . it is not working .
Re
Peter
Between step 4 and 5, you need to assign the input parameter to the filter. Check this link out –
http://teachmehana.com/sap-hana-input-parameters-graphical-view/
Cheers!
Thanks so much for this great site!
I am wondering if you have much experience working with oData services? I am trying to pass a filter (Contains) from a UI5 app to the Hana scripted calculation view. I can successfully pass an ‘EQ’ operator, and get the proper result set back, but when I try to send a ‘Contains’ to match a string pattern, I am getting an error from the backend. I assume this is because the view in Hana does not know what to do with the oData call of ‘filter=substringof(”,FIELD_NAME)’. The other issue I am seeing is there does not appear to be any operator other than ‘Equals’ in the input parameter options; do scripted calculation views support pattern matching?
Any pointers would be appreciated! Thanks again for all your hard working keeping this site maintained!
Hi Shyam,
Thanks for the nice guidance to HANA. I have one query. while creating input parameter, If i select multiple then how this can be handled in query. Is it with “IN” operator or any other way. Please assist.
Thanks
Anand
Yes. You can use IN.
Hi Shyam ,
If we have to perform join on 3 tables output of joi of 2 tables resultant should be join with third table so how we can consume it here if we don’t want to use nested Select statements ? Is there any option from which we can store output of 2 tables and then can use it in join with third table?
Regards,
Suraj Grewal
Hi ,
I have a doubt. Can you help
I have created a Calculated view (IP) to calculate my input parameters and I wanted use these input parameters for Main Calculated view “AB”
Can you tell me how can use IP CV to select input parameters for AB in Edit Input Parameter window so that My view AB directly fetches value from IP?
Also IP has only one row.
Do I need to push the data from Input Parameter CV to a table and then use Input Parameter Type =”Derived from table”
Hi Shyam,
is it more recommended to use the input parameter rather than variable parameter?
because in this tutorial u said that filtering in code level, won’t waste much memory
then when’s the situation i would use variable parameter rather than input parameter?
Thanks
Ricky
Hi Shyam ,
Thank you for the detailed tutorial.
I needed help with a scenario where i used input parameters in scripted view.
I have 3 input parameters & one of them is mandatory. The rest are filled with default values.
When i try to execute the sql on this, i get the following error ” [34023] Instantiation of calculation model failed;exception 306106: Undefined variable: $$Current_Period$$. Variable is marked as required but not set in the query” on the variable that is not marked as Mandatory. The view is not taking the default value provided in the input parameter.
The same works if i add the scripted view in another graphical view.