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.

SAP HANA Scripted Calculation View input parameters

This opens up the below pop-up window. Primarily, the data required is a name and label for this parameter and its datatype.

SAP HANA Scripted Calculation View input parameters



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.

SAP HANA Scripted Calculation View input parameters



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.

SAP HANA Scripted Calculation View input parameters

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.

SAP HANA Scripted Calculation View input parameters

Since the material variable was not mandatory, we leave it empty for now and fill in P_DOC_TYPE with the value ‘TA’.

SAP HANA Scripted Calculation View input parameters

As seen below, the distinct values tab confirms that only the value TA is present for filed DOC_TYPE.

SAP HANA Scripted Calculation View input parameters

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.

<<Previous Tutorial                                                                                                              Next Tutorial>>

Tagged .

10 Comments

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

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

  3. 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!

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *