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 transaction data.

The transaction 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.

Overall concept

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

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 done in transaction 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 data in the model. 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);

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).