Skip to Content
Author's profile photo Former Member

Simple way to use cascading filters with unx data sources

Design Studio does not support cascading filter usage with unx data sources if you want to follow classical way as you could find at official tutorial :

Simply filter a datasource based on a selection from a dropdown box , and use getmemberList method on that data source to fill another dropdown box.

If you use this method with unx data sources, you would realize that all members are added to the second dropdown_box, that means this method doesn’t work, obviously.

You can use cascading filters with the method i mentioned below. But i must warn you that as much as members you have to be cascading filtered (for instance, if you have thousands of material ids), you will have worse performance. Because this method takes dimensions member by member, and use them all to check if they needed to be added to the second dropdown box (or listbox )

We will select country from dropdown box 1 (DD_BOX_1) and fill DD_BOX_2 with cities belong to this country

1- create an array with members of a dimension which you want to use in your dd box( to be cascading filtered)

2- do a for loop through this array, and add items if the result set is not empty.

Basically let’s say you filled first DD box with DS_1 dimension members like (Country) and selected a country and with that selection you will filter DS_2.

After that; you will filter DS_2 with each City member. if result set is not empty, which means that city belongs that country, you add this city to your DD_Box_2, else simply ignore this city.

sample:


//on startup

DD_BOX_1.setitems(DS_1.getmemberlist(Country),..,…,…);

// on dd_box_1 on select event


var array = DS_2.getmembers(City;999 (tool suggests not to enter a value above 100 btw but if you need more members you can write 999 for ex.));


DS_2.setfilter(Country;DD_BOX_1.getselectedmember());


