Best Practice: Filter Table and Chart Through Settings Panel (Popup Window)

As an application designer or story developer, learn how to let viewers filter a table or a chart using a popup window.

Prerequisites

  • You've already added a table and a chart widget and placed them on top of each other.

  • To follow all functions of this sample use case, you've completed the exercise Best Practice: Switch Between Chart and Table and can now enhance your analytic application or optimized story.

Context

Through a dropdown list, viewers can filter the table and chart according to certain measures of the dataset, including Gross Margin, Discount, Quantity Sold and Original Sales Price.

In the popup widget, they can then switch between chart and table through a radio button and control the measures Actual, Plan, Absolute and % of Deviation through a checkbox group.

Procedure

  1. Create a dropdown widget, and add a label to it.
    1. Add a dropdown widget, and place it above your table.
    2. Change the ID of the dropdown to Dropdown_MeasureGroup.
    3. To add a label to the dropdown, add a text widget, and place it to the left of the dropdown. Enter Measures Group in it.
    4. Change the ID of the text widget to Dropdown_Measures_Label.
  2. Select which measures can be filtered on in the dropdown.
    1. In the Builder panel of Dropdown_MeasureGroup, under Dropdown Value choose (Add).
    2. Enter Gross_Margin as ID and Gross Margin as dedicated text.
    3. Similarly, add three more values. Enter Discount as ID and Discount as dedicated text.
    4. Enter Quantity_sold as ID and Quantity Sold as dedicated text.
    5. Enter Original_Sales_Price as ID and Original Sales Price as dedicated text.
  3. To let viewers open the popup by clicking on a settings icon, add an image.
    1. Add an image widget, and place it next to the dropdown.
    2. Choose Upload Image, and upload this image .
    3. Change its ID to Settings_Logo.
  4. Add a popup.
    1. In the Outline panel, (for analytic application) choose next to Popups, or (for optimized stories) hover over the relevant page, and select Start of the navigation path Next navigation step  Add PopupEnd of the navigation path.
    2. Change the ID the popup to Popup_Settings.
    3. In the Builder under Popup Settings, switch on Enable header & footer.
    4. Enter Settings as Title.
    5. Under Buttons add two buttons, OK and Cancel. For the first button, enter Ok_button as ID and OK as dedicated text. Then, select all the options Emphasized, Enabled and Visible.
    6. For the second button, enter Cancel_button as ID and Cancel as dedicated text. Then, select the options Enabled and Visible.
    7. Choose Apply to save the changes.
  5. To let viewers switch between table and chart, add a radio button group.
    1. Add a radio button group widget, and place it in the middle of the popup window.
    2. In the Builder panel under Radio Button Group Value, enter Show Table as text for Value 1 and Show Chart as text for Value 2, and set Show Table as default.
    3. In the Styling panel, enter RadioButtonGroup_View as ID, and select Vertical Layout under Display Option.
  6. To let viewers choose the displayed measures, add a checkbox group.
    1. Add a checkbox group widget, and place it under the radio button group widget.
    2. In the Styling panel of the checkbox group, enter CheckboxGroup_Measure_Selection as ID, and select Vertical Layout under Display Option.
    3. Switch on Label Text, and enter Measures.
    4. In the Builder panel, under Checkbox Group Value, choose twice to create Value 3 and Value 4.
    5. For Value 1, enter Actual as ID and text respectively. For Value 2, enter Plan as ID and text respectively. For Value 3, enter _Abs as ID and Absolute as dedicated text. For Value 4, enter _Percent as ID and % Deviation as dedicated text.
    6. Set all four values as Default.
  7. Hover over the Settings_Logo in the Outline panel, and choose .

    In the script editor, enter the following script:

    Sample Code
    Popup_Settings.open();

    This onClick function opens the settings popup.

  8. Add three global script variables, which access the selections from any widget.
    1. In Scripting of Outline, add a script variable.
    2. In the Script Variable panel under Structure, enter CurrentMeasureFilterSelectionPopup as name, leave type as string, and switch on Set As Array.
    3. Choose Done to close the panel.

      This script variable holds the concatenated filter of the dropdown list on the page and the checkbox group in the popup window.

    4. Similarly, create the second script variable. Enter CurrentMeasureGroup as name, and instead of switching on Set As Array, enter Gross_Margin under Default Value.

      This script variable holds the current measure filter from the dropdown list.

    5. For the third script variable, name it as CurrentMeasureSelection.

      This script variable holds the selected measures from the checkbox group in the popup window.

  9. To define what happens when a filter is selected, create a script object. In this object, write a function that sets the measure filter according to what viewers have chosen from the checkbox group.
    1. In Scripting of Outline, add a script object.
    2. Rename ScriptObject_1 to Utils.
    3. Rename function1 to setMeasureFilter.
    4. Select the function setMeasureFilter, and when the Script Function panel opens, choose (Add Argument).
    5. Enter selectedId as name of the argument, leave type as string, and choose Done twice.
    6. To write the script for the function, hover over setMeasureFilter in the Outline panel, and choose .

      Enter the following script in the script editor:

      Sample Code
      Table.getDataSource().removeDimensionFilter("Account_ BestRunJ_sold"); 
