Sort & Rank – SAP Analytics Designer

InsightCubes SAP Analytics Designer application sort and rank button

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

EPM Report – Sort Ascending

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

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

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

Table.sortByValue(selection,arg1,arg2);
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:

  1. Sort Direction
  2. Dimensions
  3. Sort On Member
  4. Selected Sort Order
  5. Reset
  6. 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);
	}
	}

2. Dimensions dropdown

Dimensions dropdown fx code
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);
			}	
	}	
}

3. Sort On Member dropdown

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).

4. Selected Sort Order dropdown

Add the Selected Sort Order dropdown

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

Apply Sort Button on click fx code
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

EPM Report – Rank Top 2

Recent Posts

Get in Touch

Learn more and ask us About Our Cloud Consolidation Solution

Share This Post