Skip to Content

How to Use Input Parameters in HANA Calculation View

May 2, 2012 | 11,237 Views |
 hanaIn-Memory Computingmodellingsap
Follow

THIS DOCUMENT ONLY HIGHLIGHTS A SIMPLE METHOD OF USING INPUT PARAMETERS.

Create a simple SQLScript based Calculation View

1. In the Modeler perspective of SAP HANA STUDIO, expand the system node from the Navigator pane.

2. Expand the Content node.

3. Right-click the required package.

4. From the context menu, choose New > Calculation View.

    • Enter the name and description of the calculation view.
    • Select the required package.

Define Input Parameter

Right Click Input Parameter from the output pane –> New

Setup the input parameter as defined below screen shot

Name the INPUT PARAMETER as IYR which will later be used in the SQLScript

OutputStructure.png

Create a Script-Based Calculation View

1. Choose SQL Script.

2. Select the required schema from the Default Schema drop-down list, for unqualified access in SQL.

3. Choose Finish.

4. Define the Output Structure

     a. Choose Define Output Parameter from the output pane.

     b. Add the output parameter(s) with the required data type and length.

     c. Choose OK.

In our case OUTPUT should be as below

OutputStructure.png

5. Define the Function

     a. Define the function using SQL Script commands.

In our case the SQL Script command should be as below. IYR is input parameter which is used to filter data for a particular calendar year

/********* Begin Procedure Script ************/

BEGIN
var_out =  select SUM(“SALES_AMT“) AS SALES_AMT, SUM(“SALES_UNIT”) AS SALES_UNIT, CAL_YEAR AS YR FROM  TABLE_A, TABLE_B      

WHERE “TABLE_A”.”CAL_SKEY”=“TABLE_B“.“CAL_SKEY” AND “TABLE_A“.“CAL_YEAR” = IYR      

GROUP BY “TABLE_B“.“CAL_YEAR”;

END;
/********* End Procedure Script ************/

6. SAVE and ACTIVATE the Calculation View

7. Click on DATA PREVIEW in HANA STUDIO

8. Enter the valid INPUT VALUE in the Input window as denoted

Input.png

9. The Output data wil be result of a query which will filter data for 2011 YEAR

OUtput.png

To report this post you need to login first.

19 Comments

You must be Logged on to comment or reply to a post.

  2. Former Member

    Hi Kartik

    Very Nice Blog , just what I was looking for . but could you please list the columns Table_A and Table_B.

    would make it very simple to understand .

    Thanks again

    Beth

    (0) 
    1. Former Member Post author

      PFB the exact code which I used to simulate the scenario

      BEGIN
         var_out = select SUM(“KN227869″.”FCT_HCO_NON_RETAIL_ALGN_WK”.”SALES_AMT”) AS SALES_AMT,
        SUM(“KN227869″.”FCT_HCO_NON_RETAIL_ALGN_WK”.”SALES_UNIT”) AS SALES_UNIT,
         “KN227869″.”DIM_CAL”.”CAL_YEAR” AS YR FROM
         “KN227869″.”FCT_HCO_NON_RETAIL_ALGN_WK”,
         “KN227869″.”DIM_CAL” 
         WHERE “KN227869″.”DIM_CAL”.”CAL_SKEY”=”KN227869″.”FCT_HCO_NON_RETAIL_ALGN_WK”.”CAL_SKEY”
         AND
         “KN227869″.”DIM_CAL”.”CAL_YEAR” = IYR
         GROUP BY “KN227869″.”DIM_CAL”.”CAL_YEAR”;

      END

      (0) 
  3. Arjun K T

    HI Kartik

    Very Nice Blog and Very much Helpful. Is there only one operator Input Parameter supports ?

    By default “Equal” is the operator and i tried to edit this but no way to change.

    Can you explain is there any other way to add “INLIST” as a  operator ?

    Thanks Again for this Blog

    (0) 
  6. Former Member

    Hi Karthik

    I’ve also crated a sample with Input Parameter on a calculation view. But, where can I use this caculation view. I’ve tried in Information Design Tool, in Crystal Reports for Enterprise, in Anaysis for Office (all Tools are on Version 4.0 with SP4 Patch 4). I allways get an error. All this Tools can’t use this Object. It seems this Tools don’t know anything about this Input Parameter.

    So do You know where to use this calculation view?

    Thanks,

    Regards Silvio

    (0) 
      1. Former Member

        Hi Ravindra,

        Thanks a lot.

        After 2 h I’ve got the solution.

        Very important: Even if the Input Parameter is written in Uppercase “VARMANDT” in HANA STUDIO, in IDT the Parameter Name must be in lowercase

        (‘PLACEHOLDER’ = (‘$$varmandt$$’, ‘800’))

        otherwise you got an error,

        Regards

        Silvio

        (0) 
        1. Henrique Pinto

          Exactly as described in page 17 of the HANA SQLScript Guide (the actual quote is about Procedures but it is the same principle for Calculation Views):

          Normally, procedures can only be executed via the call statement. By using WITH RESULT VIEW it is possible to query the result of a procedure as part of a SQL statement. Please notice that no

          variable references are supported in the WITH PARAMETERS clause and that all constants are passed as string constants (i.e. enclosed in single quotes). Also, parameter names are lower case independent from the original capitalization in the procedure signature.

                             

          Source: http://help.sap.com/hana/hana_dev_sqlscript_en.pdf (page 17/56)

          (0) 
  7. Henrique Pinto

    Apparently there is a formatting problem with the blog. The input parameter should be referenced as “:IYR” (with a colon) but it’s appearing in bold green font instead. Odd.

    (0) 
  9. Former Member

    Hi Karthik,

    I have also made the Input Parameter in the same way you mentioned. it asks for a prompt while data preview, but if I want see the same hana view through sys_bic

    it does not ask for a prompt and throws an error saying please enter a value for parameter or set any default value. I have already made it mandatory still it gives error.

    Is it the case that we do not get prompts in column views in sys_bic schema ??

    Please suggest I am really stuck.

    Regards,

    Karishma

    (0) 
    1. Former Member

      Hi Karishma,

      Did you get a solution for this ? I do have the same problem and not sure how to pass the paratmeter.

      Please let me know how you resolved this issue.

      (0) 
      1. Ravindra Channe

        Hi Bharath,

        Can you please open a discussion thread, post your query, may be a couple of screenshots of your Calc view. There are multiple ways of passing the values to input parameters and it works just fine.

        Also please mention your HANA revision no. But please open a new discussion thread.

        Regards,

        Ravi

        (0) 
      2. Former Member

        Hi Bharat,

        Ya the problem is resolved, Please tell us where exactly you are stuck along with screenshots so that I can help you over this.

        Regards

        Karishma

        (0) 

Leave a Reply