Skip to Content
Technical Articles
Author's profile photo James Barlow

Create mutually exclusive drop down lists

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

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Bob Pfeiffer
      Bob Pfeiffer

      Thanks James Barlow for sharing your work and experiences with the community. As a software developer I would recommend to use a global script function instead of duplicating the code in three different places. This is known as the DRY principle (Don't repeat yourself).

      Author's profile photo James Barlow
      James Barlow
      Blog Post Author

      Yep completely agree with you,  in practice I've used functions wherever possible.

      Thought it might be easier for people less familiar with the code if I broke it down into component parts.

      I'll edit the post with a global function example at the end.

      Actually thinking this through - with the 3 main blocks of code which bits do you think we could move to a global function

      OnInit code:

      DIM1 = Table_1.getDimensionsOnRows()[0];
      DIM2 = Table_1.getDimensionsOnRows()[1];
      ALL_DIMS = Table_1.getDataSource().getDimensions();
      
      
      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); }
      
           Dropdown_1.removeItem(DIM2);
           Dropdown_1.setSelectedKey(DIM1); 
      
           Dropdown_2.removeItem(DIM1);
           Dropdown_2.setSelectedKey(DIM2);
      

      Dropdown_1 code:

      Table_1.removeDimension(DIM1);
      var SELECTED1 = this.getSelectedKey();
      
           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); 
      
      	DIM1=SELECTED1;
          Table_1.addDimensionToRows(DIM1,0);
      

      Dropdown_2 code:

      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);