Example 1: filter with input parameter on numeric value
This example is referenced in my other post about input parameters. Please have a look at the other post to get a better understanding of the context for this example
We will use input parameter IP_1 to filter for records that have a productRating larger than 2.
As we will use the input parameter to filter on a numeric value the documentation tells us that the calling syntax should be (Note: you might sometimes find other syntax for passing input parameters. Please use this syntax with “=>”. This is especially relevant when passing input parameters inside procedures – not discussed in these posts – as otherwise the activation might fail):
(placeholder.”$$IP_1$$”=>2)
i.e., without enclosing the value 2 in apostrophes. This makes intuitively sense as we treat IP_1 as integer and not as string.
In addition, the documentation tells us that the expression during processing should also not include IP_1 in apostrophes:
“productRating” > $$IP_1$$
Again, it makes sense as we are not treating the value 2 as a string (and might want to evaluate, e.g., 1+1):
Here are the steps to model this requirement:
a) Create a Calculation View
b) Add table IPExample as a datasource
c) Map all fields to the output
d) Create direct input parameter IP_1 of type INTEGER (see screenshot below)
e) In the Aggregation Node enter the following filter (a graphical expression editor is also available):
f) Save, build and do a data preview on the view
g) When prompted for a value enter 2
You will now see only records that have a productRating higher than 2:
If you look at the respective SQL you will see that the expected placeholder syntax is used in the data preview query:
SELECT TOP 1000
“date”,
“product”,
SUM(“productRating”) AS “productRating”,
SUM(“amount”) AS “amount”
FROM “INPUTPARAMETERS_HDI_DB_1”.”inputParameters.db::example1″
(placeholder.”$$IP_1$$”=>2)
GROUP BY
“date”,
“product”;
Click here to navigate back to the context in which this example is discussed. You will also find further examples there.
Hi jan.zwickel,
could you please help out me on this issue,
Hi Siva,
not sure whether I got your requirement totally but you write “ARTICLE_ID”=’115121′
The single apostrophes indicate that ARTICLE_ID is a string. Therefore, also the input parameter should be included in ' ', i.e. '$$id$$'
The same holds probably true for 137 as SHOP_ID is probably also of type string, so '137'.
If your requirement actually is: If the input parameter is not filled by the end user no filter on "ARTICLE_ID" should be used, then have a look what your front-end tool generates in this case. If no PLACEHOLDER syntax is generated at all you will probably need to specify a default value for your input parameter and test against this default value in the filter string. If your front-end tool generates '<Enter Value>' you would compare against this, e.g.:
"SHOP_ID"='137' and ('$$id$$'='<Enter Value>' or "ARTICLE_ID"='$$id$$')
In general prefer SQL expressions over column engine expressions and use Web IDE when creating Calculation Views (this is independent of your current issue though).
Best,
Jan
Hi Jan
Thank you for a response , I'm sharing a screenshot of datatypes of fields for reference.
My requirement whenever I have to pass dynamic values input parameters should generate the expected results based on pass values in my case its failing
Here the Article id and Shop_ids having datatype as Integer, As mentioned above
Today created an input parameter Test where parameter type: Direct and datatype integer even though getting error no values to fetch; the semantic type is empty or Derived pattern
Hi Siva,
if your fields are integer you have to ensure that the input value is an integer as well. For example, '' or '<Enter Value>' are no integers. You can set a default parameter for the input parameter to avoid that it is empty, if the end user does not choose one. I cannot reproduce your error:
filter
result
Best,
Jan
Hi Jan,
Thank you for your support, I'm Learning Hana basic when I execute custom SQL syntax getting expected result as pe requirement,.In my case unable to get through graphical , could you please help on that
Here the syntax,
SELECT top 100
"ARTICLE_ID",
"SHOP_ID",
sum("MARGIN") AS "MARGIN",
sum("AMOUNT_SOLD") AS "AMOUNT_SOLD",
sum("QUANTITY_SOLD") AS "QUANTITY_SOLD"
FROM "_SYS_BIC"."EFASHI/CUBE_INPUT_VARAIABLE"(PLACEHOLDER."$$id$$"=>'177248')
GROUP BY "ARTICLE_ID",
"SHOP_ID"
Hi Siva,
you started with an error when executing the query. Now your window seems to suggest that your query runs through, so I am not sure what issue you are now having. Perhaps it is better if you open an incident and describe in detail what issue you are facing.
Best,
Jan
Hi Jan
Whenever Inputparneter parameter type is Direct /static getting expects results means I'm able to pass parameters dynamically,
As per my observation when parameter type: column getting a syntax error :
When a user data previews a calculation view, the error
"[2048]: column store error: search table error: [34023] Instantiation of calculation model failed;exception 306002" pops out.
Hi Siva,
I cannot reproduce your issue:
ip column setting
As said before: I don't think this is the right place for various how-to questions. There is quite some information that can be found by searching the internet for the specific topic. Also there is the documentation on modeling https://help.sap.com/viewer/e8e6c8142e60469bb401de5fdb6f7c00/2.0.05/en-US/e1861b1610e544cc993b4fb4b619e0d2.html
Additionally, SAP offers courses on modeling topics.
Finally, if you think you are facing a bug, please open an incident with an exact description of what you are doing and what effect the bug has.
Best,
Jan
Hi Jan
Thank you for your support , The reference document for your's i have understood can apply dynamically input parameters,
Let me work on Parameter type:Column and keep you updated
Hi Jan Zwickel,
I'm calling a calculation view which has 4 parameters using the following syntax but it's going in a dump. Could you tell me if this syntax is correct?
SELECT "MANDT",
"DIAG_ID",
"PROM_START",
"PROM_END",
"PROM_TYPE", from "_SYS_BIC"."ZAHEAD.ZD2F.REPORTING.FORECAST_ACCURACY/ZD2F_FC_ACCURACY_4A"((PLACEHOLDER."$$p_diag_id$$" => :iv_diag_id ),
( PLACEHOLDER."$$p_prom_start_start$$" => :iv_prom_start ),
( PLACEHOLDER."$$p_prom_start_endt$$" => :iv_prom_end ),
( PLACEHOLDER."$$p_sales_officet$$" => :iv_sales_office));
Hi Tushar Samal :
a) the comma after "PROM_TYPE" should be removed
b) remove the brackets around the individual placeholders, e.g.,:
(PLACEHOLDER."$$p_diag_id$$"=>:P1 ,
PLACEHOLDER."$$p_prom_start_start$$" => :P2 ,
PLACEHOLDER."$$p_prom_start_endt$$" => :P3 ,
PLACEHOLDER."$$p_sales_officet$$" => :P4)
see also the documentation on this e.g., here: https://help.sap.com/viewer/d1cb63c8dd8e4c35a0f18aef632687f0/2021_3_QRC/en-US/f1c17eb3a5b04f8b82d5908218e3fa68.html
c) these type of calculation views are deprecated.
Consider a) replacing the procedures by table functions b) migrating the repository calculation views to HDI calculation views.
Best,
Jan
Hi Jan Zwickel,
Thanks for the input. I checked with the syntax you provided but I'm getting the following dump.
What could be done for this?
Regards,
Tushar.
Tushar Samal : The error message seems truncated in ABAP so it might help to look into the indexserver trace for the full information about the error. The question is however unrelated to this blog