UAE: ✆+971 58 5616 616       USA: ✆+14099168409        ✉  info@insightcubes.com

Sort & Rank with SAP Analytics Designer

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

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

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

Rank tab components and code

Components of the Rank tab:

  1. Rank Orientation
  2. Dimensions
  3. Rank On Member
  4. Rank Top/Bottom
  5. Input Top N
  6. Reset
  7. 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);
	}
	}

2. Rank Dimension dropdown

Rank Dimensions Dropdown Code
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).

4. Rank order Top/Bottom dropdown

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.

Recent Posts

SAP Analytics Cloud Calendar

SAP Analytics Cloud Calendar Features and API’s Workflow Organization (Calendar) Use the SAP Analytics Cloud calendar to organize your workflows with calendar events: You can

SAP Analytics Cloud Data Connections

Live connection (Online) and Import connection (batch) with SAP Analytics Cloud SAP Analytics Cloud enables access to on-premise and cloud data sources using two main

Insightcubes Cloud Consolidation Extension for SAP Analytics Cloud – Landing Page

Landing Page Dashboard

InsightCubes’ SAP Analytics Cloud Consolidation Landing Page Dashboard is a navigational menu designed to provide easy access to all the Cloud Consolidation extension stories and

Get in Touch

Learn more and ask us About Our Cloud Consolidation Solution

Share This Post

Share this Page!

Share this with your network.

Want to Know More?

Get In Touch

Something isn’t Clear?


Feel free to contact us, and we will be more than happy to answer all of your questions.