SAP Analytics Cloud: Entity selection in header automatically determines line items selected in rows

[Noel_Munday]

Use case

Specific COST CENTERS plan on specific GL ACCOUNTS otherwise every cost center would see a comprehensive list of all GL Accounts in their input planning table and this is not always desirable. How can we restrict the GL ACCOUNTS that are planned per COST CENTER in SAP Analytics Cloud planning?

To add a little more complexity to the requirement, the "cost center / account" mappings are VERSION and TIME specific and must be displayed on the account HIERARCHY and not in a flat format.

There may be other methods, such as the use of validation rules, which could be used to achieve this requirement but in this blog I will explain a method that uses an Analytic Application to filter the relevant GL Accounts based on a mapping table maintained by the end user i.e. the user captures a measure used as a flag (value = "1") against the "cost center + account" combination that is valid for planning. 

The solution is dynamic because the script which sets the account filter for planning in the rows of the table is attached to the select event of the input table. In other words the user will select the cost center in the header filter and as soon as they move the focus to the input table the script will be executed automatically and the accounts filtered.

Solution

Overall concept

The end result that we want to achieve is depicted in the following diagram:

In our planning story we select a COST CENTER (annotation 1) to be planned against and the rows are dynamically filtered with the GL ACCOUNTS (annotation 2) that may be planned on by that particular Cost Center.

Notice that the rows in the planning table are not pre-populated with data e.g. by copying a value to the row that may be planned and then hiding rows that do not contain data. In this solution the input table contains no data to start with as the rows are selected using UNBOOKED data and a FILTER.

Also notice that there are three versions in the columns (annotation 3) and each version relates to different time periods (YEAR in this scenario).  The account mapping is captured by VERSION and YEAR which means that the script logic which selects the relevant ACCOUNTS for the rows must combine the mapping table settings for each of these combinations before applying it to the filter of the input table.

The mapping of the "cost center / account" relationship is stored in data by maintaining a COST CENTER and GL ACCOUNT relationship model. The model can be consumed in a story so that an end user can maintain the mapping manually using a simple input table or the data could be prepared and imported into the model using one of the data acquisition methods.

Note: Although this example selects one cost center at a time in the header filter (annotation 1) in order to capture the mappings manually (annotation 2) there is no special script logic being executed at this stage of the solution which means any design or method of capturing the "1"'s will be acceptable e.g. a bulk upload of the mapping relationships using one of the data acquisition methods.

Model preparation for the example

The model which contains the mapping rules consists of the public dimensions for Cost Center (CCPUB) and GL Account (GLPUB) (annotation 2) and each has been filled with four demo dimension members. The measure is of integer type which will be used as a boolean flag. The mapping data is version specific (annotation 1) and any data which already exists in the model can be viewed in the modelling screen.

The planning model in our example has the same structure as the mapping model but this is not a requirement for the solution to work. Using the DATA FOUNDATION browser we can see that there is no data in the planning model.

Analytic application

In this example the canvas is setup as follows:

1. Filter bar containing COST CENTER - Single value user selection
2. Tabstrip with two tabs, one for capturing the planning data and one to maintain the mapping rules
3. Input table using input model as a data source
4. Mapping table using the mapping model as a data source. This interface would typically be added to a separate page or story and perhaps with separate user authorisations e.g. a system admin or super user would maintain the mapping table.

In my example I have attached the JavaScript to the ONSELECT event of the input table. This means the user will see the updated rows when they select the "Planning" tab on the TABSTRIP and select the input table itself.

The JavaScript performs the following steps:

// Obtain the Cost Center filter value from TABLE_2 where we intend capturing the plan values 
// We will apply it to the mapping table to obtain the list of GL accounts that need for the filter
CostCenter_FilterArray = Table_2.getDataSource().getDimensionFilters("CC");

// Convert the filter array into a single filter value
// There is no need to use the TYPE and SWITCH commands as we have setup the
// filter as a single value selection
CostCenter_Filter = cast(Type.SingleFilterValue,CostCenter_FilterArray[0]);
CostCenter = CostCenter_Filter.value;

// Return all accounts from the mapping table with data cells that are flagged for input i.e. accross all versions and periods 
Account_Selections = Table_1.getDataSource().getDataSelections();

// Initialise the GL filter list
Account_FilterList = [""];
iCnt = 0;

