Create Cascading DropDowns / Filters using Universe as Datasource

[Former Member]

In Design Studio, we would like to create 3 cascading dropdowns (Site > Area > Work Center) which will only show the corresponding areas / work centers in the dropdown list when user select a specific site / area.

Using universe as data source, we are not able to use the hierarchy defined within the universe.   To achieve the requirement, we use the following setup.

Setup

1. Define 3 global variables to hold user selected site, area and work center values.

2. Define the data source query filter with optional prompt on both Site and Area.

As the prompt is optional, all sites, areas and work centers are included when prompts have no value.

3. Create 3 dropdown for Site , Area and Work Center.  Fill up these 3 dropdowns in the "On Startup" application event using the defined data source DS_1.

4. In site dropdown "On Select" event.  Update global variable g_site, g_area and g_workCenter.  In this example we reset g_area and g_workCenter to default value to illustrate when g_site changed, existing g_area and g_workCenter needs to be invalidate.  Update DS_1 site and area prompts with the new values.

5. In area dropdown "On Select" event.  Update global variable g_area and reset g_workCenter.  Update DS_1 area prompt with the selected area.

6. In work center dropdown "On Select" event, update global variable g_workCenter.

7. In DS_1 "On Result Set Changed" event.  Update both area and work center dropdown.  As the area and work center dropdown are refreshed, set the dropdowns with user selected values.

Result:

When application start, both prompts have no values and all dropdowns are not filter.

By selecting a specific site from the site dropdown, both area and work center drop down are updated to show only area and work center under the selected site.

Selected a specific area will update the work center dropdown to show only work center under the selected area.

Selected a new site from the site dropdown will update both area and work center dropdown with all areas and work centers under the newly selected site.  It also reset the previous selected area and work center to "All".

When a specific area is selected, the area dropdown will only contain 2 items.  To switch form a selected area back to all areas under the selected site, select the "All" item in the area dropdown, which will refresh both area and work center dropdown to show all areas and work centers again.

After selecting "All" in the area dropdown.

Note:

Since we are using data source prompt, on each selection it will involve a round trip to update resultset.  This will impact the application performance.