Interlinked Dropdown Filters Using Script in SAP A...

pratyushg13

In SAP Analytics Cloud (SAC) stories, dynamic and interactive filtering helps users navigate data more effectively. One such powerful interaction is interlinked dropdowns, where the selection in one dropdown automatically updates the other, and both together filter a linked table.

In this post, I will walk you through how to create two interlinked dropdown filters in SAC using scripting, where:

Dropdown 1 contains a dimension (Version).
Dropdown 2 contains a measure (Measure).

The requirement is such that:

Selecting a Version should automatically select its associated Measure.
Selecting a Measure should automatically select its associated Version.
The table will be filtered accordingly.

Scenario

We will use the following example:

Version	Measure
Actual	M1
Forecast	M2
Version3	M3
Version4	M4
Version5	M5

If the user selects Actual, M1 will be automatically selected, and the table will show data for Actual & M1.
Similarly, if the user selects M3, Version3 will be selected, and the table will filter accordingly.

diagram for the interaction flow

Steps to Implement Interlinked Dropdowns

1. Insert the Dropdowns and Table

From the Insert menu in your story, insert two Dropdowns and a Table.
Ensure the Table has both the Version and Measure fields you want to display and filter.

2. Prepare a Mapping Table (Optional but Helpful)

It’s a good practice to create a mapping table in your model or story that clearly defines the relationship between Version and Measure. This simplifies scripting.

3. Script for the Dimension (Version) Dropdown

Select the Version Dropdown.
Open the Outline panel and select the dropdown widget.
Click on fx (Script) for onSelect() event.
Use the following example script (modify based on your model):

var selectedVersion =  Dropdown_1.getSelectedKey();

var measureMap = {
    "public.Actual": "M1",
    "public.Forecast": "M2",
    "public.Version3": "M3",
    "public.Version4": "M4",
    "public.Version5": "M5"
};

var associatedMeasure = measureMap[selectedVersion];

Dropdown_2.setSelectedKey(associatedMeasure);

Table_7.getDataSource().setDimensionFilter("Version", selectedVersion);
Table_7.getDataSource().setDimensionFilter(Alias.MeasureDimension,[associatedMeasure]);

4. Script for the Measure Dropdown

Select the Measure Dropdown.
Open fx (Script) for onSelect() event.
Use the following example script:

var selectedMeasure = Dropdown_2.getSelectedKey();

var versionMap = {
    "M1": "public.Actual",
    "M2": "public.Forecast",
    "M3": "public.Version3",
    "M4": "public.Version4",
    "M5": "public.Version5"
};

var associatedVersion = versionMap[selectedMeasure];

Dropdown_1.setSelectedKey(associatedVersion);

Table_7.getDataSource().setDimensionFilter("Version", associatedVersion);
Table_7.getDataSource().setDimensionFilter(Alias.MeasureDimension,[selectedMeasure]);

5. Initialize the Dropdowns on Page Load

In the Outline Panel, select the Page, and in the onInitialization() event, add the script to populate the dropdowns:

var version_list = Table_7.getDataSource().getResultSet();

var i=0;

for ( i=0; i<version_list.length; i++)
{
    Dropdown_1.addItem (version_list[i]["Version"].id, version_list[i]["Version"].description);
}

var measure = Table_7.getDataSource().getMeasures();

var measure_list = ["M1","M2","M3","M4","M5"];

var j=0;

for (j=0;j<measure.length;j++)
{
    if(measure_list.indexOf(measure[j].id) !== -1 && measure[j].id)
    {
        Dropdown_2.addItem(measure[j].id,measure[j].description);
    }
};

Dropdown_1.setSelectedKey("public.Actual");
Dropdown_2.setSelectedKey("M1");

Table_7.getDataSource().setDimensionFilter("Version", "public.Actual");
Table_7.getDataSource().setDimensionFilter(Alias.MeasureDimension,["M1"]);

End Result

Selecting M1 will automatically select Actual, and the table filters accordingly.
Selecting Version4 will select M4, and the table will show data only for Version4 & M4.

Conclusion

This interlinked dropdown logic can be extended to many use cases where your business requirement demands a dependent filter mechanism in SAC stories. This not only improves user experience but ensures the right data context is always displayed.

Try adapting this pattern to your scenarios such as Region-Product, Category-Metric, etc.

SAP Analytics Cloud SAP Analytics Cloud, analytics designer JavaScript SAP Analytics Cloud for planning

Interlinked Dropdown Filters Using Script in SAP Analytics Cloud (SAC Story)