Best Practice: Filter Table and Chart through Cascaded Filtering

Learn how to filter on dimensions and then according to hierarchies (such as Flat Presentation, ABC, Category…) to choose how to display the data.

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: Switch between Chart and Table and can now enhance your analytic application.

Context

You add two dropdown lists, one for filtering dimension and the other for filtering hierarchies and depending on what dimension you choose to filter on, the Dropdown List for the hierarchies filters will change. There is always one consistent filter for hierarchies which is Flat Presentation and according to your chosen dimension, you might either only have that one or have more options. The different filters can be chosen by simply selecting them from the dropdown lists you added.

Procedure

Click (Add...) Dropdown and place this widget above your table.
Change the name of the dropdown to Dropdown_Dimensions. You do this on the Styling panel under Analytics Designer Properties or on the Layout panel under Canvas Dropdown_1 More Rename.
Click (Add...) Dropdown and place this widget above the table next to the other dropdown. Leave some space in between the dropdowns so you can add name labels for them later on.
Change the name of the dropdown to Dropdown_Hierarchies. You do this on the Styling panel under Analytics Designer Properties or on the Layout panel under Canvas Dropdown_1 More Rename.
Add the values you want to chose from in the widget.
1. In your Canvas select the Dropdown_Dimensions widget and click Designer.
2. b. Go to the Builder Panel and under Dropdown Value click .
3. Insert Location_4nm2e04531 as Value 1 and Location as dedicated text.
4. Click and insert Product_3e315003an as Value 1 and Product as dedicated text.
5. Click and insert Store_3z2g5g06m4 as Value 1 and Store as dedicated text.
6. Click and insertSales_Manager__5w3m5d06b5 as Value 1 and Sales Manager as dedicated text.
7. g. Set Location as default value.
8. Click Apply to save the changes.
To distinguish the dropdown lists create a label for each of them.
1. Click (Add...) Text and place it on the left side of the widget Dropdown_Dimensions.
2. Doubleclick the label and type Dimension.
3. Change the name of the text widget to Dropdown_Dimensions_Label. You do this on the Styling panel under Analytics Designer Properties or on the Layout panel under Canvas Text_1 More Rename.
4. Click (Add...) Text and place it on the left side of the widget Dropdown_Hierarchies.
5. Doubleclick the label and type Hierarchies.
6. Change the name of the text widget to Dropdown_Hierarchies_Label. You do this on the Styling panel under Analytics Designer Properties or on the Layout panel under Canvas Text_1 More Rename.
In the Scripting panel underneath the Layout panel via Script Variables add a scripting variable. Click Add Script Variable.
1. In the window that pops up under Structure change the name to CurrentDimension and select string as the Type.
2. To make the location appear as default value in the dropdown widget when you run the application insert Location_4nm2e04531 as Default Value.
3. Click Done to save the changes.
In the Canvas, hover over Dropdown_Hierarchiesand click . In the script editor insert this script as onSelect() function:
Sample Code
var sel = Dropdown_Hierarchies.getSelectedKey(); // set hierarchy for Table Table.getDataSource().setHierarchy(CurrentDimension, sel); // set hierarchy for Chart Chart.getDataSource().setHierarchy(CurrentDimension, sel);
To trigger the action of filtering when a choice is selected from the dropdown, you need an onSelect script.

This script gets the selected value of the dropdown list and accordingly sets the hierarchy of the table and the chart while referencing our script variable, CurrentDimension, so that the hierarchy displays only correctly filtered data.

In the Canvas, hover over Dropdown_Dimensionsand click

. In the script editor insert this script as onSelect() function:

Sample Code

var sel  = Dropdown_Dimensions.getSelectedKey(); 
 
// Table 
Table.removeDimension(CurrentDimension); 
Table.addDimensionToRows(sel); 
 
//Chart 
Chart.removeDimension(CurrentDimension, Feed.CategoryAxis); 
Chart.addDimension(sel, Feed.CategoryAxis); 
 
// write filter information into the browser console 
console.log( ['CurrentDimension: ', CurrentDimension ]); 
console.log( ['Selection: ', sel ]); 
 
// save the current selection (dimension) into a global variable 
CurrentDimension = sel; 
 
// get hierarchies from the current dimension 
var hierarchies = Table.getDataSource().getHierarchies(CurrentDimension); 
var flag = true; 
 
// remove all current items form the Dropdown_Hierarchies 
Dropdown_Hierarchies.removeAllItems(); 
 
// loop  
for (var i=0;i<hierarchies.length; i++){ 
 if (hierarchies[i].id === '__FLAT__') { 
 
 Dropdown_Hierarchies.addItem(hierarchi es[i].id, 'Flat Presentation'); 
 } 
 else { 
 
 Dropdown_Hierarchies.addItem(hierarchi es[i].id, hierarchies[i].description); 
  if (flag === true) { 
   var hierarchy = hierarchies[i].id; 
   flag = false; 
  } 
 } 
}    
// write hierarchy information to browser console 
console.log( ['Hierarchy: ', hierarchy ]); 
console.log( ['Current Dimension: ', CurrentDimension ]); 
 
// set Flat Hierarchie als Default 
Dropdown_Hierarchies.setSelectedKey('__FLAT__'); 
 
// Table 
Table.getDataSource().setHierarchy(CurrentDimension, '__FLAT__'); 
 
// Chart 
Chart.getDataSource().setHierarchy(CurrentDimension, '__FLAT__');

This script gets the selected choice from the dimensions dropdown and saves it in a variable called sel.

You remove all dimensions from table and chart and set the selected dimension as the new dimension.
You remove all hierarchies from table and chart. You get those hierarchies that are available for the selected dimension from the data and loop them over to the hierarchies dropdown list.
You set Flat Presentation as default hierarchy and filter table and chart with the selected dimension.

In the Layout panel hover over Canvas, click

and select onInitialization. Insert the function:

Sample Code

// get hierarchies from the current dimension 
var hierarchies = Table.getDataSource().getHierarchies(CurrentDimension); 
var flag = true; 
 
// loop  
for (var i=0;i<hierarchies.length; i++){ 
 if (hierarchies[i].id === '__FLAT__') { 
 
 Dropdown_Hierarchies.addItem(hierarchi es[i].id, 'Flat Presentation'); 
 } 
 else { 
 
 Dropdown_Hierarchies.addItem(hierarchi es[i].id, hierarchies[i].description); 
  if (flag === true) { 
   var hierarchy = hierarchies[i].id; 
   flag = false; 
  } 
 } 
}    
// write hierarchy information to browser console 
console.log( ['Hierarchy: ', hierarchy ]); 
console.log( ['Current Dimension: ', CurrentDimension ]); 
 
// set Flat Hierarchie als Default 
Dropdown_Hierarchies.setSelectedKey('__FL AT__'); 
 
//Table 
Table.getDataSource().setHierarchy(CurrentDimension, '__FLAT__'); 
 
//Chart 
Chart.getDataSource().setHierarchy(CurrentDimension, '__FLAT__');

In this use case, you want to make sure that on initialization you load all the available hierarchies of the dimensions and set Flat Presentation as the default of the hierarchies dropdown list. The script for this is the same as a part of what happens when a dimension is chosen.

Save the application and click Run Analytic Application.

Results

When you run the application, it looks like this:

If you keep the dimension on Location but change the hierarchy to States, the table would change to display the location according to the states you have.

If you change the dimension to Product and set the hierarchy to Category, you will see the different categories of products displayed.