This blog covers an area for which I struggled sometime to actually achieve desired output. Hence, I planned to put forth the usecase and how I achieved it, so that it helps developers who face similar scenarios.
I was working on a HANA Calculation view that was consolidating data from over 8-9 other views. Each of them actually exposing multiple input parameters. Three of them were as follows
- Start Month (sample representation as ‘082017’)
- End Month (sample represenation as ‘102017’)
- Months in Between, Inclusive. (sample representation ‘082017, 092017, 102017’)
Now, in my calculation view, I actually wanted to do the following
- Dynamically calculate the values for “Months in Between” input parameter based on Start Month and End Month
- Supply the calculated multiple values to the “Months in Between” input parameter
First step was to actually calculate Next Period for a given period . I used a Scalar function in HANA to achieve that
FUNCTION "XYZ"."abc.mno::SF_NEXT_PERIOD" ( IN val VARCHAR(10)) RETURNS RESULT VARCHAR(10) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN DECLARE monthVal, yearVal, thisMonth, thisYear INT := 0; thisMonth := TO_INT(SUBSTRING(val,0,2)); thisYear := TO_INT(SUBSTRING(val,3,4)); IF :thisMonth = 12 THEN monthVal := 1; yearVal := :thisYear + 1; ELSE monthVal := :thisMonth + 1; yearVal := :thisYear; END IF; result := LPAD ((:monthVal||:yearVal), 6, '0'); END;
Second step was to actually create the list of periods in between for the given start month and end month. I again used a Scalar function in HANA to achieve that
FUNCTION "XYZ"."abc.mno::SF_PERIODS_IN_BETWEEN" ( IN startPeriod VARCHAR(10), IN endPeriod VARCHAR(10)) RETURNS RESULT VARCHAR(255) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN DECLARE temp VARCHAR(255); DECLARE val VARCHAR(10); DECLARE idx, totalMonth INT := 0; totalMonth := MONTHS_BETWEEN(TO_DATE(startPeriod,'MMYYYY'), TO_DATE(endPeriod,'MMYYYY')); temp := ''''; val := startPeriod; FOR idx IN 0..totalMonth DO IF :idx = 0 THEN IF :idx = :totalMonth THEN temp := ''''||:startPeriod||''''; ELSE temp := ''''||:startPeriod||''','''; END IF; ELSEIF :idx = totalMonth THEN temp := :temp||:endPeriod||''''; ELSE val := "XYZ"."abc.mno::SF_NEXT_PERIOD"(val); temp := :temp||:val||''','''; END IF; END FOR; result := :temp; END;
Third step was to actually call that function from the HANA calculation view. If you remember while creating Input parameters in HANA you have a option to supply input parameter values via a Procedure/Scalar function
While actually using that scalar function, I faced issues. So I actually did wrapped the scalar function within a HANA procedure and used the same in Input parameter definition.
HANA procedure looked like
PROCEDURE "_SYS_BIC"."abc.mno::PR_PERIODS" ( IN startPeriod VARCHAR(10), IN endPeriod VARCHAR(10), OUT val VARCHAR(255)) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER READS SQL DATA AS BEGIN SELECT "XYZ"."abc.mno::SF_PERIODS_IN_BETWEEN"(:startPeriod, :endPeriod) INTO val FROM Dummy; END;
And the same was then used in Input Parameter definition.
Next would be to actually “Manage Mappings”. See the above screenshot for the button at the bottom. This will allow you to supply the other input parameter values directly to the SAP HANA procedure as Input.
And that’s it. Hope this was of some help.