if (CurrentMeasureGroup !== "") { 
	Chart.removeMeasure(CurrentMeasureGroup, Feed.ValueAxis); 
}
	
Table.getDataSource().setDimensionFilter("Account_Bes tRunJ_sold", selectedId); 
Chart.addMeasure(selectedId, Feed.ValueAxis);

      This script removes the set measure filters from table and chart and instead inserts the selected measure sent to the function.

  10. In the Outline panel, hover over Dropdown_MeasureGroup, and select . Enter the following script in the script editor:
    Sample Code
    var sel = Dropdown_MeasureGroup.getSelectedKey(); 

if (CurrentMeasureGroup === 'Gross_Margin') { 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Gross_MarginActual]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Gross_MarginPlan]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Gross_Margin_Abs]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Gross_Margin_Percent]", Feed.ValueAxis); 
} 
else if (CurrentMeasureGroup === 'Discount') { 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[DiscountActual]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[DiscountPlan]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Discount_Abs]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Discount_Percent]", Feed.ValueAxis); 
} 
else if (CurrentMeasureGroup === 'Quantity_Sold') {
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Quantity_soldActual]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Quantity_soldPlan]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Quantity_sold_Abs]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Quantity_sold_Percent]", Feed.ValueAxis); 
}
else if (CurrentMeasureGroup === 'Original_Sales_Price') { 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Original_Sales_PriceActual]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Original_Sales_PricePlan]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Original_Sales_Price_Abs]", Feed.ValueAxis); 
	Chart.removeMeasure("[Account_BestRunJ_sold].[parentI d].&[Original_Sales_Price_Percent]", Feed.ValueAxis); 
}

// save the current selection (measure filter) into a global variable 
CurrentMeasureGroup = sel; 

