Best Practice: Adding and Removing Dimensions in Tables

Learn how to control which measures and dimensions are displayed in the table by adding and removing rows and columns.

Prerequisites

You've already added a table and renamed it to Table.
To follow this sample use case, use the model BestRun_Advanced as data source.

Context

To add and remove dimensions and measures in a table, you add 5 Checkbox Groups.

The first one displays all the available measures so you can choose which ones you want to see in the table.
The second one displays the dimensions you want the columns to be filtered on.
The third one displays the dimensions you want the rows to be filtered on.
The fourth one displays the dimensions that you could add to the second and third Checkbox.
The fifth one is invisible at view time and only needed to sort dimensions.

On top of that, you add seven buttons for easy access to the checkbox group's measures and to shift them from one checkbox to another.

Procedure

Create and label the first checkbox group.
1. Click (Add...) Checkbox Group and place this widget on the left side of your table. Leave some space above so you can add labels and buttons later on.
2. Go to the Styling panel and under Analytics Designer Properties change the name of the checkbox group to CheckboxGroup_Measures.
3. Choose Vertical Layout as Display Option.
4. Remove the initial values Value 1 and Value 2 from the checkbox group value list in the Builder panel: Select these two values one after the other, click , and then click Apply.
5. To add a label to the checkbox, click (Add...) Textand place the text widget above of the checkbox group. Doubleclick the text widget and type Measures in it.
6. Click Designer Styling, and enter CheckboxGroup_Measures_Label as name.
7. Optional: Resize the text widget.
To create and label the second checkbox group, repeat the steps 1a-g. Keep some space between label and checkbox for your buttons. Name the checkbox group CheckboxGroup_Columns and its label CheckboxGroup_Columns_Label and enter Columns as text in the label.
To create and label the third checkbox group, repeat the steps 1a-g. Keep some space between label and checkbox for your buttons. Name the checkbox group CheckboxGroup_Rows and its label CheckboxGroup_Rows_Label and enter Rows as text in the label.
To create and label the fourth checkbox group, repeat the steps 1a-g. Keep some space between label and checkbox for your buttons. Name the checkbox group CheckboxGroup_Free and its label CheckboxGroup_Free_Label and enter Free as text in the label.
Since the last checkbox group is invisible it does not need a label and can be placed anywhere in the canvas,for example on the right. Repeat the steps 1a-d and name the widget CheckboxGroup_AllDimensions.
1. Go to the Styling panel and under Actions, deselect Show this item at view time.
For easy access to the checkbox group's measures, add the seven buttons mentioned above.
1. Click (Add...) Button and place it between the measures label and its checkbox group.
2. Repeat the step above for six other buttons. Place two of them on the right of the first button, one next to the columns label, one next to the Rows label and two next to the free label.
3. Select the first button, click Designer Styling, and enter Button_setMeasureFilter as name and set selected as text. This button sets the measures you choose from the measures checkbox as measures for the table.
4. Select the second button, click Designer Styling, and enter Button_removeAllMeasures as name and remove all as text. This button unchecks all the measures from the measures checkbox and sets the measure filters for the table to empty.
5. Select the third button, click Designer Styling, and enter Button_setAllMeasures as name and set all as text. This button sets all available measures as measures for the table.
6. Select the forth button, next to the columns label, click Designer Styling, and enter Button_ColRemove as name and Remove as text. This button removes the (in the columns checkbox) selected dimensions from the checkbox as well as from the columns of our table.
7. Select the fifth button, next to the rows label, click Designer Styling, and enter Button_RowRemove as name and Remove as text. This button removes the (in the rows checkbox) selected dimensions from that checkbox as well as the rows of the table.
8. Select the sixth button, next to the free label, click Designer Styling, and enter Button_AddToCol as name and add to Column as text. This button adds the in the free checkbox selected dimensions as dimensions to the columns of the table.
9. Select the last button, next to the previous one, click Designer Styling, and enter Button_AddToRow as name and add to Row as text. This button adds the in the free checkbox selected dimensions as dimensions to the rows in the table.
To access the values users select in the checkbox group, create five script variables that act as global variables, which can be accessed from anywhere in your application:
- The first script variable, called AllDimensions, is set as an array and holds all the dimensions in the data set.
- The second script variable, called AllMeasures, is set as an array and holds all the measures that users can select in the checkbox group.
- The third script variable, called CurrentDimensionColumn, holds the selected dimensions to implement them in columns and rows.
- The fourth script variable, called CurrentDimensionRows, holds the selected dimensions,you have chosen to add to the rows.
- The fifth script variable, called CurrentMeasureFilterSelection, holds the measures you have selected from the measures checkbox group.
1. On the Layout panel under Scripting, click (Add Script Variable).
2. In the Script Variable panel under Structure, enter AllDimensions as name, leave type as string, and toggle the Set As Array button to YES.
3. To close the Script Variable panel, click Done.
4. For the second script variable, repeat the substeps a to c and name it AllMeasures.
5. For the third script variable, repeat the substeps a to c and name it CurrentDimensionColumn.
6. For the fourth script variable, repeat the substeps a to c and name it CurrentDimensionRows.
7. For the fifth script variable, repeat the substeps a to c and name it CurrentMeasureFilterSelection.

