Sort & Rank with SAP Analytics Designer

Dina Merhebi

2 years ago

How to sort and rank reports with SAP Analytics Designer

Sort/Rank reports in SAP Analytics Cloud

The InsightCubes Cloud Consolidation solution provides users with sophisticated and flexible Sort & Rank capabilities to navigate their data sets. Sorting & Ranking with SAP Analytics Designer for the EPM report allows you to sorts all your values “horizontally/vertically” and “ascending/descending”, the Sort & Rank button will read form the analytics application filtering selections and feed the Dimensions dropdown with the current dimension selection in rows, then populates all the selected dimension’s members in the Sort On Member dropdown, after the member is selected, chose the desired sorting order “ascending/descending” and Apply Sort, the sort order is applied to the report.

Sorting with SAP Analytics Designer

Sort/Rank with SAP Analytics Designer

Create three Script Variables:

CurrentDimensionColumn, set Type to String and set as Array.
CurrentDimensionRows, set Type to String and set as Array.
SortSelection set Type to Selection and keep Set As Array deactivated.

Add a new element in Script Objects call it Utils; create a function call it sortUtil, set the Return Type to Void, add two arguments, name the first arg1 set its Type to SortOrder, keep the Set As Array deactivated. Create another argument and call it arg2, set it Type to Direction keep the Set As Array deactivated. Then add the following code to the Script Object’s fx

Sort & Rank button in the Global Filter Pane

Sort & Rank Button in the Global filters pane that opens the sort and rank tab with a popup behavior.

This panel on the left includes, like all other dashboards switching between being a report and an input template, exporting to PDF, Excel and CSV, sorting and ranking values, saving global and private bookmarks and adjusting the setting of the interface.

Add the following code to the Sort & Rank on button click fx

Utils_SideCanvas.setContent(5);

var dimensionsinrow = CurrentDimensionRows;
	for(var a=0;a<dimensionsinrow.length;a++){
		DD_SortingDimension.addItem(dimensionsinrow[a]);
		DD_SortingDimension.setSelectedKey(dimensionsinrow[0]);
		DD_RankDimension.addItem(dimensionsinrow[a]);
		DD_RankDimension.setSelectedKey(dimensionsinrow[0]);
	}


var resultSet = Table.getDataSource().getResultSet();
var selecteddimension = dimensionsinrow[0];
for(var i = 0 ; i < resultSet.length; i++)
	{
		var result = resultSet[i]; 
		for (var dimensionId in result) {
			var memberId = result[dimensionId];	
			if (dimensionId === selecteddimension) {
				DD_SortMember.addItem(memberId.id,memberId.description);
				DD_RankMember.addItem(memberId.id,memberId.description);
				DD_SortMember.setSelectedKey(memberId.id);
				DD_RankMember.setSelectedKey(memberId.id);
				}
		}	
}

Sort tab components and code

Sort Tab popup after sort rank button click

1. Sort Direction Radio Button Group

Components of the Sort tab:

Sort Direction
Dimensions
Sort On Member
Selected Sort Order
Reset
Apply Sort

Manually fill the Radio Button Group “Direction.Horizontal/Direction.Vertical” in ID input field.

Add the below code to the Sort Direction Radio Button Group on select fx

DD_SortingDimension.removeAllItems();

var sortdirection=this.getSelectedKey();

if(sortdirection==="Direction.Horizontal"){
var dimensionsinrow = CurrentDimensionRows;
	for(var a=0;a<dimensionsinrow.length;a++){
		var rowdimension=dimensionsinrow[a];
			DD_SortingDimension.addItem(rowdimension);
	}
	}
else {
	var dimensionsincolumn = CurrentDimensionColumn;
	for(var c=0;c<dimensionsincolumn.length;c++){
		var columndimension=dimensionsincolumn[c];
			DD_SortingDimension.addItem(columndimension);
	}
	}

DD_SortMember.removeAllItems();

var resultSet = Table.getDataSource().getResultSet();
var selecteddimension = this.getSelectedKey();
for(var i = 0 ; i < resultSet.length; i++)
	{
		var result = resultSet[i]; 
		for (var dimensionId in result) {
			var memberId = result[dimensionId];	
			if (dimensionId === selecteddimension) {
				DD_SortMember.addItem(memberId.id,memberId.description);
			}	
	}	
}

Create the Sort on Member dropdown, the code for this dropdown is already added to the Sort & Rank Button on click (line 22 and 24), the Dimensions dropdown (line 1 and 11) and the sortUtil Script Objects (line 2) and on the Apply Sort button (line 2).

You can find the additional code for this dropdown in Apply Sort on button click (line 3) and in sortUtil Script Objects fx (line 3)

5. Reset button

Add the code above to the on click event for the reset button

6. Apply Sort button

var sortingdimension = DD_SortingDimension.getSelectedKey();
var Sortingmember=DD_SortMember.getSelectedKey();
var sortingorder = DD_SortOrder.getSelectedKey();
var sortingdirection = RBG_SortDirection.getSelectedKey();

console.log(sortingdirection);

//what is in my data grid... my data grid is composed on columns, rows and story filter
var selection = ({
	[sortingdimension]:Sortingmember,
	"@MeasureDimension":"AMOUNT"
});