// get Measures Selection 
var Selected_Measures = CheckboxGroup_Measure_Selection.getSelectedKeys(); 

	// remove the current measures from Chart 
	for (var i = 0; i < CurrentMeasureFilterSelectionPopup.length; i++){ 
		Chart.removeMeasure(CurrentMeasureFilterSelectionPopup[i], Feed.ValueAxis); 
	}
	
	// help variables 
	var Filter_Pattern_1 = "[Account_BestRunJ_sold].[parentId].&["; 
	var Filter_Pattern_2 = "]"; 
	var Filter_Area = ArrayUtils.create(Type.string); 
	
	// loop over the selected measures 
	for (i = 0; i < Selected_Measures.length; i++) { 
	//concate all selection information together to a valid filter statement 
		var Filter = Filter_Pattern_1 + CurrentMeasureGroup + Selected_Measures[i] + Filter_Pattern_2; 
		Filter_Area.push(Filter); 
		
	// add Measure to Chart 
		Chart.addMeasure(Filter, Feed.ValueAxis); 
	} 
	
	// remove the "old" filter and set the new filter selection 
	Table.getDataSource().removeDimensionFilter("Account_ BestRunJ_sold"); 
	Table.getDataSource().setDimensionFilter("Account_Bes tRunJ_sold", Filter_Area); 
	
	// save the current measure filter selection into a global variable 
	// Note --> this global variable need to be set with the default values on the onInitialization event from the Main Canvas 
	CurrentMeasureFilterSelectionPopup = Filter_Area;
	CurrentMeasureSelection = Selected_Measures;
	
	// write the current measure filter selection to the browser console 
	console.log(["Measure Selection: ", CurrentMeasureSelection]); 
	console.log(["Measure Filter Selection: ", CurrentMeasureFilterSelectionPopup]);

    First, this script shows which value was selected and removes the measures of the measure groups. It then saves the current selection in the script variable CurrentMeasureGroup. The script filters on all the inputs given by showing the selected measures in the checkbox in the popup.

    After getting these values, all old filters get removed so the new ones can be applied. To get a valid filter, the selected measures get concatenated to a filter statement.

    Finally, the concatenated filter statement is saved in the script variable CurrentMeasureFilterSelectionPopup and the selected keys of the checkbox group in the script variable CurrentMeasureSelection.

  11. To write the script for the buttons OK and Cancel, in Outline hover over Popup_Settings, and choose . Enter the following script:
    Sample Code
    if (buttonId === "Ok_button") { 

// get Measures Selection 
var Selected_Measures = CheckboxGroup_Measure_Selection.getSelectedKeys(); 

if (CurrentMeasureSelection !== Selected_Measures) { 

	// remove the current measures from Chart 
	for (var i = 0; i < CurrentMeasureGroup.length; i++){ 
	Chart.removeMeasure(CurrentMeasureFilterSelectionPopup[i], Feed.ValueAxis); 
	} 
	
	// help variables 
	var Filter_Pattern_1 = "[Account_BestRunJ_sold].[parentId].&[";
	var Filter_Pattern_2 = "]"; 
	var Filter_Area = ArrayUtils.create(Type.string); 
	
	// loop over the seleced measures 
	for (i = 0; i < Selected_Measures.length; i++) { 
	
	// concate all selection information together to a valid filter statemant
		var Filter = Filter_Pattern_1 + CurrentMeasureGroup + Selected_Measures[i] + Filter_Pattern_2; 
		Filter_Area.push(Filter); 
	// add Measure to Chart 
	Chart.addMeasure(Filter, Feed.ValueAxis); 
	}
	
	// remove the "old" filter and set the new filter selection
	Table.getDataSource().removeDimensionFilter("Account_ BestRunJ_sold");
	Table.getDataSource().setDimensionFilter("Account_Bes tRunJ_sold", Filter_Area); 

	// save the current measure filter selection into a global variable 
	// Note --> this global variable need to be set with the default values on the onInitialization event from the Main Canvas 
	CurrentMeasureFilterSelectionPopup = Filter_Area; 
	CurrentMeasureSelection = Selected_Measures; 

	// write the current measure filter selection to the browser console 
	console.log(["Measure Selection: ", CurrentMeasureSelection]); 
	console.log(["Measure Filter Selection: ", CurrentMeasureFilterSelectionPopup]); 

}

// set the visibiltiy of Chart and Table --> Script from the RadioButtonGroup_View onSelect event 
var sel = RadioButtonGroup_View.getSelectedKey(); 

if (sel === 'Show Table') { 
	Table.setVisible(true); 
	Chart.setVisible(false); 
} 
else { 
	Table.setVisible(false); 
	Chart.setVisible(true); 
} 
}

Popup_Settings.close();

    This script starts off with an if statement that differentiates the buttons according to their IDs.

    It then gets the selections from the checkbox group in the popup window and removes the measures currently being used as filters for the chart.

    To get a valid filter, the selected measures are concatenated to a filter statement that is saved in the script variable CurrentMeasureFilterSelectionPopup and the selected keys of the checkbox group in the script variable CurrentMeasureSelection.

    Afterwards, the script gets the selected key of the radio button group in the popup window. If Show Table is selected, the table is set to visible and the chart to invisible, and vice versa if Show Chart is selected.

    Lastly, the popup window closes if you click on either of the buttons.

  12. Save the application or story, and open it in view time.

Results

When you run the analytic application or optimized story, it looks like this:

When you click on the settings icon, the popup window appears.

If you select Show Chart from the popup window and leave all the measures selected, the settings are left as they were, and the only change is that the chart is now displayed.

If you select the two items Actual and Plan from the checkbox in the popup window and change the dropdown selection to Discount, the chart looks like this:

If you switch back to the table via popup window, the previous filters and settings remain.