Passing multi-value input parameter from Calculation View to Table Function in SAP HANA – step by step guide
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.
- Using APPLY_FILTER function
- 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:
To give you more examples I prepared three different scenarios.
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!
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.
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.
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.
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:
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.
hey really very useful content
Thanks a lot! very useful!
Great blog. Can be use in BW/4HANA ?
Sure, you can use this approach in your native developments in BW/4HANA.
Well explained ...Nice blog...
Nice one Konrad...easy to follow ...Great ..Thank you..
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.
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.
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:
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!
Thanks Andreas! Nice to hear that 🙂
Konrad Załęski Can you please look into below link?
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.
This just saved my day, thanks!
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.
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:
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?
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.
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?
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.
Thank you Konrad.
can we pass multi values in variable?
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?
VARIABLE = '"A","B","C"'
INPUT PARAMETER = '"A","B","C"'
I don't see any reason, why to pass multiple values this way. However If this is required you can use input parameter and then split inputted string into multiple lines and apply it in WHERE condition of SQL query (in other blog post you can check how to split string into multiple lines)
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 ....
excellent content. Well done
Very nice blog Konrad. The above worked fine with in SAP. But our users use the Power BI tool to access these calculation views. And when we are selecting multiple entries in there, it isn't fetching the results. Can you please help me on this?
Nice blog Konrad. Does this work even if we give single value as an input parameter?
I need to use the table function in select clause so that i can pass in parameters from a join and i get below error:
SAP DBTech JDBC: : feature not supported: field or table alias is not allowed as an input of table functions
Is there a way to do this yet? Sample query would look like
select tab.col1, (select * from table_function(tab.col1,tab.col2)) y
from table_in_my_app tab
And, the function has to be table function due to some unavoidable reasons. Please help.
I tried to follow steps as mentioned in your post, and is working fine when i am passing numeric values with multiple values at same time.
But when i try to pass string as input parameter, i am getting error as below:
SELECT * FROM "SYSTEM"."shubham.Model::TABLE_FUNCTION_TESTING_MULTIVALUED_PARAMETER_VARCHAR"('STEVE');
Could not execute 'SELECT * FROM ...' in 367 ms 906 µs .
SAP DBTech JDBC: : invalid column name: search table error: "SYSTEM"."shubham.Model::TABLE_FUNCTION_TESTING_MULTIVALUED_PARAMETER_VARCHAR": line 12 col 1 (at pos 489):  (range 3) invalid column name exception: invalid column name: STEVE: line 2 col 125 (at pos 515) (please check lines: 12, 15, 19, 20)
Can you please suggest any workaround in order to solve this?
If you query it from SQL console you need to add additional quotes to the string, for example to input two values (STEVE and TEST) use:
SELECT * FROM “SYSTEM”.”shubham.Model::TABLE_FUNCTION_TESTING_MULTIVALUED_PARAMETER_VARCHAR”('''STEVE'',''TEST''');
Thank you Konrad !! It worked.
Very Nice Blog.
Thank you Konrad
Thanks for this detailed article. How about passing multiple values from CV to Procedure to CV back(CV->PROC->CV). I have one input parameter called IP_PERIOD which is like 05.2020(MM.YYYY) format and I also have one more input parameter IP_YEAR which needs to be feed from IP_PERIOD. Basically the year(YYYY) portion of the period should be passed into IP_YEAR. It can have multiple values also such as 04.2020, 05.2019. Thats why I have created a stored proc which will accept input as IP_YEAR('''04.2020'',''05.2019''') and will pass output as unique year - 2019 and 2020. Now issue is my IP_YEAR in my CV after doing mappings with proc has to be multiple values. Even if I pass like '''2019'',''2020''' as input to IP_YEAR, it does not accept it.
Konrad, Nice Blog. My requirement is to Fetch range of data. Means, How can I Use Range of Input Parameters in HANA Graphical Calculation Views. E.g. I want data based on Fiscal year If I pass 2018 to 2020 in the input parameters. How can I do this?
You need to create two separate input parameters (i.e "IP_DATE_FROM" and "IP_DATE_TO")
I got your point. But in This case I just get the data of only 2 years/Dates data but I want the range of data. That is in between of dates that I put in input parameters. E.g. I put 2018 in "IP_YEAR_FROM" and 2020 in "IP_YEAR_TO". Now the point is, I want the data of 2018, 2019, and 2020. How can I do this?
Hammad Hassan Shah.
You need to apply date range filter in your SQL WHERE clause:
Hi Konrad Załęski
We are trying to count the number of records in a HANA view based on the date filter coming from a Worklist (SAP Tax Compliance). So the user chooses a date range via SAP Tax Compliance / Fiori and those values, we want to move to a HANA view. Now, neither a Table Function (SELECT COUNT) nor an aggregation are bring the results we want. Or a way to pass the date filter from Fiori maybe via an input parameter to the HANA view...
Example: HANA view = total records 400 | HANA view for x to y dates = total records 250. Currently, the system always shows 400.
I suggest to open the question on the SAP community and share the code. Without this detailed information it's hard to guess what can be the root cause.
Hi Konrad Załęski,
The code is simple:
COUNT (*) as "amt_items_prewhite",
COUNT (DISTINCT "USNAM") as "amt_users_prewhite"
The problem here is that the user inputs a data range in a tool called Tax Compliance (Fiori). And such value, I'm not aware of how to take it from the Frontend to HANA.
Is there a way that you are aware of to bring a value that the user writes manually?
Kudos for such a nice blog, surely it will save many from braintwisting ideas. However, I have a comment for the second approach where you split the input parameter string into multiple string values. It looks like if a user decides to pass just one value in the multivalue parameter scope (quite possible to do so in real cases), the function : "_SYS_BIC"."TMP::TF_SPLIT_STRING" ( INPUT_STRING VARCHAR(5000) ) won't return anything, since there is no SUBSTR_BEFORE validation for this case (without a comma(,) symbol in the string).
For example : if we try select output_split from "_SYS_BIC"."TMP::TF_SPLIT_STRING" ( '''single_val''' ) - it should have returned 'single_val' as output, but it won't return that.
Feel free to correct me if I am wrong.
Yes - you are right. If single values need to be supported then in addition, there should be a logic for example adding trailing commas if there is single value inputted.
Anyway APLLY_FILTER is a better approach.
Yes, the APPLY_FILTER one is a gem 🙂
Excellent Blog really! In order for the TF_SPLIT_STRING to work when single value is selected in a Multiple Entries Input Parameters, I slightly modified the function as following:
Slight modification to the the TF_SPLIT_STRING will fix the single value problem..
Could you please also help with explaining how to pass multiple values of a input parameter from target view(graphical calculation view to value help view(graphical) as
I am facing issue with passing multiple values of input parameter from main graphical calculation view to graphical calculation views created for value help. The same input parameters work fine when they are set for single values.
I posted this question (below link) but nobody replied. Could you please help if there is any solution to the issue:
Hello Konrad Zaleski, the article is good, however I want to clarify something, between the 2 approaches (APLLY_FILTER and Function) it will be much better to use the function since in this way you will indicate to the SQL optimizer which should be the clache plan it should generate.
If some one can help me
I was trying this similar example in table function with 2 input parameter, multiple entries
I tried writing it in different way not sure weather it is correct.
But I wanted to ask in the input parameter sematic type : what should we select as blank | unit of
Since we keep parameter type: Direct (for users to input value)
When I do data preview on semantic node ,I see no data
Please have a look at the screenshot attached
Any help is appreciated
One Note on Scenario 3 vs the APPLY_FILTER scenarios. If you do a PLANVIZ on both methods, the APPLY_FILTER appears to be more efficient as it applies the filter when it first accesses the table, resulting in fewer records throughout execution. The Scenario 3 methods (WHERE clause with Nested sub-select) appears to apply the filtering much later in the query execution resulting in more memory being utilized and more records being processed throughout the query execution. This might not be the case with all queries, but from the 3 limited examples I created, the APPLY_FILTER execution was more efficiency in each case.
Great blog, Thanks for sharing
Thank you for sharing!
Could you please help me on how to filter a table based on multiple values in a HANA procedure? Is it possible to directly filter a table rather than using calculation view to filter?
Hi @Konrad Załęski Can we use TEXT or NCLOB data type instead of using varchar to get unlimited number of characters?