Cascading filter based on properties and hierarchy

[sebassmit]

Hi there! I am trying to design a template for Financial Planning and was wondering whether the following case is possible in a story or the analytics designer. The goal is to select a Profit Center from the hierarchy (could be at any level) and based on this selection give all associated Cost Centers. Cost Centers are linked to profit centers with a property in the dimension. As we are planning we, like to switch on unbooked data and see all combinations.

Design
The design should be something like, where the PC and CC dropdown gets populated automatically based on the information from the dimensions.

Functionality
From the PC dropdown it should be possible to select a Profit Center at any level however, the cascading CC dropdown should only show the associated Cost Centers based on its property. For example,

- Selecting PC 1 should show all CC
- Selecting PC 12 should show CC 121 and CC 122
- Selecting PC 11 should show CC 111 and CC 112

Do you know any blogs that showcase this scenario? My biggest issue is so far is cascading the CC dropdown when you do not select a profit center at the lowest level in its hierarchy. This is because only the lowest level is recorded in the property of a Cost Center.

[shraddha_mundwadkar]

Hi,

It is possible to achieve same in Analytic Designer. Please do below.

Table_1 should have Profit center hierarchy in column 1 and column 2 should be cost center

The cost centers will show in column 2 against the level of PC where they are maintained.

Oninitialization logic:

var x = Table_1.getDataSource().getMembers("Proft center")

for ( var y=0 ;y<x.length;y++)

{Dropdown_1.addItem(x[y].id,x[y].description); } "This will populate list of values for profit centers from the Table_1 data

*Note if you want to prepopulate cost centers list also on initialisation then above same logic should be copied but for dimension "Cost Center" and additem for Dropdown_1.

Logic in Dropdown_1.

var dd1 = Dropdown_1.getSelectedKey(); "it will read the selected profit center from Dropdown_1

Table_1.getDataSource().setDimensionFilter("Dimension_Name",dd1);

var n = Table_1.getDataSource().getResultSet();

*Note you will have to restrict the dropdown_2 list of values in Dropdown_1 logic as we have restricted the table data on selected profit center the result set from table 1 will give you only the cost centers associated with the profit center this should work even if you select any higher level profit center nodes. variable n will have only the required cost centers

for ( var y=0 ;y<b.length;y++)

{Dropdown_2.addItem(n[y].[“Cost Center”].id; n[y].[“Cost Center”].description);}

Logic for Dropdown_2

var dd2 = Dropdown_1.getSelectedKey(); "This will restrict the table on the selected cost center from drop down 2

Table_1.getDataSource().setDimensionFilter("Cost Center",dd2);

Viewers / Readers : If you find shared info helpful, feel free vote (arrows on left side) for it.

Thanks,

Shraddha

[N1kh1l]

Shraddha,

Cost center is not in a parent child relation ship with Profit center. I guess profit center is maintained as a property of Cost center. The challenge is that only base level PC would be maintained as properties in CC. If top level PC or sub node level PC is selected it would require the CC drop down to populate with all CC or those CC with property value of leaf PC rolling up to that node PC.

@Sebastin

Below Approach should work. The code can be tweaked based on actual requirement logic.

var ds = Table_1.getDataSource(); //Table_1 is tour table with data

var sel = Dropdown_PC.getSelectedKey(); //PC Dropdown.

var pc_level =ds.getHierarchyLevel(dimension: string|DimensionInfo) // replace with your pc dimension

This will return a integer value like 1, 2 ,3 etc based on PC selected and its level in PC dimension.

Use IF logic on pc_level to read CC members (either all or filtered on pc property by var sel) using below code

var cc_list=ds.getMembers("Dimension_name",{
	limit:20000,
	accessMode:MemberAccessMode.BookedValues});

Finally add members of cc list to CC dropdown.

for (var i = 0; i < cc_list.length; i++) {

Dropdown_CC.addItem(cc_list[i].id, cc_list[i].description);

}

Finally use below to filter your table.

Table_1.getDataSource().setDimensionFilter(dimensionId, memberId);

Regards

Nikhil

[sebassmit]

Thank you both for responding!

I provide my solution which uses a recursive algorithm to find the lowest level in the hierarchy based on the input. This is possible by setting up some global variables which can be used across scripts. By design, it was possible to let the recursion end if a given profit center started with a P.

var checkPC = parentPC;

for (var j=0; j<profitCenters.length; j ++) {
	if (profitCenters[j].hierarchies.Hierarchy.parentId === checkPC) {
		if (profitCenters[j].id.charAt(0) === "P") {
			showedPCs.push(profitCenters[j].id);
		} else {	
			parentPC = profitCenters[j].id;
			ScriptFunctions.findLeafMembers();
		}
	}
}

Final issue that I am seeing wrong combinations since only filters are set to the correct value (red outlined are valid):

And we would like to obtain: