Populate table and chart based on dropdown selection

[pooja_elangovan01]

Hello Community,

I am currently working on an SAP SAC analytical application that involves a dimension called BPP, which has properties such as Brand, Category, Package, and more. I have a dropdown that includes specific properties of BPP, such as Brand and Category.

My goal is to dynamically populate the table and chart with brand members when a brand is selected in the dropdown. I am seeking an SAP SAC analytical application script for this query.

Thank you in advance for your help.

Regards,

Pooja.

[N1kh1l]

pooja.elangovan01

pooja.elangovan01If my understanding is right as per previous post then you can try below.

I am using my product dimension with 2 attributes BRAND and CATEGORY

sample below

Step1: Add Planning Model (the model involved)

Step 2: Add 2 Dropdowns

• DD_BRAND
• DD_CATEGORY

Step 3: Add Script object and rename it to Functions and the function get_attributes with return type void

Step 4: Add the below code to function get_attributes

This will read the unique values for attribute BRAND and CATEGORY and populate the dropdown

DD_BRAND.removeAllItems();
DD_CATEGORY.removeAllItems();
var brands = ArrayUtils.create(Type.string);
var category = ArrayUtils.create(Type.string);

var h1=PlanningModel_1.getMembers("SAP_CEP_PRODUCT",{limit:1000});
console.log(h1);

for(var i=0;i<h1.length; i++)
{
	
var currentbrand = h1[i].properties.BRAND;
var currentcategory = h1[i].properties.CATEGORY;

if(brands.indexOf(currentbrand) === -1 && currentbrand)
			{
			   brands.push(currentbrand);
			   DD_BRAND.addItem(currentbrand);

			}
	
if(category.indexOf(currentcategory) === -1 && currentcategory)
			{
			   category.push(currentcategory);
			   DD_CATEGORY.addItem(currentcategory);
			}


}

DD_BRAND.setSelectedKey("BMW");
DD_CATEGORY.setSelectedKey("CRUISE BIKES");

Step 5 : Create a script variable "members" of type string and set as array. This is optional as I will use this to store the filtered products master data based on dropdowns. As its a script variable it can be used as global. You can also use local variable of type string array for the same.

Step 6: Add the below code to both dropdowns DD_BRAND and DD_CATEGORY. The reason I am adding to both dropdowns as I want the filtration to happen on both drop down selection.

var sel_brand=DD_BRAND.getSelectedKey();
var sel_category=DD_CATEGORY.getSelectedKey();

 
var Filter_Area = ArrayUtils.create(Type.string);
var Filter_Pattern_1 = "[SAP_CEP_PRODUCT].[H1].&["; 
var Filter_Pattern_2 = "]"; 
if (members.length>0)
{
members=[""];
members.pop();
}

var members1=PlanningModel_1.getMembers("SAP_CEP_PRODUCT",{limit:1000});
for(var counter1=0;counter1<members1.length; counter1++) 
{ if(members1[counter1].properties.BRAND===sel_brand && members1[counter1].properties.CATEGORY===sel_category)
	{var filtered_member=members1[counter1];
	 members.push(filtered_member.id);
		
	}

}
	
for(var i=0;i<members.length; i++) {
var Filter = Filter_Pattern_1 + members[i] + Filter_Pattern_2;
Filter_Area.push(Filter);
	
}

Table_1.getDataSource().removeDimensionFilter("SAP_CEP_PRODUCT");
Table_1.getDataSource().setHierarchy("SAP_CEP_PRODUCT","H1");
if (Filter_Area.length>0)
	{
Table_1.getDataSource().setDimensionFilter("SAP_CEP_PRODUCT",Filter_Area);
	}
else {
Table_1.getDataSource().setDimensionFilter("SAP_CEP_PRODUCT","[SAP_CEP_PRODUCT].[H1].&[NONE]");
Application.showMessage(ApplicationMessageType.Warning, "No such combination of brand and category exists");			
	}

Output:

Add the table widget and add the product in rows and measure in columns (you can choose any dimension/measure in column). Enable the brand and category attribute for products (easier to verify)

If you select a brand and category for which there are no members: You get a warning message.

Hope this helps !!

Please upvote/accept if this helps

Nikhil

[pooja_elangovan01]

Nikhil Anand,

I sincerely appreciate your efforts in answering my question. However, I would like to clarify that my scope of application will be like below attached screenshot. Thank you for your time and concern.

The properties Brand, Category, and others have nearly 2000 - 3000 members, I believe it would be most efficient to implement a dropdown menu based on dimension properties, which would enable the table and chart to dynamically populate members based on the user's selection.

Regards,

Pooja.