I searched a lot to  find a solution to this looks like there is no readymade solution available anywhere.  Cascading Filters work only with BW or HANA (OLAP) datasources.

I found the only solution (I could not think of anything else) to this issue.  Hope this is helpful to you guys.  Here is what I did.

Country>>State>>City

1) Create three data sources:

1.1– COUNTRY_DS. This will load on startup of the dashboard.  Just get the Country object and some measure through this query.  I created a column ‘count’ in the database so you can pull Country field and ‘Count’ measure field as a query.

1.2– STATE_DS: Set Load in Script to ‘True’.  Get State and Count fileds, but only this time you set a Country prompt for this query.

1.3– CITY_DS: Set Load in Script to ‘True’.  Get City and Count fields and set State prompt for this query.

2) create global variables v_country, v_state

3) Create three dropdowns COUNTRY_DD, STATE_DD, CITY_DD. For All or at least State and City Dropdowns, manually enter ‘ALL’ items section in the properties tab. (You will not be loading City datasource until user selects a State; till then ALL will be displayed in the dropdown).

4) On Start up, Write the following script.

//4.1:–

COUNTRY_DD.setItems(COUNTRY_DS.getMemberList(<country dimension>, MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 0);

//

//(Please note that you might have to experiment a bit with ‘MemberPresentation’ formats a bit.  Check what works for you. It worked for me when I used MemberPresentation.TEXT, but sorting will  become a problem if you don’t use ‘internal key’, in which case you might have to use sort method to sort data for your dropdowns)

//4.2:–

v_country=COUNTRY_DD.getSelectedText();

// (you might think of using ‘On Variable Initialization’ option to load State data (4.2), but what I found is that you need to set a default value for the global variable if you know for sure which country is going to  be the first one in the list or if you have a default value that you want then we can go for this option.  if it is possible that there  is no data for the default country in your database, then the result set is going to be empty.  So best option is load in script and do not set Force Prompts on start up option).

//4.3:–

STATE_DS.loadDataSource();

//(This will load the datasource for state and the below script will  pass the country parameter)

//4.4:–

APPLICATION.setVariableValue(‘psEnterCountry’,v_country);

//

//4.5:–

STATE_DD.setSelectedValue(“ALL”);

5) in the On Click event of the COUNTY_DD:

v_country=COUNTRY_DD.getSelectedText();

//

STATE_DS.LoadDatasource();

//

APPLICATION.setVariableValue(‘psEnterCountry’,v_country);

//

STATE_DD.setItems(STATE_DS.GetMemberList(<State Dimension>, MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 0,”ALL”));

/*0 will give you all the items in the list; and “ALL” will add ALL as the first item in the list in case you already do not have ALL in your database*/

//(Please note that you might have to experiment a bit with ‘MemberPresentation’ formats a bit.  Check what works for you. It worked for me when I used MemberPresentation.TEXT, but sorting will  become a problem if you don’t use ‘internal key’, in which case you might have to use sort method to sort data for your dropdowns)

6) In the On Click even property of STATE_DD:

v_state=STATE_DD.getSelectedText();

//

CITY_DS.LoadDatasource();

//

APPLICATION.setVariableValue(‘psEnterState’,v_state);

//

CITY_DD.setItems(CITY_DS.GetMemberList(<State Dimension>, Memberpresentation.INTERNAL_KEY, MemberDisplay.TEXT, 0,”ALL”));

//(Please note that you might have to experiment a bit with ‘MemberPresentation’ formats a bit.  Check what works for you. It worked for me when I used MemberPresentation.TEXT, but sorting will  become a problem if you don’t use ‘internal key’, in which case you might have to use sort method to sort data for your dropdowns)

Hope this helps.  You will surely have some more questions while implementing these just like I did have, which I will be happy to answer.

Vijay Bhaskar P

To report this post you need to login first.

