Pass Input Parameters to CV from Parameters derived from Procedure/Scalar Function

I am writing this blog post on SAP HANA Input Parameters. There are few blogs on HANA IPs but they are not giving clear understanding. Here I am giving a basic example which will make the understanding easy for HANA developers.

Those who are working on HANA for quite sometime and developed SAP HANA CVs they must have worked on Input Parameters and Variables.

A Variable:

Variables are bound to columns and are used for filtering using WHERE clauses. As such, they can only contain the values available in the Columns they relate to.

Important features of variables:

◉ You use variables to filter data at runtime. You assign values to these variables by entering the value manually, or by selecting it from the drop-down list.

◉ You can also define whether the Variable is Mandatory or if it should have a Default Value.

◉ You can apply variables only in analytic and calculation views.

Variable Types:

The following types of Variables are supported:

◉ Single Value: Use this to apply a filter to a Single Value.

◉ Interval: Use this where you want the user to specify a set start and end to a selected Interval.

◉ Range: Use this when you want the end user to be able to use operators such as “Greater Than” or “Less Than”.

System Tables to see Variable Information:

There are 4 system tables under the schema _SYS_BI which contains information about Variables.

◉ BIMC_VARIABLE

◉ BIMC_VARIABLE_ASSIGNMENT

◉ BIMC_VARIABLE_VIEW

◉ BIMC_VARIABLE_VALUE

Sometimes you might not want a variable to just restrict the data of a view. But you also want to take input from the user and process it, returning dynamic data based on the user selection.

Input Parameters makes this possible.

An Input Parameter is a: Place Holder to filter the data.

◉ Input Parameters can contain any value the reporting user has to enter to parameterize the result. Therefore, a data type must be specified for each Input Parameter.

◉ Input Parameters are passed by Placeholders and used in Formulas.

How to create an Input Parameter. Please see below example.You can check other blogs on how to create an input parameters.

We can create an input parameter in projection.Aggregation or semantic node as well.

Here in this blog post I am going to specifically talk about Input Parameters derived from Procedure/Scalar function. There are many blogs for other parameter type But here in this blog I will be taking an example of a store procedure with an IN Parameter and the OUT parameter value of the stored procedure we will pass to the SAP HANA Calculation View.

First let us create a stored procedure as below.

Now lets us call the stored procedure and see what is doing?

Now Let us create a CV with Input Parameter.

doing a data data preview of the CV everyone knows but execute the SELECT operation on that CV and see how the placeholder is getting passed to the CV.

Similarly if there are any complex calculations are needed then in that case you can handle that in Stored Procedure and in return take the out value of the stored procedure, pass it to HANA CV and filter the data to show as per business need.