Skip to Content

Using Multiple Values in Input parameter for filtering in Graphical Calculation View

Hi Folks,

Note: This blog is creating during earlier versions of SAP HANA.

         If you are on SP09,You have an supported option of multiple values parameters.

         Refer  Page no: 106

In the previous blog SAP HANA: Handling Dynamic Select Column List and Multiple values in input parameter have shown on how to select multiple values for filtration using “Replace” function in procedure.

In that document, we went to procedure approach, since the user needs the dynamic output based on input conditions.

But if the output is static column list, you wouldn’t want to use procedure, so in this blog will be explaining on how to achieve the same functionality of handling multiple values filter condition on a graphical calculation view using “Projection”.

Problem Description:

Giving the flexibility of choosing the values he wishes to see in the report and then pushing the filtration logic to the lowest level possible.

Case 1: To select single value or multiple values for filter based on the input from the user

Case 2: To select “All” values if he doesn’t want to apply any filter

**Note: If using “HTM5 Dashboards” , you can present the dropdown for filter as shown below:

Screen Shot 2013-12-30 at 10.18.25 PM.png

This approach is very useful, if your dropdown has more values in the selection like more than 100 selections.

Now, Let us create an analytic view for our testing as shown below:

Screen Shot 2013-12-30 at 9.18.06 PM.png

Now after adding the required fields to the “Output”.

Let us create a “Input Parameter” to hold values of selection for “Region” from the user as shown below:

Screen Shot 2013-12-30 at 9.40.30 PM.png

Now Let us create the filter using “Expression” as shown below:

in("REGION",'$$In_Region$$') or match ("REGION",'*$$In_Region$$*')

Screen Shot 2013-12-30 at 9.44.42 PM.png

Data Preview:

Case 1 : ( Single or Multiple Values) :

(a) Single Value:

Screen Shot 2013-12-30 at 9.58.31 PM.png

Sql Statement:


Screen Shot 2013-12-30 at 9.57.28 PM.png

In the above screenshot we can see 1 value i.e AMER coming in output.

(b) Multiple Value:

You have to input the value like this example: AMER”,”APAC as shown below:

Screen Shot 2013-12-30 at 10.00.25 PM.png

Sql Statement:


Screen Shot 2013-12-30 at 10.02.25 PM.png

In the above screenshot we can see two values i.e AMER & APAC coming in output.

Case 2 : ( All Values — No Filtering) :

We need to pass * as shown below :

Screen Shot 2013-12-30 at 10.08.45 PM.png

Sql Statement:


Screen Shot 2013-12-30 at 10.11.29 PM.png

In the above screenshot we can see all the 3 regions coming in output.

**Note: This approach is useful when your reporting solution is HTML5 dashboards and you cannot use Variables (multiple values) for filtering.

Hoping that blog is helpful for you , do let me know your feedback on this.


Krishna Tangudu 🙂

You must be Logged on to comment or reply to a post.
      • Hello Krishna,

        I have read your article "Using Multiple Values in Input parameter for filtering in Graphical Calculation View" but I can't make it work in my Ecliplse. Does your instructions still work, or are related to an old version of Eclipse?

        I have Eclipse IDE for Java Developers. Version: Luna Service Release 2 (4.4.2).

        Best regards.

      • Hi Krishna,

        Thanks for the sharing, your blogs are very helpful.

        Based on this example, do you know if it is possible to make the input parameter optional? For example, I like the filtering by regions but I also want to get all the records in case I don't enter the value of the parameter.


        Eric Du

        • Hi Eric,

          Thanks for your feedback 🙂

          If you see the blog closely, you can see that passing '*' to retrieve all the regions.I think that will help you .


          Krishna Tangudu

          • Krishna, for non-string types, like INTEGER, do you know how to use multiple value input parameters as the match() and LIKE doesn't work for INTEGER. Thanks.

          • Hi Eric,

            You can create a calculated column and convert the Integer to String and then use it.

            Ex: In the scenario, i have taken for this blog i have "Employee_Type" which is a integer.

            I have created a calculated column named "Emp_Type" with formula string("EMPLOYEE_TYPE")

            And then kept this expression in the projection filter:

            in("Emp_Type",'$$In_Employee_type$$') or match ("Emp_Type",'*$$In_Employee_type$$*')

            And it works.

            Do let me know in case you still find some issues.


            Krishna Tangudu

          • Hi Krishna,

            If I have to create a calculated column at projection then, in that case the input parameter cannot be passed till AT/at table level to minimize the data set.

            Or in other words you cannot pass the input parameter with multiple values to table level in case of integer data type. Is my understanding true?

      • Sir can you share the syntax for passing multiple values from input parameter when it is mapped to an input parameter from a Table Function.

        Also, 100","200 syntax doesn't work in HANA SP12. Can you please share the compatible syntax.

  • Hi Krishna,

    Really nice one 🙂

    and one doubt(its been already discussed in multiple post, still ). I have one calculation view with mandatory parameters. Say "MATERIAL" and "TO_DATE" and "FROM_DATE".

    I need to call this calc view from a procedure passing these parameters .

    I am able to run this from SQL CONSOL


                      sum("SIGNEDSTOCK") AS "SIGNEDSTOCK"

                      FROM "_SYS_BIC"."W_METALS_ONE.Modeling/CV_AVG_STOCK_MAT_PER"


                      'PLACEHOLDER' = ('$$MATERIAL$$', 'DEMO22'),

                      'PLACEHOLDER' = ('$$TO_DATE$$', '20140219'),

                      'PLACEHOLDER' = ('$$FROM_DATE$$', '20130101'))

                                        where STOCK_TYPE = 'A'

                      GROUP BY "STOCK_TYPE" ;

    If I use the same in Procedure, it get activated successfully but, run time errors happening.

    Any hope ?

    CE Function : CE_CALC_VIEW - running with placeholders


    • Hi Sreehari,

      Thanks for your feedback.

      I Just replied to your thread while you are commenting on this blog 😉


      Krishna Tangudu

  • Hi Krishna,

    Thanks for the nice blog. It works as described in HANA Studio. However, have you tries consuming(directly) in any front-end tool(apart from HTML).

    I tried the scenario consumption in Analysis Office for Excel and get the attached error. Have you come across this scenario?



  • Hello Krishna,

    I followed the above mentioned steps. Below is my query ,


      TOP 200 "PRODUCT_CLASS",



    FROM "_SYS_BIC"."SK_TEST_PACKAGE/RANGE_TEST" ('PLACEHOLDER' = ('$$In_Region$$', '0001121815","0001121816'))



    But it fetches no results. Any thoughts why this is happening.

  • Hi Krishna,

    Very useful blog.  As you mention HTML5, I assume you have been able to pass in multiple values for a single input parameter in your oData service call?  If so, I would be very grateful to see an example url with the syntax for passing multiple values - I can get the multiple values working with data preview, but not with an oData service call.

    Just to be clear - I'm not talking about $filter - I want to use input parameters to filter on a column in my model that I do not aggregate and expose in my model and odata server (hence cant use $filter).

    Many thanks, much appreciated.

  • Hi Krishna,

    I am not able to correctly use APPLY_FILTER functionality and I am using HANA SPS09.

    My Use Case as below: Calculation ViewInput.jpg

    Below line of code gives me the result in the Table1 as given below.

    var_ff = CE_AGGREGATION(:var_defects, [COUNT("ContextId") AS "Defects"],["EmployeeName"]);


    Defects EmployeeName
    4 admin
    6 admin1
    8 admin2
    10 admin3

    I want to send the Input parameter say admin'',''admin1 and get result for only that Employee Names.

    My below Calculation View is activating correctly but not giving the result.(AT_DEFECTS is Attribute view)




      var_defects = CE_JOIN_VIEW("_SYS_BIC"."aProdV1/AT_DEFECTS",["EmployeeName","ContextId"]);

      var_ff = CE_AGGREGATION(:var_defects, [COUNT("ContextId") AS "Defects"],["EmployeeName"]);

      /*SQL_STR:='"EmployeeName" IN (''admin'',''admin1'')';/* working one Input value hard coded and gives desired output*/

      SQL_STR:='"EmployeeName" IN (''$$NAME$$'')';/* NAME is INPUT PARAMETER */


    END /********* End Procedure Script ************/


    Please the attached Input.jpg for Input parameter and output.

    I want to filter the data by passing input parameter say admin'',''admin1 etc

    Please also let me know on escape character used in HANA.(I mean how to pass input parameter)



  • Hi Krishna,

    A very helpful document indeed. 🙂

    Requesting for your kind advice on a extension of this scenario as described below:

    I wish to filter the data on say  different criteria of type

    Country_list_1     Applicable_employee_grades_1     Amt_limit_1       Currency_1

    Country_list_2     Applicable_employee_grades_2     Amt_limit_2       Currency_2

    and so on...

    where the number of such criteria is variable and I need to filter the data set on all the available criteria at any point of time.

    How would I achieve this using graphical modelling and input parameters?

    Thanks for your help in advance. 🙂


    Anindita Bhowmik

  • I am on SP10. Creating an input paramter with multiple entries is working fine EXCEPT when the parameter is left blank. If it's left blank then it should equate to select all (like in ECC). I am using in() function. I tried above syntax but it's not working because it's prior to SP9.

    But I'm getting an error when leaving it blank. I get the following error

    column store error: search table error:  [6968] Evaluator: syntax error in expression string;expected TK_ID,parsing "if(\nin(\"RYEAR\",  [here]) = 1, \"RYEAR\" = '2010', \"RYEAR\" = '2011' \n)"

    Possible Cause(s): Syntax error due to data type mismatch for the input parameter value used in the calculation of a calculated column.

    Proposed Solution(s): Provide a value with the matching data type as that of the input parameter either as default value or at runtime during data preview.

    Any advice?

  • Need help in how to pass multi input values that will be passed a comma delimited string. I have a variable already defined in the Hana view and it is setup to accept multiple values.

    I am using Crystal Enterprise 4.0 to call this input variable from Hana View

    The user will be given one input parameter where they will enter Hundreds of input values that are separated by a comma.

    I used the below logic

    select * from "_SYS_BIC"."supplychain/SALES"('PLACEHOLDER' in ('$$VAR1$$','REPORTVAR'))

    VAR1 is the variable I have defined in the Hana AV

    REPORTVAR is the input parameter I have define in Crystal Enteprise.This I have defined as multi value parameter so that the user can enter values like

    Eg: 12345,58796,23456

    I even tried passing it as '12345','58796','23456

    The data is not getting filtered either ways.

    Any Help



    • Hi Aswin,

      This expression has to be entered in the expression editor of the node where the filtering should happen.


      Kindly note not to select 'List Of Values' option in the drop down list of filters to generate this expression. Enter this manually in the expression editor.

      In the input string ensure the string enters this filter in the form 12345'',''58796'',''23456 where '' is two individual single quotes and is not a double quote.

      This is when you are using an input parameter and not an input variable.

      Hope this helps. 🙂


      Anindita Bhowmik

      • Thanks Anindita. Is there any solution on how to use this filtering as input variable.

        The view should filter all the entered values in the Hana db level.

        Also the input string u suggested that the format to enter should be 2 individual single quotes then the value followed by the 2 individual single quotes...Is there a way where this can be avoided . Instead allow the user to just enter a string that is just comma delimted.



        • Hi Aswin,

          Input parameters are the method to enable filtering at HANA DB level. Input variables are applied after the completion of the processing of the view logic.

          However, if you would wish to use input variables,

          you need to define the variable from the semantics node in the view. Select single value and allow for the suggested list of values from possible set of comma separated lists stored in a table/view.

          The input string can be entered as comma delimited values by the user. This needs to be formatted as per the format suggested using front end logic and then passed to the hana logic to enable it to be parsed.


          Anindita Bhowmik

          • Hi Anindita

            I am now trying to create the multi select input parameter. In an earlier note you had suggested This expression has to be entered in the expression editor of the node where filtering should happen IN("VAR",'$$VAR$$'). I am not able to follow this as I am still new to Hana. Can you please guide me further on where exactly to find this expression editor. When you say node does that mean to the field that is in Hana View.

            After this expression is written how to call this parameter from Crystal enterprise.



          • Hi Aswin,

            Consider you have taken a table 'ABC' into a Projection_1. Say, EMP_COUNTRY_CODE is the column in that table on which you wish to apply the input parameter which passes a multi country selection.

            In the Expression editor of Projection_1, we would enter,

            IN("EMP_COUNTRY_CODE",'$$VAR$$') where 'VAR' is the input parameter and is not a variable.

            To test this within the limits of the hana studio without getting into Crystal enterprise, create a dummy table say DUMMY.

            INSERT INTO DUMMY VALUES ('MM'''',''''MN'''',''''MO'''',''''MR')

            where 'mm','mn','mo','mr' are countries based on which the rows of the table 'ABC' have to be filtered. Put this in a view say 'AT1'. In the input parameter "VAR" reference this list from the view and test it.

            For the integration testing between Crystal enterprise and HANA, I m not very sure on how this would happen. You could check this post: How to consume value help for Variables/Input Parameters in BOBJ reports in case it would help.

            The method suggested by me is a pure workaround as of Sp09. The simple consumption of a comma separated list 'MM','MN','MO','MR' as suggested by the post: Input Parameters and Variables in SAP HANA Modeling didn't work in my development scenario and hence, this alternative.

            You could try what works for you. 🙂


            Anindita Bhowmik

  • Hello Krishna,

    Thank you for sharing such a nice Blog. I have a similar scenario for you Case 1(b), where you are passing multiple values like AMER'',''APAC. This works fine if I do the data preview of calculation model in HANA. I just created a universe and pulled the objects and provided 2 values using same pattern as above but its not returning data since webi generated script looks like - ('$$IP_REGION$$','AMER\''\'',\''\''APAC').

    Could you please suggest how the multiple values should be passed from webi manually (not selecting from LOVs).



  • Based on HANA version all multi-value input parameters will not work.

    Example(Single value): C001

    Example(Multi Value):C001'',''C002.

    Henceforth we need add and click on + or Plus symbol and provide values separately.



  • I am on SPS 11, and my requirement was to pass multiple values as  input parameters to a graphical view dynamically.

    1)I created a function ,in _sys_bic schema to return a concatenated string of the form 2017”,”2016.

    2) I created a input parameter to my graphical view of the type “derived from stored procedure/function”.

    3) used the function(step 1) in the definition of the input parameter and did the mapping of input parameter of the function to input parameter of the graphical view.

    4) Created a filter in the graphical view as below
    (in(“FISCAL_YEAR”, ‘$$INP_FY$$’,’DEFAULT’))

    View gets activated fine but on data preview it does not show any data. The table on which the view is created has data for FIscal_year = 2017,2016
    I tried with different forms of concatenated string and different configurations for input parameter of the view (single, multiple) but could not get it working.

    I know this could have been done via a scripted view with apply_filter etc. My objective was to get this working on graphical view on sps11. Any inputs are welcome
    Also if any one has any input on ”external to internal convertion function”, option in the input parameter configuration window, in SPS 11, please share . I could not get that option working either.

    Thanks in advance.

  • I have Input parameter in Calculation graphical view which is accepting multiple entries and not a mandatory one. I need some other object values that need to be assigned dynamically to it so I am achieving this by writing a code in the expression editor.

    Code is something  like the following

    ( in('Current Month Year', $$IP_GI_MnYr$$) AND "ACTUAL_GI_MONTH_YR" = "CURRENT_MONTH")
    (in('Previous Month Year',$$IP_GI_MnYr$$) AND "ACTUAL_GI_MONTH_YR" = "PREV_MONTH")
    in( "ACTUAL_GI_MONTH_YR",$$IP_GI_MnYr$$ )
    $$IP_GI_MnYr$$ = ''

    1. I am able to pass multiple parameters
    2. I am able to give a combination of date string and varchar value

    But I am unable to execute the Input parameter without passing any value or executing the parameter with null.

    If I don't give any value it should execute for all the values in the field.

    Please, can anyone suggest how can I achieve?

  •  Another issue with '*' value:


    We have 5 input parameters in our SAP HANA view with default value ‘*’ to have the possibilities to select all values.

    Now when we want to select data from this HANA view into our table function using script we pass input parameter values using “PLACEHOLDER” statement but for this statement ‘*’ is not working( it returns no result).

    More important point is this that if I hard code value as ‘*’, it is showing the data correctly but if I use variable (that holds ‘*’ value), it shows me no data.

    For example:

    -          For plant (WERKS) filter, if I put constant ‘*’, it is giving me all data

    -          For plant (WERKS) filter, if I put use a variable (ZIN_WERKS) that have ‘*’ value passed from input screen of final view, it is giving me no data.

    I checked that variable is correctly filled with ‘*’ value but still no data that we are not able to understand.


    Additional question, do we always give default value as ‘*’ for input parameters because if it is blank or empty, it always filter on blank values and value help could also not be generated?


  • Hi @Krishna Tangudu

    I have created a calc view with a mutli value parameter when i try to consume it in a XSOdata , it throws me the Unsupported Parameter Error.  This issue is occurring only if i nested the Calc view with a multi value parameter

    This is Text view of the Parameter

    <variable id="IP_PARAM" parameter="true">
    <descriptions defaultDescription="IP_PARAM"/>
    <variableProperties datatype="INTEGER" mandatory="false" defaultValue="0">
    <valueDomain type="empty"/>
    <selection multiLine="false" type="SingleValue"/>




  • Thanks Krishna! I have the same requirement but this code worked for me: in("REGION",'$$In_Region$$') or match ("REGION",$$In_Region$$)

    Notice that in match clause, '* has been removed before and after the input variable.

    • Hi,

      My scenarios are : single value , multiple values and if user enters * value it suppose to fetch all values


      in("REGION",'$$In_Region$$') or match ("REGION",'*$$In_Region$$*'

      • when i give 2  or 3 values it says "Error: SAP DBTech JDBC: [2048]: column store error: search table error: [34023] Instantiation of calculation model failed;exception 306002: An internal error occurred"

      Can you suggest what modification can be done here ?

      I am using HANA studio Version: 2.3.5.

      HANA system version is (fa/hana1sp12)


      When i contacted SAP they said it could be new version issue asking to check in older version...:|



  • HI Krishna,


    This has been a very informative blog.Thank you.

    I was able to filter based in single value and multiple values, but i faced a problem with filtering on all.

    My Filter expression is:

    (IN("REGION",'$$IP_REGION$$') or match ("REGION",'*$$IP_REGION$$*'))

    When i pass '*' as the Input parameter it fetches data for all the REGIONS except for the ones with Null values.

    Is there any way to mitigate this error so that when null is passed, i get all the data irrespective of whether the value is present or absent( NULL AND NON NULL ROWS)