To define what will happen when users select a filter value in one of the checkbox groups, 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.

On the Layout panel under Scripting, click (Add Script Object).
Add a second function by hovering over ScriptObject_1, then click More Add Script Function.
To rename the folder, hover over ScriptObject_1, click More Rename, and enter Utils.
To rename the first function, hover over function1, click More Rename, and enter setDimensionCheckboxes.
To rename the second function, hover over function2, click More Rename, and enter setMeasureFilter.
Click on the function setMeasureFilter, and when the Script Function panel opens, click (Add Argument).
Enter selectedIds as name of the argument, leave type as string, toggle the Set As Array button to YES, and click Done twice.

To write the script for the first function, hover over the setDimensionCheckboxes function in the Layout panel, click

, and enter this script in the script editor:

Sample Code

CheckboxGroup_Columns.removeAllItems();
CheckboxGroup_Rows.removeAllItems();
CheckboxGroup_Free.removeAllItems();

CurrentDimensionColumn = ArrayUtils.create(Type.string);
CurrentDimensionRows = ArrayUtils.create(Type.string);
console.log(["CurrentDimensionColumn should empty",
CurrentDimensionColumn.slice()]); console.log(["CurrentDimensionRows should empty", CurrentDimensionRows.slice()]);

// Dimension in Columns
var dimCol = Table.getDimensionsOnColumns();
if (dimCol.length > 0) {
	for (var i=0;i<dimCol.length; i++){ 	
	CurrentDimensionColumn.push(dimCol[i]);
	console.log(["CurrentDimensionColumn ", dimCol[i]]);
	}
}


// Dimension in Rows
var dimRows = Table.getDimensionsOnRows();
if (dimRows.length > 0) {
	for (i=0;i<dimRows.length; i++){
	CurrentDimensionRows.push(dimRows[i]);
console.log(["CurrentDimensionRows ",
dimRows[i]]);
	}
}	

// get all Dimensions
if (AllDimensions.length > 0) {
	for (i=0;i<AllDimensions.length; i++){
		if (AllDimensions[i] !== "") {
		CheckboxGroup_AllDimensions.setSelectedKeys([AllDimensions[i]]);
		var dimdesc = CheckboxGroup_AllDimensions.getSelectedTexts();
		CheckboxGroup_Free.addItem(AllDimensions[i],dimdesc[0]);
		console.log(["AllDimensions",AllDimensions[i], dimdesc[0]]);
		}
	}
}

console.log(["CurrentDimensionColumn",CurrentDimensionColumn]);
console.log(["CurrentDimensionRows",CurrentDimensionRows]);

// remove the dimensions from the free list, which are in rows / columns
if (CurrentDimensionRows.length > 0) {
	for (i=0;i<CurrentDimensionRows.length;i++){
		if (CurrentDimensionRows[i] !== "") {
			CheckboxGroup_Free.setSelectedKeys([CurrentDimensionRows[i]]);
			dimdesc = CheckboxGroup_Free.getSelectedTexts();
			CheckboxGroup_Rows.addItem(CurrentDimensionRows[i],dimdesc[0]);
			CheckboxGroup_Free.removeItem(CurrentDimensionRows[i]);
		}
	}
}

if (CurrentDimensionColumn.length > 0) {
	for (i=0;i<CurrentDimensionColumn.length;i++){
		if (CurrentDimensionColumn[i] !== "") {
		CheckboxGroup_Free.setSelectedKeys([CurrentDimensionColumn[i]]);
		dimdesc = CheckboxGroup_Free.getSelectedTexts();
		CheckboxGroup_Columns.addItem(CurrentDimensionColumn[i],dimdesc[0]);
		CheckboxGroup_Free.removeItem(CurrentDimensionColumn[i]);
		}
	}
}

To write the script for the second function, hover over the setMeasureFilter function in the Layout panel, click

, and enter this script in the script editor:

Sample Code

// remove Measures
Table.getDataSource().removeDimensionFilter("Account_BestRunJ_sold");

// add Measures
Table.getDataSource().setDimensionFilter("Account_BestRunJ_sold",selectedIds);

// save the current selection into global variable
CurrentMeasureFilterSelection = selectedIds;

With these two scripts (substep h and substep i) you define what happens to the table when users select filter values in the checkbox groups:

You remove any already set dimensions of the table or measures of the columns, rows, and free checkboxes
You add the captured values the users have selected in the checkbox group as new dimensions of the table and as new measures of the chart.
You take the currently selected measures and save them in the script variable CurrentMeasureFilterSelection.

Define what will happen when users click the buttons you created.
1. Hover over the setMeasureFilter button in the Layout panel, click , and enter this script:
  Sample Code
  
  Utils.setMeasureFilter(CheckboxGroup_Measures.getSelectedKeys());
  This onClick function script calls the Utils.setMeasureFilter function and passes to it the selected measures of the checkbox group.
2. Hover over the removeAllMeasures button in the Layout panel, click , and enter this script:
  Sample Code
  
  CheckboxGroup_Measures.setSelectedKeys([""]); Utils.setMeasureFilter([""]);
  This onClick function script removes all the selected measures from the checkbox group itself and passes an empty array to the Utils.setMeasureFilter that updates your table.
