Skip to Content

Problem description: you use HANA calculation view with multiple input parameters (for more details see http://scn.sap.com/community/hana-in-memory/blog/2013/12/30/using-multiple-values-in-input-parameter-for-filtering-in-graphical-calculation-view) in SAP Design Studio and you need to generate appropriate input parameters values based on user choice.

  • We have two calculation views as data sources: master data view and transactional view. User chooses values from master data view and then applies complex filters on transactional data source. Filters should recalculate available master data values depending on chosen values.
  • For example master data view contains organization structure data. First we should join master data tables in one analytical view:Anlt.png
  • Then include analytical view in a graphical calculation view projection: Clc.png
  • Create 3 pairs of input parameters. I_<DIM_NAME>_VAL_DIC is for chosen values: IP1.png
  • I_<DIM_NAME>_EXC_DIC is include/exclude/all_values flag:

IP2.png

I_<DIM_NAME>_EXC_DIC = 0 – include

I_<DIM_NAME>_VAL_DIC = 1 – exclude

I_<DIM_NAME>_VAL_DIC = 2 – all_values

  • Apply the next filter in the projection node:

((in(“PLANT”,‘$$I_PLANT_VAL_DIC$$’) AND ($$I_PLANT_EXC_DIC$$ = 0)) OR (not in(“PLANT”,‘$$I_PLANT_VAL_DIC$$’) AND ($$I_PLANT_EXC_DIC$$ = 1)) OR ($$I_PLANT_EXC_DIC$$ = 2)) AND ((in(“SALES_OFF”,‘$$I_SALES_OFF_VAL_DIC$$’) AND ($$I_SALES_OFF_EXC_DIC$$ = 0)) OR (not in(“SALES_OFF”,‘$$I_SALES_OFF_VAL_DIC$$’) AND ($$I_SALES_OFF_EXC_DIC$$ = 1)) OR ($$I_SALES_OFF_EXC_DIC$$ = 2)) AND ((in(“SALES_GRP”,‘$$I_SALES_GRP_VAL_DIC$$’) AND ($$I_SALES_GRP_EXC_DIC$$ = 0)) OR (not in(“SALES_GRP”,‘$$I_SALES_GRP_VAL_DIC$$’) AND ($$I_SALES_GRP_EXC_DIC$$ = 1)) OR ($$I_SALES_GRP_EXC_DIC$$ = 2))


  • Move on to Design Studio and add the next components to a report:DScomp.png
  • FILTERPANEL_MD properties:

             DSprop.PNG

  • Function ORG_STRUCTURE_FILTER.REFRESH_PLANT_DIC():

     GLOBAL_SCRIPTS.APPLY_VARIABLE_PAIR(DS_MD, “SALES_OFF”, “I_SALES_OFF_VAL_DIC”, “I_SALES_OFF_EXC_DIC”);

     GLOBAL_SCRIPTS.APPLY_VARIABLE_PAIR(DS_MD, “SALES_GRP”, “I_SALES_GRP_VAL_DIC”, “I_SALES_GRP_EXC_DIC”);

     GLOBAL_SCRIPTS.APPLY_VARIABLE_PAIR(DS_MD, “PLANT”, “I_PLANT_VAL_DIC”, “I_PLANT_EXC_DIC”);


  • Function GLOBAL_SCRIPTS.APPLY_VARIABLE_PAIR(DS, DIM, VAR_MAIN, VAR_SPRT):

     var fltrstr = DS.getFilterExt(DIM);//Get selected values

     var var_marray = fltrstr.split(“; “);//Spliting

     var var_mstr = ;//Val variable

     var var_sstr = ;//Exc variable

     var_marray.forEach(function(element, index) {

            if (element.substring(0,1) == “!”) {

             //Exlusion

             var_mstr = var_mstr + element.substring(1) + “”,””;

             var_sstr = ‘1’;

            

       } else {

             //Inclusion

             var_mstr = var_mstr + element + “”,””;

             var_sstr = ‘0’;

       }

      

     });

     var_mstr = var_mstr.substring(0,var_mstr.length-5); //Cut extra “”,””

     //All values

     if (var_mstr == ) {

       var_mstr = ‘*’;

       var_sstr = ‘2’;

     }

     //Update available filter values

     DS.setVariableValueExt(VAR_MAIN, var_mstr);

     DS.setVariableValueExt(VAR_SPRT, var_sstr);


  • BUTTON_CALC – TRANSAC_DATA.APPLY_FILTERS():

     //Calculate transactional data

     APPLICATION.setVariableValueExt(“I_SALES_OFF_VAL”, DS_MD.getVariableValueExt(“I_SALES_OFF_VAL_DIC”));

     APPLICATION.setVariableValueExt(“I_SALES_OFF_EXC”, DS_MD.getVariableValueExt(“I_SALES_OFF_EXC_DIC”));

     APPLICATION.setVariableValueExt(“I_SALES_GRP_VAL”, DS_MD.getVariableValueExt(“I_SALES_GRP_VAL_DIC”));

     APPLICATION.setVariableValueExt(“I_SALES_GRP_EXC”, DS_MD.getVariableValueExt(“I_SALES_GRP_EXC_DIC”));

     APPLICATION.setVariableValueExt(“I_PLANT_VAL”, DS_MD.getVariableValueExt(“I_PLANT_VAL_DIC”));

     APPLICATION.setVariableValueExt(“I_PLANT_EXC”, DS_MD.getVariableValueExt(“I_PLANT_EXC_DIC”));

  • Result:

     Apply Sales Office filter:

DS1.png

     And move to Sales group:

DS2.png

     There are only corresponding Sales groups:

DS3.png

     Also we can exclude Sales Office Values:

DS4.png

DS5.png

     Sales groups corresponding values:

DS6.pngDS7.png

     Example of generated SQL:


     SELECT DISTINCT “PLANT”, “PLANT_TXT” FROM “SCHEMA”.”MD_VIEW_NAME”  ORDER BY “PLANT”  ASC LIMIT 1001 WITH PARAMETERS      (‘PLACEHOLDER’ = (‘$$I_ZCTYPBUS_VAL_DIC$$’,’*’), ‘PLACEHOLDER’ = (‘$$I_ZCTYPBUS_EXC_DIC$$’,’2′), ‘PLACEHOLDER’ =      (‘$$I_SALES_OFF_VAL_DIC$$’,’10”,”40′), ‘PLACEHOLDER’ = (‘$$I_SALES_OFF_EXC_DIC$$’,’1′), ‘PLACEHOLDER’ =      (‘$$I_SALES_GRP_VAL_DIC$$’,’201”,”204”,”503′), ‘PLACEHOLDER’ = (‘$$I_SALES_GRP_EXC_DIC$$’,’0′), ‘PLACEHOLDER’ =      (‘$$I_PLANT_VAL_DIC$$’,’*’), ‘PLACEHOLDER’ = (‘$$I_PLANT_EXC_DIC$$’,’2′))


After implementation of all Master Data filters we can calculate transactional data using TRANSAC_DATA.APPLY_FILTERS() script:DS8.png


To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply