Skip to Content
Author's profile photo Jan Zwickel

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.

Assigned Tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo siva ram
      siva ram

      Hi jan.zwickel,

       

      could you please help out me on this issue,

      As per requirement whenever I pass dynamic values on Input parameters should expected results but my case not getting expected results
      Requirement: I have developed a cube with protection( shop_fact and mapping with the aggregated node selected required column like Article id, Shop_id, Margin, Amount
      step1: created an input parameter Test where parameter type: column and reference could Article id
      step2: At down level, projection_1 level: create a filter with syntax (“ARTICLE_ID”=’115121′) and (“SHOP_ID”=$$Article_id$$)
      please find attached screenshot your reference
      without any error after executing /Activation getting an error message
      Code : SELECT
      "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$$',
      '<Enter Value>'))
      GROUP BY "ARTICLE_ID",
      "SHOP_ID"
      Author's profile photo Jan Zwickel
      Jan Zwickel
      Blog Post Author

      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

      Author's profile photo siva ram
      siva ram

      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

      Author's profile photo Jan Zwickel
      Jan Zwickel
      Blog Post Author

      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

      filter

       

      result

      result

      Best,

      Jan

      Author's profile photo siva ram
      siva ram

      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"

       

      Author's profile photo Jan Zwickel
      Jan Zwickel
      Blog Post Author

      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

      Author's profile photo siva ram
      siva ram

      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.

      Author's profile photo Jan Zwickel
      Jan Zwickel
      Blog Post Author

      Hi Siva,

      I cannot reproduce your issue:

      ip%20column%20setting

      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

      Author's profile photo siva ram
      siva ram

      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

      Author's profile photo Tushar Samal
      Tushar Samal

      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));

      Author's profile photo Jan Zwickel
      Jan Zwickel
      Blog Post Author

      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

      Author's profile photo Tushar Samal
      Tushar Samal

      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.

      Author's profile photo Jan Zwickel
      Jan Zwickel
      Blog Post Author

      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