How to create dynamic filters when using SAP HANA calculation view with multiple values input parameters in the backend

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:
• Then include analytical view in a graphical calculation view projection:
• Create 3 pairs of input parameters. I_<DIM_NAME>_VAL_DIC is for chosen values:
• I_<DIM_NAME>_EXC_DIC is include/exclude/all_values flag:

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:
• FILTERPANEL_MD properties:

• 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:

     And move to Sales group:

     There are only corresponding Sales groups:

     Also we can exclude Sales Office Values:

     Sales groups corresponding values:

     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: