Skip to Content
0
May 30, 2018 at 01:21 PM

SAP HANA Multiple parameter in calculation Views via OLAP BEx variable

648 Views

Hi All

I saw a lot postings about HANA calculation views, so I’m hoping you might have an answer.

I followed a SAP note 2315085 so far, saying multiple Input params won’t work.

Our need it to pass multiple document numbers from OPAL variable via HCPR to the underlying SQL Script calc view.

I already created the function referenced in SAP note to parse the colon separated values to an internal table to be used in script view

Code:

CREATEFUNCTION"MY_OBJECTS"."9CA_VAR_INLIST"(str_input nvarchar(5000), delimiter nvarchar(10)) RETURNStable ( VAR_LIST NVARCHAR(10) )

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

/********* Begin Function Script ************/

BEGIN

DECLARE cnt int;

DECLARE temp_input nvarchar(128);

DECLARE slice NVARCHAR(10) ARRAY;

temp_input = :str_input;

cnt = 1;

WHILElength(temp_input) > 0 DO

if instr(temp_input, delimiter) > 0 then

slice[:cnt] = substr_before(temp_input,delimiter);

temp_input = substr_after(temp_input,delimiter);

cnt = :cnt + 1;

else

slice[:cnt] = temp_input;

break;

endif;

ENDWHILE;

tab2 = UNNEST(:slice) AS (VAR_LIST);

returnselect VAR_LIST from :tab2;

END;

Testing the function in sql console by dropping e.g. 2 values the Output is fine…all is as expected ( see figure below)

But

Running the same test on calculation view by 2 params

Will not return any value.I added the code just as described by SAP but no result comes back

Any Suggestion that might help me?

The Major Problem is that actual this Bypass is suggested caused by BEx variables being able to pass multiple single values to HANA Calculation views