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
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
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
9. The Output data wil be result of a query which will filter data for 2011 YEAR
Good Blog – Thanks
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
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
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
Hi Karthik
Thanks for the blog,Good work.
Good stuff Karthik.
Regards Jon
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
Hi Silvio,
Please refer to the following document. It explain in details how to use Input parameters in BO tools.
http://scn.sap.com/docs/DOC-27676
Regards,
Ravi
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
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):
Source: http://help.sap.com/hana/hana_dev_sqlscript_en.pdf (page 17/56)
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.
Does this now allow ABAP to pass RANGES fields to HANA queries?
did u get the answer to this? I am looking for the same
Hi Suraj, This one is even older than first.
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
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.
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
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
Brilliant.
It’s very userful