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 · Accepted solution]

pooja.elangovan01

Thanks for clarifying your requirement. Below is the solution yo can approach.

Step 1 : Create a dropdown with attribute ID and description. As you are only using handful of attributes you can populate them manually. Please ensure that ID in dropdown is same as Dimension attribute ID. I have added only 2 attributes but you can add more on same lines.

Step2: Add a table and chart with attribute on rows and your desired measure on columns. I have kept brands on rows.

Now go to the On select event of the dropdown created in step 1 and add the below code.

var sel_attribute=DD_Attributes.getSelectedKey(); // reads the current dropdowm selection
var dim_rows = Table_1.getDimensionsOnRows();    // gets the current dimenions on rows in Table
var fp = "SAP_CEP_PRODUCT."+ sel_attribute;     // creates the pattern for parameter to be used later
var dim_ctr_axis =Chart_1.getDimensions(Feed.CategoryAxis); //gets the current dimenions on rows in Chart


//-- Remove all dimensions on rows in table
for(var i=0;i<dim_rows.length; i++) 
{
Table_1.removeDimension(dim_rows[i]);
}
//--

//-- Add the dropdown selected attribute to the rows

Table_1.addDimensionToRows(fp);

//-- Remove all dimensions on rows in chart
for(var j=0;j<dim_ctr_axis.length; j++) 
{
Chart_1.removeDimension(dim_ctr_axis[j],Feed.CategoryAxis);
}


//-- Add the dropdown selected attribute to the chart


Chart_1.addDimension(fp,Feed.CategoryAxis);

Output: When dropdown says Brand

When dropdown says Category

Hope this helps !!

Please upvote/accept if this helps you

Nikhil

[sourabhpaul]

Hi,

For this, you should implement cascading effect on dropdowns.

In the first dropdown, you should add only the brand and category. and in the next dropdown, you can add values according to the value selected in the first dropdown.

Regards,

Sourabh Paul

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

[N1kh1l]

pooja.elangovan01

Do you have dropdown for Brand and Category already populated ? Once Brand and category is selected in dropdown, you want to filter your table with all master data members of BPP where Brand and category attributes are same as dropdown selections ? Also is the model involved a planning model ?

Is my understanding right ?

Nikhil