Skip to Content
Technical Articles

Passing multi-value input parameter from Calculation View to Table Function in SAP HANA – step by step guide

Introduction

In my previous post I demonstrated how to create Table Functions (TF) in SAP HANA XS Classic. One of the TF disadvantage, which I mentioned there was the fact, that they are not supporting multi-value input parameters – and this is official:

Personally I think this is quite a big limitation, because when some functionality cannot be covered by graphical view, then recommendation is to go for SQL Table Function – so why we cannot pass multi-value parameter there? In the reporting world this is one of the most basic functionality to give the user flexibility in defining input criteria, isn’t it?

For some scenarios this limitation forced me to implement complex logic in graphical views, although in SQL it could be done much easier. But I could not take away from the end user possibility of selecting more than one value.

For small datasets when there was no option for implementing report logic graphically, I was creating Table Function (without input parameters) and then in graphical view, which consumed that TF I was creating variables. But as you might know in this approach, values provided by the user in variable  were not pushed down to the table directly, so performance of such a solution is simply bad.

Finally I got a requirement which forced me to find a way of passing multi-value parameters directly to the SQL query – this was for creating BOM (Bill-of-Material) report, where user wanted to display for inputed materials all its components. Now imagine running such a report for all existing materials and recursively searching for all its components from all levels – impossible even for HANA 😉 This was the moment when I started deeply look for the solution of passing multiple values through Calculation View Input Parameter to Table Function.

In this blog I will share my findings and workarounds which I discovered.

Why passing multiple values from Calculation View parameter to underlying Table Function parameter doesn’t work?

Common developers mistake is trying to apply filter combining multi-value input parameter with SQL IN predicate, which won’t work:

To visualize the issue here let’s create a simple Table Function, which will return only the value of inserted parameter:

FUNCTION "_SYS_BIC"."TMP::TF_DUMMY" ( INPUT VARCHAR(100) ) 
	RETURNS TABLE
		(
			INPUT_PARAMETER VARCHAR(100)
		)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS

BEGIN

	SELECT :INPUT AS "INPUT_PARAMETER" FROM DUMMY;

END;

Then let’s create calculation view which will consume that TF (1). Before activation remember to map input parameter between TF and calculation view (2) and set the input parameter as Multiple Entries:

After activation pass multiple values to the selection screen and check the output:

At first glance the result may seem to be correct, because these are the values which we wanted to pass to the IN predicate, but in fact it’s wrong. Here instead of passing 3 separate values, we are passing single string with all three values concatenated. This is how the syntax in background would look like:

WHERE COLUMN IN :MULTI_VALUE_IP => WHERE COLUMN IN ‘ AA, BB, CC ‘

So instead of filtering three separate values there is a filter on one concatenated value (I skipped here single quotes to make it more clear, because actual value which is passed would be: ‘ ”AA”,”BB”,”CC” ‘ )

Below I showed how HANA converts multiple inserted values (on the left) and what values we would need for IN predicate to make it work (on the right):

What are the workarounds to pass multi-value input parameter to Table Function?

There are two approaches to implement multi-value parameter handling.

  1. Using APPLY_FILTER function
  2. Creating custom function splitting string into multiple values

For the demonstration purposes I created database table TEST_ORDERS and inserted some sample records there.
The structure of the table is as follows:

I will use it as a data source for further demonstrations.

1. Using APPLY_FILTER() statement

The APPLY_FILTER is SQL function, which allows creating dynamic filter on a table. Syntax of the function is following:

APPLY_FILTER(<table_or_table_variable>, <filter_variable_name>);

To give you more examples I prepared three different scenarios.

Scenario 1.

Requirement is to create Orders report with Mandatory and Multi-Value Input parameter for Customer Number column.

Here is definition of the Table Function:

I. Define input parameter. Assign varchar data type to the input parameter (even if the filtered column is of integer type). Consider that HANA concatenates multiple inserted values into single string, so to allow user to input as many values as it’s supported I defined maximum number of characters which is 5000. I will describe this limitation in details in the last section.

II. Create a variable and assign dynamic filter definition. Filter definition syntax should be the same as it is used after the SQL WHERE clause. Combine the filter definition with the input parameter ( || symbol is used for concatenating strings )

III. Assign SELECT query to the variable. This Table Variable will be consumed by APPLY_FILTER function.

IV. Use APPLY_FILTER function. As first function parameter provide Table Variable with SELECT query (here :ORDERS) and for second parameter – Variable with dynamic filter definition (here :FILTER). Assign output of APPLY_FILTER to another Table Variable (here FILTERED_ORDERS)

V. Query the Table Variable with the output of APPLY_FILTER function.

Once the table function is activated create a graphical Calculation View. As a source provide created Table Function and map input parameters:

Select options Multiple Entries and Is Mandatory for that input parameter:

Now let’s run the data preview, select multiple values on selection screen and see the output:

So we just passed multiple values through Calculation View to Table Function!

Scenario 2.

Requirement is to create Orders report with Optional and Multi-Value Input parameter for Customer Number column.

To support optional multi value parameter there is a need to adjust a part of the code (only part II, everything else keep as in Scenario 1.):

