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

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

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

      (0) 
        1. 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.

          (0) 
  1. 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)  ;}

    });

    (0) 
    1. Onur Göktaş 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.

      (0) 
      1. 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?

        (0) 
        1. Onur Göktaş 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

          (0) 
          1. 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!

            (0) 
            1. Onur Göktaş 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 .

              (0) 
  2. 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)  ;}

    });

    (0) 
    1. 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.

      (0) 

Leave a Reply