Best Practice: Filtering Table and Chart through Settings Panel (Popup Window)

Learn how to filter a table or a chart using a popup window.

Prerequisites

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

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

Context

Through a dropdown list in the canvas, you can filter your table and chart according to certain measures of the data set. These measures are Gross Margin, Discount, Quantity Sold and Original Sales Price.

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

Procedure

  1. Create and label the dropdown widget.
    1. Click Start of the navigation path (Add...) Next navigation step DropdownEnd of the navigation path and place this widget above your table.
    2. Change the name of the dropdown to Dropdown_MeasureGroup. You do this on the Styling panel under Analytics Designer Properties or on the Layout panel under Start of the navigation pathCanvas Next navigation step Dropdown_1 Next navigation step  More Next navigation step RenameEnd of the navigation path.
    3. To add a label to the dropdown, click Start of the navigation path (Add...) Next navigation step TextEnd of the navigation pathand place the text widget on the left side of the dropdown. Doubleclick the text widget and type Measures Group in it.
    4. Click Start of the navigation pathDesigner Next navigation step StylingEnd of the navigation path, and enter Dropdown_Measures_Label as name.
  2. Select which measures you can filter on in the dropdown.
    1. In your Canvas select the Dropdown_MeasureGroup widget and click Designer.
    2. Go to the Builder Panel and under Dropdown Value click (Add).
    3. Insert Gross_Margin as Value 1 and Gross Margin as dedicated text.
    4. Click (Add) and insert Discount as Value 1 and Discount as dedicated text.
    5. Click (Add) and insert Quantity_sold as Value 1 and Quantity Sold as dedicated text.
    6. Click (Add) and insert Original_Sales_Price as Value 1 and Original Sales Price as dedicated text.
    7. Click Apply to save the changes.
  3. To open the popup by clicking on a settings icon, add a settings icon as image.
    1. Click Start of the navigation path (Add...) Next navigation step ImageEnd of the navigation path and place it next to the dropdown.
    2. Optional: Click Upload Image and upload this image .
    3. On the Styling panel under Analytics Designer Properties change the name to Settings_Logo.
  4. In the Layout panel next to Popups click Start of the navigation path Add PopupEnd of the navigation path.
    1. Rename the popup to Popup_Settings by doubleclicking it in the Layout panel.
    2. In the Builder under Popup Settings toggle the Enable header & footer button to YES.
    3. Enter Settings as Title .
    4. Under Buttons add your two buttons OK and Cancel. Click and insert Ok_button as ID and OK as dedicated text. Then check all three displayed options Emphasized, Enabled and Visible.
    5. Click and insert Cancel_button as ID and Cancel as dedicated text. Then check the options Enabled and Visible.
    6. Click Apply to save the changes.
  5. To be able to switch between table and chart, add a radio button group widget. Click Start of the navigation path (Add...) Next navigation step Radio Button GroupEnd of the navigation path and place it in the middle of the popup window.
    1. In the Builder panel under Radio Button Group Value, insert Show Table as Value 1 and Show Chart as Value 2 and set Show Table as default.
    2. Click Apply to save the changes.
    3. Go to the Styling panel and under Analytics Designer Properties enter RadioButtonGroup_View as name and Vertical Layout as Display Option.
  6. To be able to choose the displayed measures, click Start of the navigation path (Add...) Next navigation step Checkbox GroupEnd of the navigation path and place the widget underneath the radio button group widget.
    1. Go to the Styling panel of the checkbox group and under Analytics Designer Properties enter CheckboxGroup_Measure_Selection as name and Vertical Layout as Display Option.
    2. Toggle Label Text and insert Measures.
    3. In the Builder panel under Checkbox Group Value, click twice to create Value 3 and Value 4.
    4. Insert Actual as Value 1 and Actual as dedicated Text. Insert Plan as Value 2 and Plan as dedicated Text. Insert _Abs as Value 3 and Absolute as dedicated Text. Insert _Percent as Value 4 and % Deviation as dedicated Text.
    5. Set all four values as Default and click Apply.
  7. Hover over the Settings_Logo in the Layout panel, click , and enter this script:
    Sample Code
    Popup_Settings.open();

    This onClick function opens the settings popup.

  8. Add three global script variables, to be able to access the selections from any widget.
    1. On the Layout panel under Scripting, click (Add Script Variable).
    2. In the Script Variable panel under Structure, enter CurrentMeasureFilterSelectionPopup as name, leave type as string, and toggle the Set As Array button to YES.
    3. To close the Script Variable panel, click Done.

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

    4. For the second script variable, repeat the substeps a to c but name it CurrentMeasureGroup and instead of toggling the Set As Array button to YES insert Gross_Margin as Default Value.

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

    5. For the third script variable, repeat the substeps a to c but name it CurrentMeasureSelection.

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

  9. To define what will happen when a filter is selected, create a script object. In this object, write a function that sets the measure filter according to what the user has chosen from the checkbox group.
    1. On the Layout panel under Scripting, click (Add Script Object).
    2. To rename the folder, hover over ScriptObject_1, click Start of the navigation path More Next navigation step RenameEnd of the navigation path, and enter Utils.
    3. To rename the function, hover over function1, click Start of the navigation path More Next navigation step RenameEnd of the navigation path, and enter setMeasureFilter.
    4. Click on the function setMeasureFilter, and when the Script Function panel opens, click (Add Argument).
    5. Enter selectedId as name of the argument, leave type as string, and click Done twice.
    6. To write the script for the function, hover over the setMeasureFilter function in the Layout panel, click , and enter this 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 Layout panel, hover over Dropdown_MeasureGroup, click , and enter this 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 CurrentMeasureSelection script variable.

  11. To write the script for the buttons OK and Cancel, hover over Popup_Settings in the Layout panel, click , and enter this 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 which 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 either of the buttons.

  12. Save the application and click Run Analytic Application.

Results

When you run the application, 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.