3. Hover over the setAllMeasures button in the Layout panel, click , and enter this script:
  Sample Code
  
  CheckboxGroup_Measures.setSelectedKeys(AllMeasures); Utils.setMeasureFilter(AllMeasures);
  This onClick function script sets the selected keys of the checkbox group to the AllMeasures script variable you defined before and passes the same variable to the Utils.setMeasureFilter function.
4. Hover over the ColRemove button in the Layout panel, click , and enter this script:
  Sample Code
  
  var selKeys = CheckboxGroup_Columns.getSelectedKeys(); for (var i=0;i<selKeys.length; i++){ // remove dimension Table.removeDimension(selKeys[i]); } Utils.setDimensionCheckboxes();
  This onClick function script gets the selected keys of the columns checkbox group and then removes these dimensions from the table. It then calls the setDimensionCheckboxes function to set the checkboxes according to the new selections.
5. Hover over the RowRemove button in the Layout panel, click , and enter this script:
  Sample Code
  
  var selKeys = CheckboxGroup_Rows.getSelectedKeys(); for (var i=0;i<selKeys.length; i++){ // remove dimension Table.removeDimension(selKeys[i]); } Utils.setDimensionCheckboxes();
  This onClick function script gets the selected keys of the rows checkbox group and then removes these dimensions from the table and calls the setDimensionCheckboxes function to reset the checkboxes again according to the new selections.
6. Hover over the AddtoCol button in the Layout panel, click , and enter this script:
  Sample Code
  
  var selKeys = CheckboxGroup_Free.getSelectedKeys(); for (var i=0;i<selKeys.length; i++){ // add dimension to Column in table Table.addDimensionToColumns(selKeys[i]); } Utils.setDimensionCheckboxes();
  This onClick function script gets the selected keys of the free checkbox and adds the dimensions to the column of the table.The script will then call the setDimensionCheckboxesfunction to set the checkboxes to the new selection.
7. Hover over the AddtoRow button in the Layout panel, click , and enter this script:
  Sample Code
  
  var selKeys = CheckboxGroup_Free.getSelectedKeys(); for (var i=0;i<selKeys.length; i++){ // remove dimension Table.addDimensionToRows(selKeys[i]); } Utils.setDimensionCheckboxes();
  This onClick function script gets the selected keys of the free checkbox and adds the dimensions to the Rows of the table, and then, same as the previous script, it will call the setDimensionCheckboxes function to set the Checkboxes to the new selection.
Define what will happen when the analytic application is first run, by creating the onInitialization function of the canvas itself.
1. Hover over the Canvas in the Layout panel, click , and select onInitialization.
2. Enter this script in the script editor:
  Sample Code
  
  // Measures // get all measures from the table data source var measures = Table.getDataSource().getMeasures(); // define array or the selected Keys var selectedKeys = ArrayUtils.create(Type.string); if (measures.length > 0) { for (var i=0;i<measures.length; i++){ // add the Measure to checkbox group CheckboxGroup_Measures.addItem(measures[i].id,measures[i].description); //add the measure to the selected Keys selectedKeys.push(measures[i].id); } } CheckboxGroup_Measures.setSelectedKeys(selectedKeys); console.log(["selectedKey ", selectedKeys]); AllMeasures = selectedKeys; // define array or the selected Keys var selectedDims = ArrayUtils.create(Type.string); var dims = Table.getDataSource().getDimensions(); if (dims.length > 0) { for (i=0;i<dims.length; i++){ CheckboxGroup_AllDimensions.addItem(dims[i ].id,dims[i].description); selectedDims.push(dims[i].id); } } console.log(["selectedDims ", selectedDims]); AllDimensions = selectedDims; Utils.setMeasureFilter(selectedKeys); Utils.setDimensionCheckboxes();
  With this script you make sure that on initialization, you get all the available measures of the table's data source.
  
  You define a selected keys array of type string and, using a loop, you add the measures to your measures checkbox group and the selected keys array. You set the selected keys of the checkbox group to the selectedKeys variable and set your script variable AllMeasures to selectedKeys since it still holds all the measures of your data set.
  
  Afterwards, you define another string array and put all the dimensions of the data source in it as well as add these dimensions as items of the checkbox group of all dimensions CheckboxGroup_AllDimensions.
  
  Next, you set the script variable AllDimensions to the string array selectedDims that you created to store the dimensions in.
  
  Finally you call the functions of setMeasureFilter to set the selected keys to the array selectedKeys and to call the setDimensionCheckboxes function to set the dimension checkboxes to its initial state.
Save the application and click Run Analytic Application.

Results

When you run the application, it looks like this:

If you select Time in the Free Checkbox and click add to Column, this dimension gets added to the table columns and you can see in detail what happened in which year.

If you select Location and click add to Row, you will see the columns being filtered on time and the rows on location.

You get back to starting condition by using the Remove buttons of the Column and Row Checkboxes.

Note

You can't remove all the dimensions from the Columns because it must get filtered on at least one dimension.