Skip to Content

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.

9 Comments
You must be Logged on to comment or reply to a post.
  • 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”
    /
    • 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

          filter

           

          result

          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%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

          • 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