II. Create a variable and assign dynamic filter definition. Here there is additional check implemented. If the value of inserted parameter is empty then assign to FILTER variable string 1=1, otherwise use the same condition as in first scenario. When you pass to APPLY_FILTER function value of 1=1 as a filter variable, then all records will be returned (because this condition is always valid). This is a workaround for displaying all the records when user doesn’t provide any value to input parameter.

Now when you don’t provide any value in input parameter, you will get all records in the output:

For other inserted values view will act as in Scenario 1.

Scenario 3.

Requirement is to create Orders report with Two Mandatory and Multi-Value Input parameters one for Customer Number and second for Category column.

In this scenario two parts of code need small adjustments compared to the Scenario 1.

I. Add new input parameter for Category.

II. Adjust string assigned to FILTER variable to include CATEGORY as a filter.

In Calculation View you need to add new input parameter and map it with the one which was created in Table Function (IP_CATEGORY):

Now you can input multiple values for both parameters and filter will consider both of them:

2. Creating custom function splitting string into multiple values

Second approach to enable passing multiple values to Table Function is to create custom SQL function. Logic inside that function will split concatenated strings into multiple records. Here is the code for the custom function:

FUNCTION "_SYS_BIC"."TMP::TF_SPLIT_STRING" ( INPUT_STRING VARCHAR(5000) ) 
	RETURNS TABLE
	(
		"OUTPUT_SPLIT" VARCHAR(5000)
	)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN

	DECLARE COUNTER INT := 1;
	SPLIT_VALUES = SELECT SUBSTR_BEFORE(:INPUT_STRING,',') SINGLE_VAL FROM DUMMY;	
	SELECT SUBSTR_AFTER(:INPUT_STRING,',') || ',' INTO INPUT_STRING FROM DUMMY;
	
	WHILE( LENGTH(:INPUT_STRING) > 0 )	
	DO
	
	   SPLIT_VALUES =	
	   
	   		SELECT SUBSTR_BEFORE(:INPUT_STRING,',') SINGLE_VAL FROM DUMMY	 
				UNION 	   
			SELECT SINGLE_VAL FROM :SPLIT_VALUES;
	
	   SELECT SUBSTR_AFTER(:INPUT_STRING,',') INTO INPUT_STRING FROM DUMMY;
	   	
	END WHILE;
	
	RETURN
	
	SELECT REPLACE(SINGLE_VAL,'''','') AS "OUTPUT_SPLIT" FROM :SPLIT_VALUES; 

END

Now we can consume that function in the SQL query. For the demonstration purposes let’s use Scenario 1.

Scenario 1.

Requirement is to create Orders report with Mandatory and Multi-Value Input parameter for Customer Number column.

Create graphical Calculation View, use Table Function as a source and map input parameters:

Now let’s run the data preview, select multiple values on selection screen and see the output:

The result is the same as in the approach with APPLY_FILTER function. From the performance perspective APPLY_FILTER will work faster, but the difference will be minor.

Limitations

Although the presented solutions allow to pass multiple values through Calculation View to Table Function, there is a major limitation for both approaches implementations.

As mentioned at the beginning of that post HANA concatenates all inserted values into single string. Considering the fact that maximum number of characters allowed in VARCHAR data type is 5000, at some point you may realize that number of inserted values is limited.

Let’s take an example of Material (MATNR) field which in SAP is of VARCHAR(18) data type. When inserting two materials:

Concatenated string will look as follows:

The concatenated string length for this two values is:

  • 18 x 2 characters for two materials (36 in total)
  • 2 x 2 single quotes (4 in total)
  • 1 x comma (1 in total)

For the presented example the total string length is 41.

Keeping in mind that the maximum varchar length is 5000, for this scenario the maximum number of Materials which you can pass to table function equals to:
5000 / (18 characters + 2 quotes + 1 comma for each material) ~ 238.

For some scenarios it can be more than enough, but sometimes you want to allow import to the input parameter thousands of values. In such a case if the string for input parameter will exceed the limit of 5000 characters you will get error as below:

Summary

The workaround shown in my blog allows you to pass multiple values into Table Function however you need to keep in mind that there is a big limitation for the number of values which you can pass. Also when the report contains many input parameters and some of them are mandatory, others optional then it can be quite a big effort to support all the various scenarios and then maintenance can be difficult.

My approach for reporting on Table Function is to pass all mandatory input parameters into the SQL code. For optional selection criteria I create Variables directly in graphical Calculation View.

From version of SAP HANA 2.0 SP03 (XS Advanced) there is a new functionality, which can be applied for described scenarios – see documentation here (thanks Lars Breddemann  for sharing!)

There are many different reporting scenarios, so I will be really appreciated if you comment the post, share your ideas on that topic or provide your feedback.

 

Thanks for reading!

Likes and shares always very welcomed 🙂

29 Comments
You must be Logged on to comment or reply to a post.
  • Hello Konrad,

     

    Thanks for the wonderful post.

     

    I’ve a sql scripted calculation view and I want to allow multiple values in an input parameter. Need to be able to filter the script view based upon multiple values in one parameter. Can you guide me with this scenario please.

     

     

    Thanks

    Ravi.

    • APPLY_FILTER function should also work in Scripted View, however if possible you can migrate scripted view to table function first and then apply the approach which I described in the post.

  • Dear Konrad,

    although the table Integration and interface capabilities have been extended with the rise of SAP HANA 2.0 SP03 we have to consider the real world and the prerequisites:

    • not all applications run on that SP level and migrating to it might take some time here and there
    • not all systems and customers haven’t adapted to the web IDE as yet

    That is, as users require selctions of multiple values, your approach will remain a good one  to overcome the limitations for quite a while even as we have to accept further restrictions as you pointed out.

    Your contribution is of high value because it shows a deep insight of how HANA works at that point in conjunction with a common solution to get the things going (later rework accpeted!)

    Besides: It’s sometimes hard to find the SAP standard documentation 🙂

    Thank you very much for your high quality post!

    @bi4

     

    • As Florian replied – table type for input parameter is not supported for graphical and scripted views. If you are using HANA 2.0 XSA you can check new option for passing multiple values to table function.

  • Hi Konrad Załęski,

    Thanks for the nice blog! It’s really helpful. I am currently struggling with the restriction of 5000 characters length of the input parameter. How does multi value input parameter behave in case of hierarchy nodes selected in the prompt? Basically I have a multi value input parameter in calculation view and it’s tied to an infoobject in BW and restricted globally in BEx query with a hierarchy node variable. If user selects the top node of the hierarchy, query fails with the message “String too long”. I think system internally creates a string with leaf nodes of the top node selected by user which in turn becomes more than 5000 character and it fails. I must pass the input parameter values to table function as the overall calculation is dependent on the number of values selected by user. Therefore, restriction only at query level and not using the values in table function would not help.  Do you think if there is any solution for this? Thank you for your time!

     

    • I’m not sure how does it work with bex, but when using Hierarchy in native calculation view, then from my observation it anyway takes the last level of the hierarchy and passes it to the query. In this case hierarchy is used only for the display, but when selecting multiple values it behaves same way as regular input parameter. Make sure that Input Parameter is set as VARCHAR(5000) both in TF and in CV. Also consider that when generating the string additionally quotes and commas are being added which additionally limits the string length. Maybe you are trying to pass too many values at once.

  • Hello Konrad,

    Thanks for the post. I have one question.

    I have developed one Table Function (TF_TEST_ABC)  and one Graphical Calculation View (CV_TEST_ABC)

    In CV_TEST_ABC i have one Mandatory and Multi-Value Input parameter.

    i have to consume CV_TEST_ABC  in TF_TEST_ABC. Is there any way to achieve it using place holder.

    when i am trying the following syntax it is not working, the following syntax works only in case of single value input parameter.

    Select * FROM “_SYS_”.”workshop.test2/CV_TEST_ABC” (PLACEHOLDER.”$$InputParam1$$” =>  :userId,

    • Syntax which you mentioned should also work for multiple values. Make sure that you pass values in proper format – each value should be surrounded by double quotes. In your example if you have three users: AA, BB, CC then you should pass following string:

      DECLARE USER_ID VARCHAR(5000) := '''AA'',''BB'',''CC''' ;
  • Hello Konrad,

    Excellent Article. It gave much insights to people like me who have just started working on it. I have a question. I will have 50,000+ records of data which i would like to left join with other tables  and arrive at other field values based on conditions. If Table function doesnt support multiple input data(Work around also has limitations) what would be the other best approaches for handling 50,000+ records criteria?

     

    Thank you.

    • You can try to create table function without any input parameters. Then use that table function in calculation view and create Variable instead of Input Parameter in calculation view directly (variables supports multiple input values). Disadvantage of that solution is that filter from variable will not be pushed down to table level, so table function will execute query for all resultset and on top of that variable will filter records – this means that the performance of will be very low.

      • Hello Konrad,

        Nice article. One question though. I read another post(couldnt find it again) where they have stated with examples from visualization plan that the variables passed are indeed used in the WHERE clause in the bottom most node but based on your above answer it looks like the variable can only filter from the result set. Can you clarify please?

        Also, one additional question – Is table user defined function & table function both the same?

        Thank you.

         

         

        • Hi Prem,

          This is true that variables are passed to the lowest node, but only when talking about graphical calculation view (and also not in all cases – for some complex data models sometimes there might be some issues). When using Table Function as a data source of calculation view, then variables will not be passed to the table level of SQL query.

          Table Function is just an abbreviation of Table User-Defined Function.

          I hope it’s clear.

    • Sure, this is pretty straightforward. Just tick “Multiple Entries” checkbox on variable properties and then you will be able to pass multiple values.

  • single string can we pass multiple values concatenated using variables or input parameter?

    example:

    VARIABLE =  ‘”A”,”B”,”C”‘

    INPUT  PARAMETER =  ‘”A”,”B”,”C”‘

  • Excellent Blog Konrad…it will be very helpful as the requirement for passing multiple values is quite usual in the real-time world..

    Thanks for your time and effort to explain in such a nice way ….