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...) in SAP Design Studio and you need to generate appropriate input parameters values based on user choice.
I_<DIM_NAME>_EXC_DIC = 0 – include
I_<DIM_NAME>_VAL_DIC = 1 – exclude
I_<DIM_NAME>_VAL_DIC = 2 – all_values
((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))
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");
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);
//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"));
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
9 | |
7 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |