Skip to Content
Author's profile photo Ashutosh Rastogi

Multiple values in Input Parameter for HANA calculation view

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

 

Solution

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.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo ANIRUDDHA SHINDE
      ANIRUDDHA SHINDE

      If SCALAR FUNCTION can be used  as INPUT PARAMETER IN CV, WHY WAS NEED TO CREATE PROCEDURE AS ONE MORE OBJECT?

      Author's profile photo Ashutosh Rastogi
      Ashutosh Rastogi
      Blog Post Author

      I had issues in eclipse to use the scalar function. So had to workaround it. This may be related to the eclipse version I was using.

      Ashutosh

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Nice to see someone blogging about how to write code for HANA!

      While I'm not a 100% sure that I get why this whole thing is necessary, I'm pretty sure that we can remove the whole SF_NEXT_PERIOD function simply with

      select to_varchar(
                   add_months(  
                    to_date('122018', 'MMYYYY') , 1)
                  , 'MMYYYY')
      from dummy;
      
       

      The result of this call is - correctly - '012019'.

      Obviously, you can change the actual value for the date and also the number of periods to add.

      Even better, why not get rid of all the looping altogether and use the SAP HANA built-in auxiliary table for all sorts of time calculations: _SYS_BI.M_TIME_DIMENSION.

      You can, of course, build your own aux. table but dealing with generating code all the time seems not the best option in terms of runtime efficiency and code maintenance.

      Once the table is pre-populated (see generate time data in the reference manual) you could run a single SELECT like this to get your in between periods in that funny format MONTH-YEAR:

      select string_agg(SF_PERIOD, ', ' ) as PERIODS_BETWEEN
      FROM (
      	select  
      	      distinct (MONTH || YEAR) as SF_PERIOD
      	from "_SYS_BI"."M_TIME_DIMENSION"
      	where calmonth  between (right('082017',4)||left('082017',2))
      	                    and (right('102017',4)||left('102017',2))
      	   );
      PERIODS_BETWEEN 
      082017, 092017, 102017

      The complicated bit here really is to swap the YEAR and MONTH data around so it matches the SAP notation for CALMONTH (YYYYMM).

      This logic can obviously be simply put into your PR_PERIODS code so that you can feed the result back to the parameters of the other calculation views.

      Author's profile photo Ashutosh Rastogi
      Ashutosh Rastogi
      Blog Post Author

      Hi Lars,

      Thank you for time and effort to provide the feedback. This was actually my first attempt coding on SAP HANA, what you see above was limited by my knowhow on the topic.

      Your inputs are really helpful. I will use your suggestions to imrpove/optimize the code here.

      Regards,

      Ashutosh

      Author's profile photo Ravi Madari
      Ravi Madari

      Hello Ashutosh,

       

      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.

       

      Thanks

      Ravi.