array.foreach(function(element,index)

{DS_2.setfilter(City, element.internalKey (or text doesnt matter at unx);

if(DS_2.isResultSetEmpty())

{

//do nothing

}

else { DD_BOX_2.addItem(element.text , element.text)  ;}

});

Regards,

Onur

Assigned tags

      16 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Kenny Scott
      Kenny Scott

      Cascading Filters for UNX in BICS 2.32 release -

      2236777 - Relational Mapper – Cascading Filters

      Author's profile photo Mustafa Bensan
      Mustafa Bensan

      Hi Kenny,

      I assume Note 2236777 is not required as of Design Studio 1.6 SP02 as cascading filters for universes is supposed to be supported in this version.  Is that right?

      Thanks,

      Mustafa.

      Author's profile photo Andrea Costantini
      Andrea Costantini

      Hi Mustafa,

      I've found in the application designer guide for DS 1.6 SP2 (

      http://help.sap.com/businessobject/product_guides/AAD16/en/ds_16SP02_user_en.pdf )

      the cascading filters are not supported by this version.

      Guide.PNG

      Kind regards,

      Andrea.

      Author's profile photo Mustafa Bensan
      Mustafa Bensan

      Hi Andrea,

      Thanks for the confirmation.  Hopefully it is not a case that the Designer Guide simply hasn't been updated.  Otherwise it would appear that this feature planned for SP02 didn't make it in the end.

      Regards,

      Mustafa.

      Author's profile photo Andrea Costantini
      Andrea Costantini

      Hi Mustafa,

      Please, could you show me where did you see this feature was planned for SP02?

      Thanks,

      Andrea.

      Author's profile photo Mustafa Bensan
      Mustafa Bensan

      Hi Andrea,

      Please refer to Figure 1 under the "Analyst" title (shown below) in the following blog post: What’s New Design Studio Feature Pack ASUG Webcast Summary

      /wp-content/uploads/2016/07/1fig_949852.png

      Regards,

      Mustafa.

      Author's profile photo Robert Shoemake
      Robert Shoemake

      Hi Onur,

      I know this is an older post, but it would seem to work the same now as before.

      I am able to filter DD2 effectively the first time, however, subsequent changes to DD1 does not filter the results.

      Any idea what could be going on? Instead of country, I have Division to Region.

      My DD1 On Select Action:

      FILTERED_L_REGION.setFilter("_JX77wGH8EeWZptowH2T1zg", DROPDOWN_1.getSelectedValue());

      var array = FILTERED_L_REGION.getMembers("_Jwqk0GH8EeWZptowH2T1zg", 25);

      array.forEach(function(element, index)

      {FILTERED_L_REGION.setFilter("_Jwqk0GH8EeWZptowH2T1zg", element.internalKey );

      if(FILTERED_L_REGION.isResultSetEmpty())

      {

      //do nothing

      }

      else {DROPDOWN_2.addItem(element.text , element.text)  ;}

      });

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Robert,

      Your code seems fine, but can you try to increase the number of members at .getMembers method (which is 25 at your code) ? Because  Getmembers method takes all the members, whether you filtered the datasource or not, so every time it will take same 25 regions, independent from your filtering. Please try to make it 1000 or above. Please let me know if your problem persists.

      Author's profile photo Robert Shoemake
      Robert Shoemake

      I increased the number to 2000, with the same result.

      What I noticed is that when I select Country A first, i get A cities.

      Then, i select Country B, I get A cities and B cities.

      Select Country C, I get A B and C Cities?

      What fields does you DS_1 contain? Just Country?

      What about DS_2? Country and City?

      Also, the query only refresh ones after selecting from DD1. Should it refresh each time a new selection is made?

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Additem() method works cumulatively. You need to remove previously added items. So at the beginning of dropdown_box1 on select event, please add DD_2.removeallitems(); command.  I am not sure if it was clearallitems, or removeallitems but you will get it 🙂

      DS_1 can include both country and city or only country it doesnt matter.

      DS_2 should include both.

      For the refresh part, query shouldnt refresh ever. It should use same data output. Thats the difference from .setvariablevalue method.

      Hope i could help

      Author's profile photo Robert Shoemake
      Robert Shoemake

      Perfect! Worked like charm with exception of clearing my "All Cities" options. I added in

      DROPDOWN_2.addItem("All Cities", "All Cities");

      to keep my users happy so they can see data for each DD_1 as a whole.

      Odd thing happening on DD2 is that it appends spaces to the end of the selection after 1 change.


      For example, "San Diego" is selected first and is fine.

      Then, I select "San Francisco" and it appears "isco                     "


      I'll have to try to trim that off. Once you click off of the selection, it keeps the selection and removes the excess space, if that's what we want to call it. Odd behavior...

      Many thanks on this Onur!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Glad i could help Robert 🙂 I never faced an issue like you described (with excess spaces) , it could be some kind of bug. I suggest you to test this in different browsers and local mode to see if this behaviour persists.

      Good luck solving this issue too .

      Author's profile photo David Heath
      David Heath

      Does this still work in DS 1.6?

      I'm finding it impossible for this part of the script to resolve to anything other than positive. i.e. None of the members from the array seem to match with the results from the data source.

      array.forEach(function(element,index)

      {

          DS_CITY.setFilter("_AfW4MDxbEeaGSISSyORTCQ", element.text );

      if(DS_CITY.isResultSetEmpty())

      {

      //do nothing

      }

      else { DROPDOWN_CITY.addItem(element.text , element.text)  ;}

      });

      Author's profile photo Mustafa Bensan
      Mustafa Bensan

      Hi David,

      What version of Design Studio are you running?  The recently released DS 1.6 SP02 is supposed to support cascading filters for universes.

      Regards,

      Mustafa.

      Author's profile photo David Heath
      David Heath

      Thanks for the reply. I'm on 1.6 SP1. I'll try upgrading to SP2 and try the new functionality.

      Regards

      David

      Author's profile photo Mustafa Bensan
      Mustafa Bensan

      Hopefully you'll get the desired result and can share your experiences.  FYI, some community members have reported encountering issues as per the following discussion:

      Cascading filters in SP02

      Regards,

      Mustafa.