In this blog post we’ll go through how to create mutually exclusive drop down lists.

I had need to create this logic during a recent client project as they were using live BW connectivity with a compressed table.
They wanted to be able to switch dimensions in and out of a table to create a simple drill down type effect that could be customized by users.

Trying to add the same dimension twice to a table; inevitably would result in a SAC script error
combined with the fact that having the same dimension at both levels of a compressed table
makes no sense.

In the graphic below we can see that the value selected in a drop down list will be removed from
from the other drop down.

Example:
If the current value in Drop down 1 is ‘Product’ and we then selected ‘Store’ in Drop down 1
Store will no longer be available for selection in Drop down 2
However Product will now be available for selection in Drop down 2

Scripting the logic

Widgets

Dropdown_1
Dropdown_2
Table_1

Global Script Variables

ALL_DIMS      Type: DimensionInfo  Set As Array: YES
DIM1               Type: String
DIM2               Type: String

onInitialization code

/* I use this code onInitialization to grab the dimensions present
   in the table at runtime and write them to global variables */

// Get the dimension in the first row (visually column 1)
DIM1 = Table_1.getDimensionsOnRows()[0];
// Get the dimension in the second row (visually column 2)
DIM2 = Table_1.getDimensionsOnRows()[1];

// ---------------------------------------------------------------------

// Get all the dimensions from the datasoure
ALL_DIMS = Table_1.getDataSource().getDimensions();

/* Loop through all dimensions in the query and them all
    to both dropdowns */ 
for (var i=0;i<ALL_DIMS.length;i++)
	{ Dropdown_1.addItem(ALL_DIMS[i].id,ALL_DIMS[i].description);
	  Dropdown_2.addItem(ALL_DIMS[i].id,ALL_DIMS[i].description); }

// -----------------------------------------------------------------------

/* This code ensures the drop down shows the correct dimension that 
   corresponds to the table (e.g. drop down 1 shows the dimension in
   column 1 & doesn't contain the dimension in column 2)    */

     // Remove the dimension in the 2nd table row from this dropdown
     Dropdown_1.removeItem(DIM2);
     // set the selected Dropdown value to DIM1
     Dropdown_1.setSelectedKey(DIM1); 
     
     // Remove the dimension in the 1st table row from this dropdown
	Dropdown_2.removeItem(DIM1);
     // set the selected Dropdown value to DIM2
     Dropdown_2.setSelectedKey(DIM2);

Dropdown_1 code

// Remove DIM1 from the table (the dimension in the first row)
Table_1.removeDimension(DIM1);

/* Get the dimension value selected in this dropdown and store it 
   in the variable 'SELECTED1'   */
var SELECTED1 = this.getSelectedKey();

/* 
1. This code below removes all values from Dropdown 2, 
   then adds them all back in to it.

2. # The mutually exclusive bit # 
     It then removes the dimension selected (SELECTION1) 
     in Dropdown1 from Dropdown 2 

3. It then sets the dropdown2 selected key to the dimension 
   value stored in the variable 'DIM2'  */

     Dropdown_2.removeAllItems();
     for (var i=0;i<ALL_DIMS.length;i++)
	 {Dropdown_2.addItem(ALL_DIMS[i].id,ALL_DIMS[i].description);}
     Dropdown_2.removeItem(SELECTED1);     
     Dropdown_2.setSelectedKey(DIM2); 

/* Set the Global variable DIM1 to the value selected in 
   dropdown 1 (SELECTED1) */
	DIM1=SELECTED1;
// Add DIM1 to row 0 of the table
    Table_1.addDimensionToRows(DIM1,0);

Dropdown_2 Code

Uses the same logic as Dropdown_1
just the Drop down and global variables being referenced are different.

Table_1.removeDimension(DIM2);
var SELECTED2 = this.getSelectedKey();

     Dropdown_1.removeAllItems();
     for (var i=0;i<ALL_DIMS.length;i++)
	 {Dropdown_1.addItem(ALL_DIMS[i].id,ALL_DIMS[i].description);}
     Dropdown_1.removeItem(SELECTED2);     
     Dropdown_1.setSelectedKey(DIM1); 

	 DIM2=SELECTED2;
     Table_1.addDimensionToRows(DIM2,1);

Summary

In this post we’ve seen how script logic can be implemented to provide greater functionality to end users without the need to launch the Navigation/Builder panel in Analytic Applications.

This type of logic could also be expanded to provide more advanced Global filter functionality within an application.

Whenever feasible I try to design analytic applications that achieve functionality without relying on
menus / features that may require user training.
The goal is to produce as close to a ‘no training’ required solution as possible.

Feel free to add comments, questions below & as always further help from the community can be
found here: Analytic Designer Q&A