How to Create Multiple Dropdown boxes (Cascading Filters) when using Universe in the backend.

[former_member195517]

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