Skip to Content
Technical Articles
Author's profile photo Noel Munday

SAP Analytics Cloud : Dynamic account filtering based on a data mapping table

Use case

A common requirement in planning scenarios is to limit the available Cost Elements or GL Account numbers that a Cost Center manager is allowed to plan on. In other words the organisational structure determines the line items (rows) of the input planning table.

Validation rules can 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 in input data.

The input data allows the end user to maintain this relationship by setting a measure used as a boolean “flag” i.e. a one (“1”) or a zero (“0”) indicating if it can be planned on or not respectively.

The solution is dynamic because the code which sets the filter for planning can be attached to any event and in my example it is attached to the select event of the input table. In other words the user selects the cost center in the header filter area and as soon as they move the focus to the input table in order to capture data the code will be executed automatically and the accounts filtered. Of course the code could be attached to any other event, such as a button, but then the dynamic/automatic nature of the solution will be lost.

 

Solution

Overall concept

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

End%20result%20of%20solution

In our planning story we select a VERSION and COST CENTER to be planned on and the rows dynamically populate with the GL ACCOUNTS that may be planned on by that particular Cost Center. In scenario 1 above Cost Center = “CC01” is selected and GL Accounts = “GL01” and “GL02” are returned.

Now, looking at scenario (2), when we select COST CENTER = “CC02” a different set of GL ACCOUNTS are returned. It might have overlapping accounts (e.g. “GL02”) or a completely different list of accounts.

The mapping of this relationship is stored in data by maintaining a COST CENTER and GL ACCOUNT relationship model which is VERSION dependent. 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.

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 (this is not a requirement for the solution to work) but it’s worth noting that there is no pre-populated data in the model to display the relevant combinations and avoids any mass input and delete data actions.¬†For this solution to work it is not necessary to copy or set any data in the planning model since the principle that is followed is to display all UNBOOKED master data and then to set a filter on that dimension.

Analytic application

 

In this example the canvas is setup as follows:

  1. Filter bar containing VERSION and COST CENTER – Single value user selection
  2. Input table using input model as a data source
  3. 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.

 

 

I’ve chosen to add the code to the ONSELECT event of the input table.

The JavaScript performs the following steps:

  1. Obtain the Cost Center filter value from the input table and store it in a filter value variable
  2. Since the filter is setup as a user selectable single value selection it is not necessary to use the SWITCH and TYPE statements to determine the nature of the filter. The array can be converted directly to a SINGLE VALUE FILTER variable.
  3. Obtain the full list of GL Accounts from the mapping table into a variable of memberinfo array
  4. Initialise a string array which will contain the values to use to set the filter of the input table
  5. Loop through the complete list of GL Accounts in the mapping table and where the data cell value is set to one (1) add the GL Account ID to the string array list and update the index number (iCnt)
  6. The last step is to set the GL Account filter of the input table to the list of GL Accounts determined from the mapping table
// Obtain the Cost Center filter value from the input table
vCC = Table_1.getDataSource().getDimensionFilters("CCPUB");

// Convert the filter array into a single filter value and obtain the value of the selection
sfvCC = cast(Type.SingleFilterValue,vCC[0]);
sCC = sfvCC.value;

// Return the full list of available GL Accounts from the mapping table
GL_List = Table_2.getDataSource().getMembers("GLPUB");

// Initialise the GL filter list that will be used to set the filter of the input table
iCnt = 0;
GL_Filter_List = [""];

// Loop through the available GL Account list and where the data cell value is equal to one (1) add the account to the resultant list
for (var i=0; i < GL_List.length; i++) {	
    GL_Selection = {"GLPUB": GL_List[i].id , "CCPUB" : sCC};

	if (Table_2.getDataSource().getData(GL_Selection).rawValue === "1") 
	{
	   GL_Filter_List[iCnt] = GL_List[i].id;
	   iCnt++;
	}
}

// Apply the GL filter list to the input table
Table_1.getDataSource().setDimensionFilter("GLPUB", GL_Filter_List);

 

Script variables used:

Variable Type Set as an array
vCC FilterValue Yes
sfvCC SingleFilterValue No
sCC String No
GL_List MemberInfo Yes
GL_Selection Selection No
GL_Filter_List String Yes
iCnt Integer No

Result

The end result is that the user is able to maintain the mapping table on the right (2) as a manual input table and then whichever cost center is selected in the filter (1) will determine which accounts are filtered in the input table on the left (3).

 

 

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.