// Loop through the list of data cells and where the data cell value is equal to one (1) 
// or it is a hierarchy node (no account type), add the account to the resultant selection list to be used in the filter
// Note: This is where an account will be selected as soon as it is flagged in any VERSION + TIME combination because the loop is based on
// data cells and not on dimension members (master data)
   for (var i=0; i < Account_Selections.length; i++) {

// Return the account type - Hierarchy nodes do not have a type
	  GL_AccType = GL_Model.getMember("GLACC",Table_1.getDataSource().getMember("GLACC",Account_Selections[i].GLACC,"parentId").displayId).properties["accType"];
	   
// If the row is not a hierarchy node and the cell value is equal to 1
// then add it to the list but note that the same account may now be added
// multiple times so a 'clean up' of the list is required
// We also add the hiearchy node to which the account is attached to the
// filter list so that the hierarchy is also part of the filter
	  if ((GL_AccType !== '') && (Table_1.getDataSource().getData(Account_Selections[i]).rawValue === "1"))
		{
		  Account_FilterList[iCnt] = Account_Selections[i].GLACC;
	      iCnt++;
		  Account_FilterList[iCnt] = Table_1.getDataSource().getResultMember("GLACC",Account_Selections[i]).parentId;
		  iCnt++;
		}	   
}

			   
// Remove duplicate selections by first returning the list of GL accounts
// which are in the memberlist of the mapping table i.e. a unique list of accounts
// and then loop through this list i.e. visiting each account only once
// and if the account is found at least one time in the filter list
// add it to the 'cleansed' filter list. Now there will be no duplicate values
GL_Filter_List_Clean = [""];
iCnt = 0;

Accounts_MappingTable = Table_1.getDataSource().getMembers("GLACC");
	
for (i=0; i < Accounts_MappingTable.length; i++) {
	
	if (Account_FilterList.includes(Accounts_MappingTable[i].id)) 
	{
	   GL_Filter_List_Clean[iCnt] = Accounts_MappingTable[i].id;
	   iCnt++;
	}	
	
}

// Now comes the crux of the logic...when an INCLUSIVE FILTER list is provided to a table using an API for filtering
// then any hierarchy node that is included in the list will automatically display ALL
// the INDIVIDUAL account(s) in that node even if they have not been explicitly included
// This is a problem for our scenario because we would like to exclude GL accounts within a hiearchy node where other accounts in that node are included
// So the solution logic is as follows - up until this point we have constructed a clean unique list 
// of accounts and hiearchy nodes we want included in the filter. Now we will loop through this list 
// and construct a list of accounts which must be EXCLUDED and create an  EXCLUSION list
// Notice that we loop through all the accounts in the mapping table as before but this time
// when we find the account in the cleansed list we don't perform any action
// rather when we don't find it we construct the EXCLUSION list

iCnt = 0;
for (i=0; i < Accounts_MappingTable.length; i++) {
	
	if (GL_Filter_List_Clean.includes(Accounts_MappingTable[i].id)) 
	{}
		else {
		  GL_AccType = GL_Model.getMember("GLACC",Table_1.getDataSource().getMember("GLACC",Accounts_MappingTable[i].id,"parentId").displayId).properties["accType"];
		  if (GL_AccType !== '') {
		        AccList_FilterExcl_Clean[iCnt] = Accounts_MappingTable[i].id;
	            iCnt++;
			  }
		}
}

// We now instantiate our filter with the EXCLUSION LIST and apply it to the planning table

Filter_exclude = {values: AccList_FilterExcl_Clean, exclude: true };
Table_2.getDataSource().setDimensionFilter("GLACC", Filter_exclude);

Script variables used:

Variable	Type	Set as an array
CostCenter_FilterArray	FilterValue	yes
CostCenter_Filter	SingleFilterValue	no
CostCenter	string	no
Filter_exclude	MultipleFilterValue	no
AccList_FilterExcl_Clean	string	yes
Account_FilterList	string	yes
GL_Filter_List_Clean	string	yes
Account_Selections	Selection	yes
Accounts_MappingTable	MemberInfo	yes
GL_AccType	string	No
GL_Model	Mapping Model	Note: not found under "Script Variables" but under "Planning Models"
iCnt	Integer	No

Result

The end result is that the user is able to maintain the mapping table as a manual input table and then whichever cost center is selected in the header filter will determine which accounts are filtered in the input table.

 The columns which display asymmetrical member combinations e.g. VERSION = ACTUAL with YEAR = 2022, 2023 but VERSION = BUDGET with YEAR = 2024 and VERSION = FORECAST with YEAR = 2025 is achieved by using the ADVANCED FILTERING of the TABLE. This can be done by capturing the rules in the filter manually or by using the graphical display of the table and highlighting the columns you require to be hidden and then using the right-click context menu options and selecting "HIDE". There is no API to set this using JavaScript in this version.

[jegarciadiaz]

Hello,

I just replicated all from scratch and following all the steps and I am getting the following error when I click on the input table "Script execution failed for event "Table_1.onSelect", is there anything in the code that should be changed? I am using an Optimized Design Experience Story.

Update: I also tried with the classic Analytic Application and it showing the same error message.

Thanks,

Jesus.