48 Comments

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

  1. Penny Robards

    Hi Vijay,

    Thanks for the post, I have been trying to solve the issue of cascading parameters over a universe for a while.

    I am wondering do you know if there is a way to stop the user from being prompted for the country prompt the first time they run the application? i.e. I would like the prompt to be filled by the script, not the user.

    Thanks Penny.

    (0) 
    1. Vijay Bhaskar P Post author

      Hi Penny,

      Use “APPLICATION.setVariableValue(‘psPromptText’,v_country);” on startup section .  If it is a fixed prompt value then you can replace varualbe value with text “USA” etc.

      You can also set this up in “On Variable Initialization” section to avoid prompt.

      Let me know if this answers your question.  If not, provide more details as to what are your datasources etc.

      Thanks,

      VB

      (0) 
      1. Penny Robards

        Hi VB,


        Thanks for the information; “On Variable Initialization” section to set the value of the variables will avoid the user being prompted when the application is opened.


        i.e as you suggest place:


        APPLICATION.setVariableValue(‘psPromptText’,v_country) in the “On Variable Initialization”

        I had been trying something similar i.e. three different queries however I was trying to filter the data source as opposed to run it on demand and filter.


        I believe that there may be an error in your post:


        (on select Country)

        STATE_DD.GetMemberList(<State Dimension>, MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 0,”ALL”);


        I think it should be


        STATE_DD.setItems(STATE_DD.getMemberList(“_9HY_4cBvEeOMbdZh-4RtVw”, MemberPresentation.EXTERNAL_KEY,MemberDisplay.KEY,300,”All” ));


        Thanks again for the post very helpful, absolutely nobody else has been able to come up with a solution to this problem, even SAP thought that it was impossible!


        Well done!


        Pen.

        (1) 
        1. Vijay Bhaskar P Post author

          Thanks Pen.  I thought this would cut down lot of efforts and frustrations for people like me out there.

          You are correct.  I missed entering ‘SetItems’ in my code.

          Also one little alteration to your suggestion is

          STATE_DD.setItems(STATE_DS.getMemberList (should be STATE_DS) as per my datasource name before getMemberList.

          All, please note this change so you will not have to reinvent the wheel 🙂 .  I will be editing my original post too.  Thanks Pen.

          Regards,

          VB

          (0) 
    1. Vijay Bhaskar P Post author

      Hi Paolo,

      In the universe level when you build the query.  you set Country in the filter and set it to prompt.  Then a prompt definition window opens and you type whatever text you want there.  I selected EnterCountry.  It manifests as psEnterCountry (appends ‘ps’) when you use APPLICATION.setInitialVariableValue() method in the content assistance in Design Studio.  You will get to see all prompt texts and you pick the relevant one.  Let me know if it  is still unclear.

      (0) 
      1. Paolo Mapelli

        OK, got it, put psEnterCountry into STATE_DS and psEnterState into CITY_DS.

        Now I’m facing another issue. I’ve a chart component, bounded to a DS called OGGI_CHART_1, with the same dimensions above.

        For country my dimension is called OBJ_239.

        Now, after selecting a country I want the chart showing “progressive” filter so in the COUNTRY_DD “On Select” I added the following code at the end:

        OGGI_CHART_1.setFilter(‘OBJ_239’, v_country);

        The issue is that the chart doesn’t get filtered. Should the setFilter be applied with some other thing ?

        Thanks again.

        Paolo.

        (0) 
        1. Vijay Bhaskar P Post author

          Check if you select another country from the dropdown list is the chart data changing.  Sometimes the variable holds previous selection and current selection is visible on next selection.  I faced this issue too.

          Try setting prompt to OGGI_CHART_1 as well.  so you can pass the same country as prompt to OGGI_CHART_1 datasource too.  Try it and let me know.

          (0) 
  2. afs naz

    Hi Bhaskar,

    Thanks for the post!!

    I’m facing the issue where the variable holds previous selection and current selection is visible on next selection.

    Is there any resolution for this?

    (0) 
    1. Vijay Bhaskar P Post author

      Hi afs naz,

      you can get over this issue by doing the following.

      I understand you are facing the following problem.

      When you select a country from country drop down box, the variable still holds the previous country and therefore the state dropdown box still displays states for the previous country.

      What you can do is this:

      1. create two state dropdown boxes.

      2. on load the first value in the country drop down box will be selected and the states in the state drop down boxes are corresponding to the selected country.

      3. when you change the country the state drop down box does not change.  therefore you need to create another state dropdown box and keep its visibility as false for both the state drop down boxes.

      4. when the dashboard loads statedd2 is not visible.  only statedd1 is set to visible.

      5. in the on select section of the countrydd you can enter a code to hide statedd1 and set the statedd2 as visible.  this will make sure that statedd2 will always populate the values corresponding to the selection in countrydd.  do that for city dd as well.

      I think this is a normal behavior and this is the only way I could overcome this.

      Let me know if this helps.

      Regards,

      VB

      (0) 
      1. afs naz

        Vijay,

        I was using getselectedvalue() instead of getselectedText() as mentioned in your post when i changed my variable to getselectedText() from dropdown it worked now my variable allways holds the current selection:-).

        Thanks for your response Vijay!!!

        (0) 
  3. Naveen M

    Vijay,

    I am using Design Studio 1.3 SP1 on native HANA views and I tried many ways to implement Cascading filters. But looks like none of the options are working as of now. I tried the work around that you explained above but that doesn’t work too..

    Do you know if there is any other workaround to implement cascading filters in design studio with HANA views as data sources.

    Thanks,

    Naveen

    (0) 
    1. Vaibhav Singh Rathore

      Hi Kiran,

      As far as cascading dropdown on HANA views is concerned you can follow the below mentioned steps:

      on start-up:

      DROPDOWN_1.setItems(DS_1.getMemberList(“DIM_1″, MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 20,”ALL”));

      on click of DROPDOWN_1:

      DS_1.setFilter(“DIM_1”, DROPDOWN_1.getSelectedText());

      DROPDOWN_2.setItems(DS_1.getMemberList(“Emp_ID”, MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 20,”ALL”));

      on click of DROPDOWN_2:

      DROPDOWN_2.getSelectedText();

      Edit Initial View:

      take the dimension of second dropdown in background filter, right click it and select “Members For Filtering—->Only values with posted data”

      Hope this helps

      Regards

      Vaibhav

      (0) 
  4. Cañas José

    Hi Vijay very good post!!

    I want a dropdownlist with month_name sort by month number. I have a data source with both month_name and month _number with sort by month number, but when i load the data source into the dropdownlist doesn’t keep the sort any suggestions?

    (0) 
    1. Vijay Bhaskar P Post author

      I think you are doing a member display by key.  Try text.  Also, in edit initial view section of the datasource, you can click on the arrow symbol beside the header and you can sort it ascending or descending.  Let me know if this helps.

      (0) 
  5. Nikki Tsoflikis

    Hi Vijay,

    Thank you for your awesome post – much appreciated! I thought you weren’t able to use a BW Universe for Design Studio since this a multi-source universe and this is not supported by Design Studio. Am I wrong? How did you get this universe to work?

    Thanks so much,

    Nikki

    (0) 
    1. Vijay Bhaskar P Post author

      Hello Nikki,

      You are right I was not using a BW universe.  I was using unx format of the universe.  It was a single universe.  I don’t know if this is what you are asking but you can create any number of Datasources (queries) from a single universe.  I used that feature to accomplish the cascading filters.

      Regards,

      VB

      (0) 
      1. Nikki Tsoflikis

        Hi Vijay,

        Thanks for your prompt reply. I want to bind 2 datasources to the crosstab and I was able to do this in this by creating a universe but then I noticed that Design Studio doesn’t support multi-source universes (it was a table with a BW Query).

        However, I am trying to build Cascading Filters and although I tried following what you said, I don’t think it’s possible because the filters I am trying to use to cascade are not related to each other in an existing hierarchy. Was this the case for you as well?

        Once again, thanks so much!

        (0) 
        1. Vijay Bhaskar P Post author

          Hello Nikki,

          If you are using SAP Netweaver then you don’t need to follow what I did.   You have a pretty straightforward feature called hierarchy.  You can simply select hierarchy (expand dimension node and you will find attributes and hierarchies).  Select the hierarchy of your choice.

          I was using SAP Business Objects BI Platform under Preferred Startup Mode under Tools/Preferences because my data source was a universe.  since you are using BW, I think you should set the preferred start up mode to SAP NetWeaver.

          Then you can build a query and when you edit initial view of the datasource; you proceed with selecting hierarchies.

          I followed this method because it was not possible for me to define hierarchies when using BO Universe.

          Regards,

          VB

          (0) 
  6. Jean-Guillaume Kramarz

    HI Vijay,

    Thanks for your post.

    I’m currently setting up a cascading filter with two listboxes with a hierarchy enabled dimension.

    On startup, I populate the first listbox and activate the hierarchy like this:

    LISTBOX_1.setItems(DS.getMemberList(“ZCTRIGR”, MemberPresentation.INTERNAL_KEY,

    MemberDisplay.TEXT,1000));

    DS.assignHierarchy(“ZCTRIGR”, “ORGA”);

    DS.activateHierarchy(“ZCTRIGR”);

    LISTBOX_1 is OK.

    On select of LISTBOX_1, I try to populate LISTBOX_2 like this:

    DS.clearFilter(“ZCTRIGR”);

    var text = “HIERARCHY_NODE/ZCTRIGR/” + LISTBOX_1.getSelectedValue();

    DS.setFilter(“ZCTRIGR”, text);

    LISTBOX_2.setItems(DS_WEEK_UNIQUE.getMemberList(“ZCTRIGR”,

      MemberPresentation.INTERNAL_KEY, MemberDisplay.KEY, 100));

    I want the list of ZCTRIGR under the selected node/value and that doesn’t work!

    I always have the full list of ZCTRIGR displayed.

    When I populate with an other dimension like this:

    LISTBOX_2.setItems(DS_WEEK_UNIQUE.getMemberList(“0EMPLOYEE”,

      MemberPresentation.INTERNAL_KEY, MemberDisplay.KEY, 100));

    That works fine, except when the selected value is the lowest node.

    My questions:

    Can we set a cascading filter for the same dimension?

    Why does the script returns the full list when the selected node is the lowest one?

    Thanks,

    Jean-Guillaume

    (0) 
    1. Vijay Bhaskar P Post author

      Hi Jean-Guillaume,

      Sorry for the delay. when using data sources which are not OLAP,   you won’t be able to enable hierarchy.  what is your data source?

      (0) 
      1. Jean-Guillaume Kramarz

        Hi Vijay,

        My datasource is a Bex Query.

        There really is a problem with cascading on the same member. I went over it by copying the member in my inforprovider.

        There is also a problem with the lowest node. I did a routine to check the number of items. If it is too high, that means I’m right in the bug, i.e. on the lowest node. In that case I populate only with the member and not the node (“text”).

        Regards,

        Jean-Guillaume

        (0) 
      1. Jean-Guillaume Kramarz

        Hi, as I answered to Vijay,

        I solved it by creating a copy of the characteristic into the infocube (ZCTRIGR2) and populating the second listbox with it after filtering the DS with ZCTRIGR.

        Concerning the problem with the lowest node, I did a routine to check the number of items. If it is too high, that means I am on the lowest node (because the lowest node returns all the values). In that case, I populate the listbox only with the member on not the node.

        Regards,

        Jean-Guillaume

        (0) 
  7. Arun Krishnan

    Hi Vijay,

    Can you please help me in creating a prompt for the query. How & where I need to create the same thing in a .unx based universe. If possible help me with step by step.

    I want to implement the same cascading in my dashboard application.

    Regards,

    Arun Krishnan G

    (0) 
  8. Aurélien Vadi

    Hi Vijay Bhaskar P,

    Thank you for your post, it  was very interesting indeed.

    For information, another way to implement cascading drop dwon lists from UNX query is ti use an array to store data and then loop on it to display or not the value in the corresponding dropdown.

    If you want I can share y code.

    (0) 
      1. Onur Göktaş

        Hello

        I can make cascading filter following this way like Aurelien mentioned:

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

        sample: 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.));

        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 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:

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

        array.foreach(element,text)

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

        update: i just made a fast blog post about this so more people can see it.

        Simple way to use cascading filters with unx data sources

        Regards,

        Onur

        (0) 
  9. Sujith PN

    Hi VB,

    Thanks for the workaround.

    Im having difficulty in the statement “APPLICATION.setVariableValue(‘psEnterCountry’,v_country);”

    when i use this im getting an error as in the image, bu ti f i comment the code the application launches but the cascading doesnt work.

    Untitled.png

    (0) 
    1. Kanchana Balakrishnan

      Hi

      If you are using Design Studio 1.6 and universe as data source, it looks like the SetVariableValue method is having a known limitation as per SAP Note 2251729. As per SAP notes, correction for the same is scheduled for DS 1.6 Support package 01.

      As a workaround, the solution provided by Amala in the post mentioned in the link below can be used. This solution worked for me when implementing cascading prompt using universe as datasource in design studio 1.6

      https://scn.sap.com/thread/3389453

      Thanks

      (0) 
  10. Ajinkya Shinde

    Hi Vijay,

    I am facing an issue while implementing the above solution  in my scenario.

    I have 2 Data Sources based on efashion universe.

    DS_1 contains State,Quantity

    DS_2 contains City,Quantity with State as prompt.(Please find Fig 1 below)Fig 1.PNG

                                                                                  Fig 1

    Then I make a prompt for State as shown in Fig 2 below

    Fig 2.PNG

    Rectify me if the above prompt setting is correct.

    Now, I have 2 drop downs

    DROPDOWN_1  :- For State

    DROPDOWN_2 :- For City

    Now,DS_1 has Load In Script property to false

    and DS_2 has load in script property to true.

    I have a global variable “v_state”

    The application startup script is:-

    DROPDOWN_1.setItems(DS_1.getMemberList(“OBJ_218″, MemberPresentation.EXTERNAL_KEY, MemberDisplay.TEXT, 20,”All”));

    v_state=DROPDOWN_1.getSelectedText();

    DS_2.loadDataSource();

    APPLICATION.setVariableValue(‘psEnterState’, v_state);

    Now On Select of  State drop down ,the script is

    v_state=DROPDOWN_1.getSelectedText();

    DS_2.loadDataSource();

    APPLICATION.setVariableValue(‘psEnterState’, v_state);

    DROPDOWN_2.setItems(DS_2.getMemberList(“OBJ_166″, MemberPresentation.EXTERNAL_KEY, MemberDisplay.TEXT, 0,”All”));

    Note:- OBJ_218 is State dimension and OBJ_166 is City Dimension.

    Now,while executing/viewing  the application

    State dropdown gets populated with values

    On selection of State,All the City values are seen i.e.City drop down displays all values for any State selection.

    Please help me.

    Regards,

    Ajinkya Shinde

    (0) 
    1. Jean-Guillaume Kramarz

      Hi,

      you should try to load DS_2 after applying the value v_state to psEnterState.

      Ifhtat still doesn’t work, filter DS_2 with the psEnterState.

      Regards,

      Jean-Guillaume

      (0) 
    2. Roy Menger

      the maxNumber parameter in the second getmemberlist function is set to 0. Not sure how this would work, but i’d suggest replacing it with something like 99.

      Other than that, your script seems fine as far as I can tell. For verification, you could try to add a crosstab showing the city dimension (to be sure the data source is filtered properly).

      (0) 
  11. asfandyar rashid

    Hi

    I tried but not working. I don’t understand this part …..” APPLICATION.setvariablevalue(‘psENTERSTATE’,V_STATE);. what to enter in psENTERSTATE? and in global script variables the tyoe should be strings? and url parameter should be true?

    (0) 
  12. Michael McCormick

    Hello Vijay,

    Very helpful post, and a very clever workaround. This particular challenge was confounding us for a couple of days, before we came across this post.

    I was wondering if I can ask a follow up question. We have a scenario in which the requirement is to be able to select one or more values from the dropdown box, and subsequently have the remaining dropdown boxes filter based on these multiple selections.

    To use your example: the ability to select multiple countries in COUNTRY_DD, which would subsequently cause the STATE_DD dropdown to show all states for all the countries selected.

    The above solution is posing challenges for our scenario, since the statement APPLICATION.setVariableValue(‘psEnterState’,v_state); will only accept a single value at a time. I’ve tried sending this statement an array of values, but it won’t work. Another challenge is that SAP Design Studio apparently doesn’t let you define an array as a Global Variable.

    Any thoughts on this? Assistance would much appreciated!

    (0) 
    1. Michael McCormick

      Hello. FYI – I seem to have answered my own question. This code example allows you to send multiple values to a prompted data source, via scripting:

      //Loads all currently selected values in Team list box into array variable team_values

      var team_values = TEAM.getSelectedValues();

      var listToFilter = “”;

      //populates listToFilter string variable into one string value consisting of all selections separated by semi-colons

        team_values.forEach(function(element, index) {

            listToFilter = listToFilter + ” ” + element + “;”;

        });

      //applies selected values to prompt of data source in order to filter based on selected values

      APPLICATION.setVariableValueExt(“pmEnter Team Name:”, listToFilter);

      (0) 
      1. asfandyar rashid

        Hi,

        How can I use a query prompt in the dashboard using universe as a data source? Example I have a filter set in the query as a prompt have to make it work in the dashboard using listbox

        (0) 
        1. Mustafa Bensan

          Asfandyar,

          You are more likely to get a broader response from the community if you post your issue as a new question, very clearly explaining your scenario and the problem you require help with.

          Regards,

          Mustafa.

          (0) 
          1. Michael McCormick

            hi Mustafa,

            My opinion on this matter is that Asfandyar’s question is related to this thread. In fact, as stated in my reply below, the answer to the question lies in the original (first) post of this thread.

            (0) 
        2. Michael McCormick

          Hi Asfandyar,

          The ability to set up a query prompt using a Universe as a data source is clearly described in the original (first) post in this thread, by Vijay. In fact, the use of a prompt is the key to get this whole solution to work.

          Therefore in order to answer your question, I’d suggest re-reading the original post in detail, since it answers your question. Hope this helps!

          (1) 

Leave a Reply