SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SAP HANA Modeling
+
-
Spatial Processing
+
-
Predictive Analysis

Variables and Input Parameters

In the article SAP HANA Modeling Introduction and SAP HANA Calculation View we explained the basics of SAP HANA data modeling. We also learnt how to create modeling views in Build Your First SAP HANA Model in 10 Minutes
In this article we will learn what are Variables and Input Parameters in HANA and how do they work.

Variables and Input Parameters in HANA:

Sometimes we need to filter the data based on user's input. This is where Input Paramters and Variables come into the pictures.
Input parameters and variables are used to filter the result of modeling views.

SAP HANA Modeling

Let us first see what is Variable and how does it work.


Introduction to variables:

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.

Sounds confusing?? Let us take a simple example.
In the article Create Graphical Calculation View in 10 minutes, we created a calculation view which has 4 columns PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME and SALES_AMOUNT.
REGION_NAME contains different regions like “America”, “Asia” and “Europe”. The calculation view gives all the region’s details in output.

SAP HANA Modeling

Now suppose we want to filter the data on region basis. The calculation view will prompt the user to specify Region Name and the output data will be filtered based on that.
For example if the user provides the value as “Asia”, the calculation view will show data only for “Asia” region.

SAP HANA Modeling

How to create and use variables in modeling view:

Now we will see how to implement this using variable.

Note: We will refer to the calculation created in the article Create Graphical Calculation View in 10 minutes.
    1. Open the calculation view. On Right-Bottom side you will see a pane called “Variables/Input Parameters”. Click on the down arrow of “+” icon and select “Variable”

      SAP HANA Modeling

    2. Specify Name of the Variable. Click on the drop down arrow besides “Attribute” and select REGION_NAME. Click on ok.

      SAP HANA Modeling

    3. Activate the calculation view. Now click on the data preview.
    4. A pop-up window will appear. Click on Open Value Help besides “From” and select “Asia” region. Click on ok.

      SAP HANA Modeling

    5. Go to Raw Data tab to see complete output. It contains data only for “Asia” region.

      SAP HANA Modeling

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
Introduction to Input Parameters:

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.

How to create and use input parameters in modeling view:

Example Scenario:

Let us consider the same calculation view we created before which has following attributes and measures.
Attributes: PRODUCT_NAME, REGION_ID, REGION_NAME, SUB_REGION_NAME
Measures: SALES_AMOUNT

Suppose we want the end user to decide whether SALES_AMOUNT or NET_AMOUNT should be shown in the output.
We can create a input parameter of type “Static List” which will have 2 values “SalesAmount” and “NetAmount”.
The calculation view will prompt for the input parameter. The user will choose either “SalesAmount” or “NetAmount”. Based on this selection, the output will be shown.

Let us create the input parameter.
    1. Open the same calculation view. On Right-Bottom side you will see a pane called “Variables/Input Parameters”. Click on the down arrow of “+” icon and select “Input Parameter”.

      SAP HANA Modeling

    2. Provide Name, description and select the data type as INTEGER.

      SAP HANA Modeling

    3. Select the Aggregation block and right click on “Calculated Columns” to create a new column.

      SAP HANA Modeling

    4. Specify the name as “NET_AMOUNT”, Data Type as double. In the expression editor provide the expression as
                  "SALES_AMOUNT" - ( "SALES_AMOUNT" * $$DISCOUNT$$ /100)

      Note that we are using Input parameter DISCOUNT by using syntax $$DISCOUNT$$

      SAP HANA Modeling

    5. Activate the calculation view.
    6. Click on data preview. A pop-up window will be opened. Provide the Region Name as ‘Asia’ and Discount value as 10.

      SAP HANA Modeling

    7. The NET_AMOUNT will be calculated as per the given DISCOUNT value.

      SAP HANA Modeling

Important Features of Input Parameters:

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

Input Parameter Types:

The following types of Input variables are supported.

Currency:
Use this during currency conversion where the end user should specify a source or target currency.

Date:
Use this to retrieve a date from the end user using a calendar type input box.

Static List:
Use this when the end user should have a set list of values to choose from.

Attribute Value:
When an Input Variable has this type, it serves the same purpose as a normal Variable.

None:
If none of the above applies you do not have to specify an Input Variable type. The Type can be left blank.

How to Pass Input Parameters and Variables in SQL Query:

Open the data preview of calculation view as mentioned in previous step.
Click on the “Show Log” and then double click on the message highlighted below. A new window will be opened which contains the SQL query.

SAP HANA Modeling

The generated query is:
SELECT TOP 200 "PRODUCT_NAME", "REGION_NAME", "SUB_REGION_NAME", "NET_AMOUNT", sum("SALES_AMOUNT") AS "SALES_AMOUNT"
     FROM "_SYS_BIC"."sap-hana-tutorial/SalesReport"
      ('PLACEHOLDER' = ('$$DISCOUNT$$', '10'))
      WHERE ("REGION_NAME" IN ('Asia') )
      GROUP BY "PRODUCT_NAME", "REGION_NAME", "SUB_REGION_NAME", "NET_AMOUNT"

