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 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.
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);
}
}
2. Sort Dimensions dropdown
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
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);
}
}
2. Rank Dimension dropdown
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
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.
