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”.
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”.
2. Provide Name, description and select the data type as INTEGER.
3. Select the Aggregation block and right click on “Calculated Columns” to create a new column.
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$$
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.
7. The NET_AMOUNT will be calculated as per the given DISCOUNT value.
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.
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') )
No comments:
Post a Comment