Cascading Filter in SAP Analytical Desiner

[abhimanyu_sharma]

Hi Experts,

I have added two widgets (Dropdown filter ) in my Analytical Application say - Customer Type and Customer ID.

My requirement is when I select Customer type say "ABC" then all the customer ID related to "Customer type = ABC" should only be visible to me in the dropdown filter - Custonmer ID. Please let me know how to achieve it.

Below is the current implementation done by me but i am not able to do cascading effect.

Currently I have initialized both the dropdown filters in onInialization() method of Canvas to fill it with master data available in backend.

onInitailzation() Method : To fill dropdown filters with master data

For Customer Type:

var i=0;

var customerType = Chart_1.getDataSource().getMembers("Customer_Type");

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

dropdown1.addItem(customerType[i].id,customerType[i].description);

}

For Customer ID:

var j=0;

var customerID = Chart_1.getDataSource().getMembers("Customer_ID");

for (j=0;j< customerID.length; j++) {

dropdown2.addItem(customerID[i].id,customerID[j].description);

}

And then I added a code in dropdown filter onSelect() method to pass the selected key by the user to the global variable VAR_CUSTOMER_TYPE and VAR_CUSTOMER_ID.

onSelect() method Dropdown 1 and Dropdown 2:

VAR_CUSTOMER_ID = dropdown_1.getSelectedKey();

VAR_CUSTOMER_TYPE = dropdown_2.getSelectedKey();

And in the last , I created a button - Apply Filter and on click of that applying filters to all charts :

Chart_1.getDataSource().setDimensionFilter("dropdown_1",VAR_CUSTOMER_ID);

Chart_1.getDataSource().setDimensionFilter("dropdown_2",VAR_CUSTOMER_TYPE);

Chart_2.getDataSource().setDimensionFilter("dropdown_1",VAR_CUSTOMER_ID);

Chart_2.getDataSource().setDimensionFilter("dropdown_2",VAR_CUSTOMER_TYPE);

[bosegsap]

Hi Abhi

I am facing similar issue, could you share your knowledge in fixing this issue

Regards

Bose

[wilson_yeung]

Hi Abhimanyu,

I've run into this problem before. Unfortunately, you cannot use the standard filter functionality and use some variation of getMembers to get the result set's members. The getMembers API takes the master data entries, instead of your filtered result set. You'll need to iteratively check to see if the selected Customer Type and every Customer ID is a valid entry in your dataset.

Assuming dropdown1 is the dropdown for Customer Type

Assuming dropdown2 is the dropdown for Customer ID

Assuming table1 is a table that stores your customer data which you'll keep for the purposes of populating the cascading dropdowns

You need to place this script in dropdown1's onSelect script:

//Initialize
var customer_type = dropdown1.getSelectedKey();
var customer_id_list = table1.getDataSource().getMembers("Customer_ID");
dropdown2.removeAllItems();
dropdown2.addItem("All","All"); //Assuming you want an "All" customer ID entry
var i = 0;

//Initialize data cell to check validity on. Measure name,initial value of Customer_Type and Customer_ID is arbitrary
var cell = table1.getDataSource().getData({"Customer_Type": customer_type, "Customer_ID" : "", "Account":"[Account].[parentId].&[MEASURE_1]"});

//Populating Customer_ID dropdown
for (i=0; i < customer_id_list.length; i++){
    cell = table1.getDataSource().getData({"Customer_Type": customer_type, "Customer_ID" : customer_id_list[i].id, "Account":"[Account].[parentId].&[MEASURE_1]"});
    if (cell){ //If this cell exists, then it's a valid combination of customer type and customer id, so add entry
        dropdown2.addItem(customer_id_list[i].id, customer_id_list[i].description);
    }   
}

Hope this helps!

[former_member671721]

Hi Abhimanyu

You need to create to put the following code in OnSelect() for the dropdown_1 (customer Type)

and use the getResultSet() method which returns an array

try the following code

onSelect() method Dropdown 1 :

VAR _CUSTOMER_TYPE = Dropdown_1.getSelectedKey();
var _Customers = Chart_1.getDataSource().getResultSet({"Customer_Type": _CUSTOMER_TYPE});
// Customer_Type is the field or column name as per the model

// Use the console.log to have an idea about the how the _Customers Array looks like
console.log(_Customers );

// Now Populate customer drop down
var i = 0;for(i=0; i<_Customers.length; i++) { 
Dropdown_2.addItem(_Customers[i].Customer_ID.id, _Customers[i].Customer_ID.description);
}

This will get the list of customers according to selected Customer_Type

Then you can do the filters

Generally I create a table, bind it to the model, say Table_Customers, I will hide it and use it to get the datasource and do the filtering and so on

[IngoH]

Hello Abhimanyu

I suggest you look at the online documentation.

Regards

Ingo Hilgefort

[abhimanyu_sharma]

Hi, I have checked but couldn’t find much on that.

If u could help me on this , that would be great help

[IngoH]

Hello Abhimanyu

I do see that you apply the filter to the charts, but I don't see any script / logic where you change the values from the dropdown lists after a values has been selected.

seems that part is missing.

Regards

Ingo Hilgefort, SAP

[abhimanyu_sharma]

For that I need help to apply cascading effect.

Could you please help me in getting that please.