Dynamic filter / User Exit Variable for HANA Views

[anindya_bose]

Recently I saw some posts in SCN where people asked "how can we restrict data HANA views dynamically?" Like showing data for "Last 3 months" , "Last 13 Months" or "Last two years" etc . So, thought of listing the steps in a small blog post.

 

Requirement : Based on user input or current month , we want to restrict data in our view.  Common requirement is to show last 12/13 months data dynamically. In general , we want to go 'n' number of months back from current month or whatever month user would select.

 

Solution :  From  SPS09 , HANA allows Input Parameter of type   "Derived From Procedure /Scalar Function" - and I would use this to meet this requirement. Good thing about this solution is , we can restrict the data at the first projection node and avoid bringing unnecessary data to upper nodes.  We shall write a procedure which would take month input from user ( or current month if nothing supplied)  and return  "to" and "from" month .

 

This procedure takes the input of  "desired month" and  "number of months" user wants to go back.   Default value for "desired month " is Current Month and that for "look back month" is 12 .

 

Example in Fig 1, give me 5 months range from 2016/01 i.e 2015/08 to 2016/01  . We shall see procedure coding later. Same procedure can be used for any number of look back month .



Fig 1 - Procedure Call .  Here my "from month" is 2015/08 and "to month" is 2016/01

 

Detail Steps :

 

Step 1) Create a procedure with Input and output type as string ( must) . It can have only one scalar  output parameter . By this time you probably guessed why I have "to" and "from" month in same field separated by a hyphen .

 

Step 2) Create two Input Parameters , one type  "Direct" and another "Derived From Procedure/Scalar Function " in your Calculation / Analytic View .



Fig 2 : Input Parameter type Direct

 



Fig 3 : Input Parameter type "Derived from Procedure/Scalar Function"

 

Step 3) We need to map one input parameter with another to receive user input and return calculated month range. Click on "Manage Mapping". This will pass user selection of month and number of months we want to go back .  For this model, I have selected  2 ( constant ) months back from user month/current month ; based on the requirement we can ask user to select number of month he/she wants to go back .  For that we need create another Input Parameter type 'Direct' .



Fig 4 : Mapping of Input Parameter to pass user selection to Procedure .

 

4) Now , we will use Input Parameter IP_MONTH to restrict data in projection node. Right click on the "Expression" under "Filter" folder and click "Open" .

   

Fig 5 : Filtering Expression

 

I am using "leftstr" and "rightstr" operators to take only relevant portion from the output of procedure, i.e first 6 character and last 6 character respectively

 

Output of Procedure  201604-201606

( Leftstr (201604-201606),6 )   =  201604  and (  Rightstr ( 201604-201606) ,6 ) = 201606 .   My Calmonth would be between 201604 and 201606 .

 

Let's see at what level Filters are getting applied  from PlanViz --  In total I am getting 703 records as per plan viz , we will check what is the total number of records in base table based on our selection from SAP end .

      

This procedure can be reused for any number of look back months. Either we need select a different constant value or ask user to select number of months he wants to go back .

 

 

Detail of Procedure :

 

We need to go to HANA Development perspective to create this procedure .

 

Open  "Repository View" ----> Right click on your Package -----> Select "New"

 

Choose "Others" ----> SAP HANA ----> Database Development ---->Stored Procedure   . Provide Procedure Name and Target Schema. It would automatically take .hdbprocedure extension .  Put the attached code , save and activate the procedure .   Once activated successfully, test the procedure from SQL prompt using "call <Procedure> " statement .  . If everything fine, follow from step 2.


*****Due to SCN Migration, attachment got missing , procedure code has been updated here *******

PROCEDURE "_SYS_BIC"."<Package_Hierarchy>::ZPROC_DYN_MONTH" (IN NUM_MON NVARCHAR(2) ,IP_CALMON NVARCHAR(6) , OUT FROM_TO NVARCHAR(13)) 

	LANGUAGE SQLSCRIPT

	SQL SECURITY INVOKER 

	DEFAULT SCHEMA "_SYS_BIC"

	READS SQL DATA AS

BEGIN

/***************************** 

	Write your procedure logic 

 *****************************/

 DECLARE v_num_mon INT ;

 

 IF :NUM_MON = '' OR :NUM_MON IS NULL 

 THEN v_num_mon := 12;

 ELSE v_num_mon := TO_INT(:NUM_MON) ;

 END IF ;



 

IF :IP_CALMON = '000000' OR :IP_CALMON = '' OR :IP_CALMON IS NULL 



THEN FROM_TO :=  left (  to_char ( to_dats(  ADD_MONTHS (TO_DATS(CURRENT_DATE), - v_num_mon))),6)|| '-' || left( to_char( to_dats( current_date)),6) ;



ELSE FROM_TO :=  left (  to_char ( to_dats(  ADD_MONTHS (TO_DATS(:IP_CALMON), - v_num_mon))),6) || '-' || left( to_char( to_dats(:IP_CALMON)),6) ;



END IF ;

END;

PS:  In case you want, you can use two different procedure to return "To" and "From" values of the month selection . In that case, you would not need to use leftstr or rightstr operators.