if (sortingorder === "SortOrder.Ascending" && sortingdirection ==="Direction.Horizontal") {
Table.sortByValue(selection,SortOrder.Ascending,Direction.Horizontal);
}
else if (sortingorder === "SortOrder.Descending" && sortingdirection ==="Direction.Horizontal") {
Table.sortByValue(selection,SortOrder.Descending,Direction.Horizontal);
}
else if (sortingorder === "SortOrder.Default" && sortingdirection ==="Direction.Horizontal"){
	Table.sortByValue(selection,SortOrder.Default,Direction.Horizontal);
}

if (sortingorder === "SortOrder.Ascending" && sortingdirection ==="Direction.Vertical") {
Table.sortByValue(selection,SortOrder.Ascending,Direction.Vertical);
}
else if (sortingorder === "SortOrder.Descending" && sortingdirection ==="Direction.Vertical") {
Table.sortByValue(selection,SortOrder.Descending,Direction.Vertical);
}
else if (sortingorder === "SortOrder.Default" && sortingdirection ==="Direction.Vertical"){
	Table.sortByValue(selection,SortOrder.Default,Direction.Vertical);
}

End Sort, now you can sort your report with SAP Analytics Designer.

Ranking (Top N) with SAP Analytics Designer

Rank tab components and code

Components of the Rank tab:

Rank Orientation
Dimensions
Rank On Member
Rank Top/Bottom
Input Top N
Reset
Apply Rank

1. Rank Orientation Radio Button Group

Manually fill the Radio Button Group “Direction.Horizontal/Direction.Vertical” in ID input field.

Add the below code to the Rank Direction Radio Button Group on select fx

DD_RankDimension.removeAllItems();

var rankdirection=this.getSelectedKey();

if(rankdirection==="Direction.Horizontal"){
var dimensionsinrow = CurrentDimensionRows;
	for(var a=0;a<dimensionsinrow.length;a++){
		var rowdimension=dimensionsinrow[a];
			DD_RankDimension.addItem(rowdimension);
	}
	}
else {
	var dimensionsincolumn = CurrentDimensionColumn;
	for(var c=0;c<dimensionsincolumn.length;c++){
		var columndimension=dimensionsincolumn[c];
			DD_RankDimension.addItem(columndimension);
	}
	}

DD_RankMember.removeAllItems();

var resultSet = Table.getDataSource().getResultSet();
var selecteddimension = this.getSelectedKey();
for(var i = 0 ; i < resultSet.length; i++)
	{
		var result = resultSet[i]; 
		for (var dimensionId in result) {
			var memberId = result[dimensionId];	
			if (dimensionId === selecteddimension) {
				DD_RankMember.addItem(memberId.id,memberId.description);
			}	
	}	
}

3. Rank on Member

Create the Rank on Member dropdown, the code for this dropdown is already added to the Sort & Rank Button on click (line 23 and 26), the Dimensions dropdown (line 8 and 9) and on the Apply Sort button (line 5).

Manually fill the Dropdown ID’s as shown above

The apply order code for Rank Top/Bottom dropdown is on the Apply Rank button (Line 1) found below in section 7.

5. Input Top N

Input Top N code can be found in the Apply Rank button (Line 4) found below in section 7.

6. Reset button

7. Apply Rank button

Apply Rank onClick fx

var order = DD_RankOrder.getSelectedKey();
var directionrank = RBG_RankDirection.getSelectedKey();
var rankingdimension = DD_RankDimension.getSelectedKey();
var number = Inputfield_RankTopN.getValue();
var rankingmember = DD_RankMember.getSelectedKey();
console.log(rankingmember);

var selection = ({
	[rankingdimension]:rankingmember,
	"@MeasureDimension":"AMOUNT"
	});

console.log(selection);

if (order === "Top" && directionrank ==="Direction.Horizontal") {
Table.rankBy({
	applyToEachDimension: false,
	direction: Direction.Horizontal, // or Direction.Vertical
    rankOrder: RankOrder.Top,   // or RankOrder.bottom
    relatedDimensions: selection,
	value: Number.parseInt(number)
});
}
else if (order === "Bottom" && directionrank ==="Direction.Horizontal") {
Table.rankBy({
	
	applyToEachDimension: false,
	direction: Direction.Horizontal, // or Direction.Vertical
    rankOrder: RankOrder.Bottom,   // or RankOrder.top
    relatedDimensions: selection,
	value: Number.parseInt(number)
});
}

if (order === "Top" && directionrank ==="Direction.Vertical") {
Table.rankBy({
	
	applyToEachDimension: false,
	direction: Direction.Vertical, // or Direction.Horizontal
    rankOrder: RankOrder.Top,   // or RankOrder.bottom
    relatedDimensions: selection,
	value: Number.parseInt(number)
});
}
else if (order === "Bottom" && directionrank ==="Direction.Vertical") {
Table.rankBy({
	
	applyToEachDimension: false,
	direction: Direction.Vertical, // or Direction.Horizontal
    rankOrder: RankOrder.Bottom,   // or RankOrder.top
    relatedDimensions: selection,
	value: Number.parseInt(number)
});
}

Now you are able to Sort and Rank your reports with SAP Analytics Designer.