Friday 9 December 2016

Dynamic filter / User Exit Variable for HANA Views

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 .

Dynamic filter / User Exit Variable for HANA Views

Fig 1 - Procedure Call .

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 .

Dynamic filter / User Exit Variable for HANA Views

Fig 2: Input Parameter type Direct

Dynamic filter / User Exit Variable for HANA Views

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' .

Dynamic filter / User Exit Variable for HANA Views

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

Step 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" .

Dynamic filter / User Exit Variable for HANA Views

Dynamic filter / User Exit Variable for HANA Views

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 .

Dynamic filter / User Exit Variable for HANA Views

Dynamic filter / User Exit Variable for HANA Views

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 **

Write your procedure logic 
 DECLARE v_num_mon INT ;

 THEN v_num_mon := 12;
 ELSE v_num_mon := TO_INT(:NUM_MON) ;


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) ;


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. 


No comments:

Post a Comment