Developers often find it useful to utilize a table function (.hdbtablefunction) as the base projection within their calculation views. When this option became available, it effectively replaced the need to create script-based calculation views. Table Functions offer advanced SQLScript and SQL coding that can allow you to greatly enhance the performance of query execution with the use of variables for filtering. Calculation Views offer variables and input parameters. Variables can easily support multiple values for filtering, but the filter logic might not be pushed to the lowest levels of the query execution depending on the complexity of the model and table function SQL. Input parameters can be created in the calculation view and then mapped to the input variables on a table function. For single value input parameter values, this is very straight forward. You effectively define an input parameter in the calculation view and an input variable in the table function. Map the calculation view input parameter to the table function input variable. Within the table function, you define a filter in the WHERE clause setting some column equal to the input variable.
However, if the HANA calculation view’s input parameter is defined as “multiple value” we run into issues passing the values into the table function’s WHERE clause. Based on my tracing, I can see that the input parameter passes an array of values to the table function as a string. For example, if we have a HANA Calculation View input parameter called IP_PLANT that support multiple values and the user executes the calculation view, configuring two plants (1000,1001) in the input parameter screen, the calculation view would pass the string ‘1000’,’1001’ to the variable in the table function. If the table function WHERE clause has WERKS IN ( :V_PLANT ), you get no data returned. From what I can tell what is actually being executed is WERKS IN ( ‘ ‘1001’,’1000’ ‘). Note the extra single quote around the array which cause the WHERE clause to look for a string lateral value of ‘1001’,’1000’. Because these additional single quotes are applied at run-time, there is no way that I could find to remove them before the SQL was compiled.
With that said, I was able to find a workaround to this problem using some of the advanced capabilities of the table function script. Without using the WHERE clause of the table function SQL, there is a way to dynamically apply a filter to a table, table variable or view using a function called APPLY_FILTER(). This function accepts two inputs. APPLY_FILTER( <Table>, <Filter String>) and this function is defined the SQL before the RETURNS statement and compiled with the SQL. Below, I will walk you through the complete setup.
- Define a calculation view with two input parameters that is based on a table function.
2. Input Parameter (For this example) is defined as follows.
- Multiple Entries
- Is Mandatory
- Default Value of * – We will use this in the filter string to return all values when * is selected.
- List of Values from table
3. We will map the input parameter to the table function input variable
4. We define the table function script as follows:
FUNCTION "JHAUN"."xxx_sandbox.poc3::TF_JH_TEST" ( V_MATERIAL_GROUP NVARCHAR(1000), V_MATERIAL_TYPE NVARCHAR(1000) )
RETURNS TABLE (
MATERIAL NVARCHAR(18),
BASE_UOM NVARCHAR(3),
MATERIAL_GROUP NVARCHAR(9),
MATERIAL_TYPE NVARCHAR(4),
WEIGHT_UOM NVARCHAR(2),
NET_WEIGHT Decimal(17,3),
PRODUCT_HIERARCHY NVARCHAR(18),
MATERIAL_STATUS NVARCHAR(5),
FILTER_STRING NVARCHAR(10000)
)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER AS
BEGIN
DECLARE MY_FILTER varchar(10000) := '(''*'' in (' || :V_MATERIAL_GROUP || ') or MATKL in (' || :V_MATERIAL_GROUP || ')) and ( ''*'' in (' || :V_MATERIAL_TYPE || ') or MTART in (' || :V_MATERIAL_TYPE || '))';
BWTABLE = APPLY_FILTER( SAPHANADB."/BIC/AY_MATRIAL2", :MY_FILTER );
RETURN
SELECT
MATNR AS MATERIAL,
MEINS AS BASE_UOM,
CASe WHEN MATKL = '' THEN '00' ELSE MATKL END AS MATERIAL_GROUP,
MTART AS MATERIAL_TYPE,
'LB' AS WEIGHT_UOM,
NTGEW AS NET_WEIGHT,
PRDHA AS PRODUCT_HIERARCHY,
MSTAE AS MATERIAL_STATUS,
'MATKL in (' || :V_MATERIAL_GROUP || ') and MTART in (' || :V_MATERIAL_TYPE || ')' AS FILTER_STRING
FROM :BWTABLE
UNION ALL
SELECT
null AS MATERIAL,
null AS BASE_UOM,
null AS MATERIAL_GROUP,
null AS MATERIAL_TYPE,
null AS WEIGHT_UOM,
null AS NET_WEIGHT,
null AS PRODUCT_HIERARCHY,
null AS MATERIAL_STATUS,
'MATKL in (' || :V_MATERIAL_GROUP || ') and MTART in (' || :V_MATERIAL_TYPE || ')' AS FILTER_STRING
FROM DUMMY;
END;
4. There are several items in this example table function that are included for troubleshooting purposes. However, the core of the solution centers around the “DECLARE MY_FILTER varchar(10000)” and the “BWTABLE = APPLY_FILTER( SAPHANADB.”/BIC/AY_MATRIAL2″, :MY_FILTER )” sections before the RETURNS clause.
DECLARE MY_FILTER is a variable we use to craft the filter clause string from the two input variables. Once compiled, the string would be (for example) (‘*’ in (‘T01,’T02’) or MATKL in (‘T01,’T02’)) and ( ‘*’ in (‘ZPCF’,’TERT’) or MTART in (‘ZPCF’,’TERT’)) . This is very similar to how SQL based Analytics Privileges work in situations where we directly define the filter clause. The difference here is that we dynamically craft the string based on what calculation view input parameter are passed.
BWTABLE = APPLY_FILTER section is used to set a table variable equal to the APPLY_FILTER function. In the function we specify the table where we want to apply the filter and also the filter condition string.
In the RETURNS SQL, we SELECT FROM the table variable :BWTABLE and not the actual table in the database. For added value in troubleshooting, I added a string constant to output an example of the filter conditions and a UNION from dummy to always return an example of the filter conditions even if no matching data was found. These were only added for troubleshooting purposes and would not be needed in a production grade table function.
This is just a basic example but it demonstrates how you can pass multiple values from a calculation view into a filter condition in a table function. Their are other methods documented that can be found on the internet. For example, you can use another table function to convert the string array into a results table. You can then use this table function and it’s results table in a WHERE clause sub-select. However, based on my plan visualization testing, the APPLY_FILTER method out performs the WHERE clause sub-select method rather significantly. The APPLY_FILTER actual gets applied at the lowest levels when accessing the referenced table. The WHERE clause sub-select method seams not apply the record filtering until near the end of the query execution. Hopefully this will help you get started on creating your own version.
SAP BI Blog 