Note that in your case package name might be different and query might be slightly different.

Input Parameter is passed using PLACEHOLDER:

The value of Input Parameter is passed using PLACEHOLDER clause.
    ('PLACEHOLDER' = ('$$DISCOUNT$$', '10'))

WHERE:
The value of Variable is passed using WHERE clause.
     WHERE ("REGION_NAME" IN ('Asia') )

Support us by sharing this article.



Explore More
Close X
Close X

19 thoughts on “SAP HANA Variables and Input Parameters

  1. Vijaya Balaji says:

    Hi,

    I tried to pass input parameters via SQL, however getting below error:

    SAP DBTech JDBC: [2048]: column store error: search table error: [6968] Evaluator: syntax error in expression string;expected TK_RPAREN,parsing ‘double(“SALES_AMOUNT” – ( “SALES_AMOUNT” * [here]/ 100 ))’

    The same view works our fine with graphical calculation view.

    Could you please help me on finding what went wrong with my querry.

    SELECT “PRODUCT_NAME”, “REGION_NAME”, “SUB_REGION_NAME”, “CC_NETAMOUNT”,
    sum(“SALES_AMOUNT”) AS “SALES_AMOUNT”
    FROM “_SYS_BIC”.”A141529_PACKAGE/SALESREPORT”
    (‘PLACEHOLDER’ = (‘$$IP_DISCOUNTS$$’, ’10’))
    WHERE (“REGION_NAME” IN (‘ASIA’))
    GROUP BY “PRODUCT_NAME”, “REGION_NAME”, “SUB_REGION_NAME”, “CC_NETAMOUNT”;

    • Admin says:

      Hi Vijaya,
      Please note that there are 2 type of syntax for Placeholder.

      SELECT “NAME”, sum(“NetValue”) AS “NetValue” FROM “_SYS_BIC”.”calcview1″ (‘PLACEHOLDER’ = (‘$$P_Discount$$’, ’10’)) GROUP BY “NAME”

      OR

      SELECT “NAME”, sum(“NetValue”) AS “NetValue” FROM “_SYS_BIC”.”calcview1″ (placeholder.”$$P_Discount$$” => ‘10’) GROUP BY “NAME”

      Here “P_Discount” is input parameter name. Pass the SQL parameter using second syntax.

      For example:
      SELECT “NAME”, sum(“NetValue”) AS “NetValue” FROM “_SYS_BIC”.”calcview1″ (placeholder.”$$P_Discount$$” => ?) GROUP BY “NAME”

      Regards,
      Admin

  2. Rahul says:

    Hi vijaya,
    Can you please tell me how to pass a variable in placeholder?
    suppose if the variable is coming from the procedural view and we need to use this variable inside a placeholder.
    for ex- date1 is a variable
    can we use… placeholder.”$$P_Discount$$” => :date1)? (this is not working)
    It will great if you can tell this.

    Regards,
    Rahul

    • Admin says:

      Hi Rahul,
      Please note that there are 2 type of syntax for Placeholder.
      Assume there is a calc view having an input parameter “P_Discount” and has 2 columns NAME and NetValue.

      SELECT “NAME”, sum(“NetValue”) AS “NetValue” FROM “_SYS_BIC”.”CalcView” (‘PLACEHOLDER’ = (‘$$P_Discount$$’, ’10’)) GROUP BY “NAME”

      OR

      SELECT “NAME”, sum(“NetValue”) AS “NetValue” FROM “_SYS_BIC”.”xsjs-examples.models/InputParamExample” (placeholder.”$$P_Discount$$” => ‘10’) GROUP BY “NAME”

      The first one does not support a SQL placeholder (?). But in the second syntax, an SQL placeholder (?) is possible.
      SELECT “NAME”, sum(“NetValue”) AS “NetValue” FROM “_SYS_BIC”.”xsjs-examples.models/InputParamExample” (placeholder.”$$P_Discount$$” => ?) GROUP BY “NAME”

      In your case, (placeholder.”$$P_Discount$$” => :var1) will definitely work. Please share the complete code if it is not working.

      Regards,
      Admin

  3. Kotesh says:

    Nice article.

  4. Savitha Sree says:

    Hi, In the above article it is mentioned that “You can also define whether the Variable is Mandatory or if it should have a Default Value”
    May I know how to implement the default value for a variable, during data preview the screen should pop up the default value to user automaticaaly instead of user selecting the variable. for example if the user wants specific region data then the role is designed for that particular region and the variable should pop up automatically in the variable screen during data preview, can u plz suggest

  5. Atul Kumar Bajpai says:

    Hi,

    I have some issue in Input Parameter,
    Brief description: There is a table with FiscalYear on this table we have created Calculation View A with Input Parameter (FiscYear) and tested it is working fine, Now on top of this CV A we have created another calculation B view using the previous view two time in projection. The reason is we want to filter the data on 2014 and 2015 year on top of projection we have created union, when we are passing two different values in two input parameters the data is coming only for one.
    If constant is assigned to the Input parameter then the data is coming properly, Can anybody help

    • Admin says:

      Hello,

      Please, send the screenshot of your actions and issues with details at “admin@saphanatutorial.com”.

      Thanks,
      Admin

  6. Payal says:

    Hi,
    I have a table with columns production plant, order type, requirement year and extended cost.
    I have also created 3 input parameters for production plant, order type and year.
    When i pass value to one input parameter at a time, i am getting the correct output.
    But it throws an error when i pass values two input parameters at the same time.

    Eg: I am passing the value Production plant = 2730 and order type = “ZPO”, and i get the below error.
    Error: SAP DBTech JDBC: [2048]: column store error: search table error: [6968] Evaluator: syntax error in expression string;expected TK_ID,parsing “( \”Production_Plant\”=’2730′ ) AND in(\”Order_Type\”,[here])”
    Possible Cause(s): Syntax error due to data type mismatch for the input parameter value used in the calculation of a calculated column.
    Proposed Solution(s): Provide a value with the matching data type as that of the input parameter either as default value or at runtime during data preview.

    Generated SQL:
    SELECT TOP 200 DISTINCT “Production_Plant”, “Order_Type”, SUM(“Extended_Cost_1”) AS “Extended_Cost_1_SUM”
    FROM “_SYS_BIC”.”PLANT/PLANT_CALC”(‘PLACEHOLDER’ = (‘$$ProductionPlant$$’, ‘2730’))
    GROUP BY “Production_Plant”, “Order_Type”
    ORDER BY “Production_Plant” ASC, “Order_Type” ASC

    Can you please suggest a solution for this.
    Thanks in advance.

  7. vibhor says:

    Hii,
    Vibhor this side.
    Supposing I am having CV1 (cal view 1) and CV2 and CV2 is using input parameters in it. Now I drag & drop CV2 in CV1. What configuration do I need to do in CV1 to open the input parameter that I have used in CV2. How will I check my input parameters in CV1 now ?

    • Aman Bhardwaj says:

      Hi Vibhor,

      In your case, you need to map the input parameters in CV2 to the view CV1 parameters.
      To do this, Select the Semantics node in CV1, and in the Variables/Input Parameters tab, choose “mapping icon”.
      You can choose the Auto Map by Name option to automatically create the input parameters corresponding to the source and perform a 1:1 mapping.

      Regards,
      Aman

  8. Deepak srivastava says:

    can anyone tell when to use variables and when to use input parameters.can we use both in any case.plz

    • Aman Bhardwaj says:

      Hello Deepak,

      Variable can be used only to filter the data based on the user’s input. But if you want to perform some calculations based on user’s input then Variable can’t handle this and input Parameter is the only savior.

      Regards,
      Aman

  9. sai says:

    Hi,
    ​I need a help on parameter mapping to bring cascading entries on input filter criteria. e.g. according to country, I need display company codes dynamically.

  10. Bisu says:

    Hi,

    Please let me know how can I use Input parameter for range and intervals. For example in input parameter I need to use Date to and Date from. how can I use I/P parameter for this case.

    Thanks
    Bisu

  11. Dany says:

    Hi,
    anybody know how to address NULL values within a placeholder ?
    something like ‘PLACEHOLDER’ = (‘$$DISCOUNT$$’, NULL)
    Thanks !

    Dany

    • Admin says:

      Hi Dany,
      As a workaround, you can create a calculated column on DISCOUNT field like

      IF(ISNULL(“DISCOUNT”),”,”DISCOUNT”)

      This would convert the null values into a blank sting (”) and then you can do the comparison.

      You can also try to use the ISNULL(“DISCOUNT”) in your filter condition if that helps.

  12. Christine says:

    Hello,

    we are using SAP BW (without HANA) and HANA will be one of the source systems for SAP BW. Therefore I created a DataSource in SAP BW to get data of one view with inputparameter. Unfortunatly I do not know how to pass values for the inputparameter from SAP BW to HANA. At the moment the default values of the view will be used while showing the data of the view in the InfoProvider via the DataSource in SAP BW. Does anyone know how to pass values for the inputparameter?

    Thanks in advance,
    Christine

    PS: I only know that it is possible to pass values to inputparameters while using SAP BW on HANA and a special InfoProvider “VirtualProvider based on a HANA View”. Unfortunatly we do not use SAP BW on HANA.

Leave a Reply

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

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy