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.
- 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:
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 🙂
- SAP Managed Tags:
59 Comments
