Hide / Show dimensions on Tables and Charts in SAC...

skapitan

Preamble

This blog guides SAC application designers and engineers on a simple and effective solution for how to control which dimensions to be displayed on a table or chart in "view" or "present" modes. So that business user could have a flexible way to manually choose different dimensions within the same widget at run-time.

Lately I came across multiple questions in various SAP discussion boards on how to hide a particular dimension from a table, and it inspired me to start this blog to give some useful hints for achieving it.

Analysis

Let's take as an example a simple Sales Orders model, that gets consumed by the Sales Order table with the following three measures and four dimensions.

Model:

Table (view mode):

The goal is to have a possibility to hide any of four dimensions on this table in view mode.

With the new features, such as Optimized Design Experience and the ability to add script elements that consume SAC API to existing stories, it is possible to tweak standard behavior.

Solution approach

The idea of controlling dimension visibility in a table is to create a dropdown widget where all available dimensions would be stored and set up as shown or hidden. Then, the business user could manually set and define a particular dimension to be shown or hidden by selecting it from the dropdown list.

The following steps instruct how to make dimensions dynamic for a table widget. For a chart, it will be very similar, with some slight adjustments that will be covered in the end of this blog.

NOTE: This solution is not hardcoded, it is neither dependent on a number of defined dimensions nor on their type (it handles hierarchies as well). It can be taken as a add-on feature to implement dynamic dimensions at run-time.

Step1. Assuming the story structure is constructed as depicted below, add a new dropdown element (dropdownDimensionTable) into same page/lane along with the corresponding table (tableSalesOrders). No input values needed, just keep it empty from the scratch.

Step2. Create two new collections (as script variables) that will store dimension IDs and their description values.

Set both variables as an Array.

Step3. Initialize both variables and the dropdown list with dimension ID and Description defined in the table. The script below gets the job done. Put it into the table's page in onInitialization event.

// reset global dimensions collections
while (actualDimensionIdTable.length) { actualDimensionIdTable.pop(); }  // dimensions id
while (actualDimensionDescTable.length) { actualDimensionDescTable.pop(); }  // dimensions description

// list of all available dimension in the given table (tableSalesOrders)
var tableDimensionList = tableSalesOrders.getDimensionsOnRows();
var modelDimensionList = tableSalesOrders.getDataSource().getDimensions();

// loop through all available dimensions in chart
for (var i = 0; i < tableDimensionList.length; i++) {
	// populate global dimensions collections
	actualDimensionIdTable.push(tableDimensionList[i]);  // dimensions id
	for (var j = 0; j < modelDimensionList.length; j++) {  // dimensions description
		if (modelDimensionList[j].id === tableDimensionList[i]) {
			actualDimensionDescTable.push(modelDimensionList[j].description);
		}
	}
	// initiate dropdown items
	dropdownDimensionTable.addItem(actualDimensionDescTable[i]);
}

Step4. Create a script that implements main logic.

As user picks a particular dimension item from the dropdown list, this action will either

hide dimension that was visible before and add the status (HIDDEN)

or

show dimension that was invisible before

The script below handles this behavior. Put it into the dropdown's onSelect event.

var pickedDimension = dropdownDimensionTable.getSelectedKey();  // picked from dropdown
var dimensionIndex = actualDimensionDescTable.indexOf(pickedDimension);  // index in the collection
var position = 0;
var idx = 0;

// Show / Hide picked Dimension in the Table
if (pickedDimension.includes(' (HIDDEN)')) {  // show
	actualDimensionDescTable[dimensionIndex] = pickedDimension.replace(' (HIDDEN)','');
	for (idx = 0; idx < dimensionIndex; idx++) {
		if (!actualDimensionDescTable[idx].includes(' (HIDDEN)')) {
			position++;
		}
	}
	tableSalesOrders.addDimensionToRows(actualDimensionIdTable[dimensionIndex], position);
} else {  // hide
	actualDimensionDescTable[dimensionIndex] = pickedDimension + ' (HIDDEN)';
	tableSalesOrders.removeDimension(actualDimensionIdTable[dimensionIndex]);
}

// alway reset dropdown list in the end, with new (changed) items 
dropdownDimensionTable.removeAllItems();
for (idx = 0; idx < actualDimensionDescTable.length; idx++) {
	dropdownDimensionTable.addItem(actualDimensionDescTable[idx]);
}

Run the Report in view/present mode and note the dropdown list is automatically populated with all the dimensions.

Hide Sales Org Id and Partner Id by picking them from the drop down list. They will be removed from the dimension columns in the table and will be set with status (HIDDEN) in the dropdown list.

Hidden dimensions could be made visible by selecting them once again from dropdown list. Status (HIDDEN) will be unset and dimension column will be added into the table with the same position as it was defined in design builder.

Eg, make dimension Partner Id visible.

Adjustments for Charts

For charts, the solution is the same as for tables with only slight differences in steps 3 and 4: in addDimension and removeDimension use Feed as an extra parameter:

chartSalesOrders.addDimension(actualDimensionIdChart[dimensionIndex], Feed.CategoryAxis, position);

chartSalesOrders.removeDimension(actualDimensionIdChart[dimensionIndex], Feed.CategoryAxis);

Considering that for chart only two dimensions are defined - Sales Org and Partner Id, the dashboard looks like the following:

Pick Partner Id dimension from in the dropdown list to hide it from the chart:

Hide / Show dimensions on Tables and Charts in SAC in view / present mode