Dynamic Casading Listbox or Dropdown with UNX backend (using existing datasources)

[Former Member]

I was a bit surprised to find that dropdowns and listboxs do not work with Universe Data Sources the same way they do with BW Data Sources.

While there are solutions out there to implement cascades with your selections, they either involve creating your own with the SDK or using a trick with multiple data sources.  The multiple data source solution works just fine, but it adds more data sources when most of us are trying to reduce the number of data calls we have.

I created a lightweight solution that uses a single global script to parse out an array of concatenated cascade values to listboxes.  The script is called on start and on change events and it allows for multiple selections from any list.  It requires you have access to your universe and can create a dimension.

For this writeup, I am using a 3 tiered cascade of Region -> Country -> City.

• In Information Design Tool, create a new dimension that references your cascade in order, and concatenates the values.   In my case, I created a dimension called City_Hier_Concat.  With an Oracle backend (I'm not sure if it's different for other backends) I concatenate with double pipes ||. My dimension definition is simply:

  
  

  @Select(Office\Region) || '|' || @Select(Office\Country) || '|' || @Select(Office\City)

  
  
  

               This should output a string of Region|Country|City separated by a pipe character.

• Add this new dimension to your existing datasource that contains your dimensions you will filter against.
  • Set your initial view to what it was previously.  It is not necessary to add this new dimension to the initial view. 
    
• In my layout I created 3 listboxes and a crosstab.  I labeled the listboxes LB1, LB2, and LB3 and set the crosstab datasource. 
  
• I created a global script set called Datafill, and a script called LB_CASCADE.
  • LB_CASCADE expects a single integ
    
  • The LB_CASCADE script:

//Assign concatenated list to array variable

var arraymembers = DS_1.getMembers("<city_hier_concat>", 250);

//Initialize string variables to hold each member once concatenated list is parsed out

var LB1_ELEMENT = "";

var LB2_ELEMENT = "";

var LB3_ELEMENT = "";

//Depending on entry point, clear and set default to *All text (null value) for lists

if (pLB == 0) {

  LB1.removeAllItems();

  LB2.removeAllItems();

  LB3.removeAllItems();

  LB1.addItem("", "*All");

  LB2.addItem("", "*All");

  LB3.addItem("", "*All");

  LB1.setSelectedValue("");

  LB2.setSelectedValue("");

  LB3.setSelectedValue("");

}

else if (pLB == 1) {

  LB2.removeAllItems();

  LB3.removeAllItems();

  LB2.addItem("", "*All");

  LB3.addItem("", "*All");

  LB2.setSelectedValue("");

  LB3.setSelectedValue("");

}

else if (pLB == 2) {

  LB3.removeAllItems();

  LB3.addItem("", "*All");

  LB3.setSelectedValue("");

}

//Get selected values from lists and assign to variables

var LB1SELECT = LB1.getSelectedValues();

var LB2SELECT = LB2.getSelectedValues();

var LB3SELECT = LB3.getSelectedValues();

//Loop through concatenated list, split out members and assign to element variables

arraymembers.forEach(function (a , index) {

  var elementsplit = a.text.split("|");

  var x = 0;

  elementsplit.forEach(function (b , index) {

  if (x == 0) {

  LB1_ELEMENT = b;

  x = x + 1;

  }

  else if (x == 1) {

  LB2_ELEMENT = b;

  x = x + 1;

  }

  else if (x == 2) {

  LB3_ELEMENT = b;

  x = 0;

  }

  });

  //Depending on entry point, check to see if elements match selection or if *All is selected (null value)

  //if matched, assign child members to appropriate listbox

  if (pLB == 0) {

  LB1.addItem(LB1_ELEMENT, LB1_ELEMENT);

  LB2.addItem(LB2_ELEMENT, LB2_ELEMENT);

  LB3.addItem(LB3_ELEMENT, LB3_ELEMENT);

  }

  else if (pLB == 1) {

  LB1SELECT.forEach(function (c , index) {

  if (LB1_ELEMENT == c || c == "") {

  LB2.addItem(LB2_ELEMENT, LB2_ELEMENT);

  LB3.addItem(LB3_ELEMENT, LB3_ELEMENT);

  }

  });

  }

  else if (pLB == 2) {

  LB2SELECT.forEach(function (d , index) {

  if (LB2_ELEMENT == d) {

  LB3.addItem(LB3_ELEMENT, LB3_ELEMENT);

  }

  if (d == "") {

  LB1SELECT.forEach(function (c , index) {

  if (LB1_ELEMENT == c || c == "") {

  LB2.addItem(LB2_ELEMENT, LB2_ELEMENT);

  LB3.addItem(LB3_ELEMENT, LB3_ELEMENT);

  }

  });

  }

  });

  }

});

//Sort Results

LB1.sort();

LB2.sort();

LB3.sort();

//Set appropriate filters to selected elements.

//Since *All texts are set to null values, selecting *All will essentially clear that filter.

DS_1.setFilter("<region>", LB1SELECT);

DS_1.setFilter("<country>", LB2SELECT);

DS_1.setFilter("<city>", LB3SELECT);

• Finally call the script:
  • On Startup:  DATAFILL.LB_CASCADE(0);
  • LB1 On Select:  DATAFILL.LB_CASCADE(1);
  • LB2 On Select:  DATAFILL.LB_CASCADE(2);
  • LB3 On Select:  DATAFILL.LB_CASCADE(3);

That's it!

I am sure the script can be improved upon.  I may add input field searches to the lists as well.

There are some caveats and notes:

• LB1 is always static.  With unscripted, normal cascade filters from BW sources, I believe that once you make a selection LB1 would filter itself down as well, and you'd have to click a button to clear the filters.  I prefer how this functions over how BW sources would function.
  • With LB1 always being static, you cannot use the built in Property Binding Filter option on LB1.  Regardless, I set the filters manually in the script.
• You can create a button and assign DATAFILL.LB_CASCADE(0); to the button to reset the filters.  Or you can just select *All from LB1 to do the same. 
• *All will appear even if there is only a single value present in the list.  I prefer this to happen, although you can easily check if a single value exists and skip adding *All. 
• You should be able to easily scale this solution out to more tiers based on your requirements.  However, this is 3 tiers with almost 250 elements per tier and it relies on nested loops.  With my data of 250 elements, the bottom cascade ends up looping through 250 times initially, and then 250 times inside each initial loop for a total of 62,500 loops.  It happens in milliseconds.  You may have a different experience once you begin adding more tiers and more elements.  I have not tested this.
• This should be able to be used with dropdowns exactly the same.  I have not tested this. 

Good luck and Happy Cascading!  Feedback, suggestions, improvements are always welcome, and I will update accordingly.

Chad