Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
Karol-K
Product and Topic Expert
Product and Topic Expert

Purpose

You want to get top X from result set based on some measure.

Background

Design Studio does not support yet Top / Bottom X out of the box (BEx Conditions) and for non BW Data Sources sometimes you do not have access..

Demo as Animated GIF (click to animate)

Idea

The idea is based on two functions in design studio:

1) getMembers()

2) getDataCell()

In short, we can ask the detasource for all members.

Then by loop on all members (of course you should not have a query of millions data entries..) and ask for a data cell. the data cell will be returned always (if read mode is booked values) or will be empty (read mode all).

with simple comparison you can filter out the top5 as in example.

Bottom5 can be achieved in similar way, just the checks must be changed.

Script


var allMembers = DS_1.getMembers("0BC_PERS1", 9999);
// initialization of all variables
var top1 = 0.0;
var top2 = 0.0;
var top3 = 0.0;
var top4 = 0.0;
var top5 = 0.0;
var top1CellInternalKey = "";
var top2CellInternalKey = "";
var top3CellInternalKey = "";
var top4CellInternalKey = "";
var top5CellInternalKey = "";
var top1CellMemberDescription = "";
var top2CellMemberDescription = "";
var top3CellMemberDescription = "";
var top4CellMemberDescription = "";
var top5CellMemberDescription = "";
allMembers.forEach(function(member, index) {
  var memberKey = member.internalKey;
  var memberText = member.text;
 
  var dataCell = DS_1.getData("4FW8C4WXM3HULQ4M1YPFT79EF", {
  "0BC_PERS1": memberKey
  });
 
  if(dataCell.formattedValue != "") {
  var value = dataCell.value;
  // if bigger, move all
  if(value > top1) {
  top5 = top4;
  top4 = top3;
  top3 = top2;
  top2 = top1;
  top1 = value;
  top5CellInternalKey = top4CellInternalKey;
  top4CellInternalKey = top3CellInternalKey;
  top3CellInternalKey = top2CellInternalKey;
  top2CellInternalKey = top1CellInternalKey;
  top1CellInternalKey = memberKey;
  top5CellMemberDescription = top4CellMemberDescription;
  top4CellMemberDescription = top3CellMemberDescription;
  top3CellMemberDescription = top2CellMemberDescription;
  top2CellMemberDescription = top1CellMemberDescription;
  top1CellMemberDescription = memberText;
  }
  // if bigger, move all
  else if(value > top2) {
  top5 = top4;
  top4 = top3;
  top3 = top2;
  top2 = value;
  top5CellInternalKey = top4CellInternalKey;
  top4CellInternalKey = top3CellInternalKey;
  top3CellInternalKey = top2CellInternalKey;
  top2CellInternalKey = memberKey;
  top5CellMemberDescription = top4CellMemberDescription;
  top4CellMemberDescription = top3CellMemberDescription;
  top3CellMemberDescription = top2CellMemberDescription;
  top2CellMemberDescription = memberText;
  }
  // if bigger, move all
  else if(value > top3) {
  top5 = top4;
  top4 = top3;
  top3 = value;
  top5CellInternalKey = top4CellInternalKey;
  top4CellInternalKey = top3CellInternalKey;
  top3CellInternalKey = memberKey;
  top5CellMemberDescription = top4CellMemberDescription;
  top4CellMemberDescription = top3CellMemberDescription;
  top3CellMemberDescription = memberText;
  }
  // if bigger, move all
  else if(value > top4) {
  top5 = top4;
  top4 = value;
  top5CellInternalKey = top4CellInternalKey;
  top4CellInternalKey = memberKey;
  top5CellMemberDescription = top4CellMemberDescription;
  top4CellMemberDescription = memberText;
  }
  // if bigger, move all
  else if(value > top5) {
  top5 = value;
  top5CellInternalKey = memberKey;
  top5CellMemberDescription = memberText;
  }
  }
});
LISTBOX_1.removeAllItems();
if(top1 > 0.0) {
  dataCell = DS_1.getData("4FW8C4WXM3HULQ4M1YPFT79EF", {
    "0BC_PERS1": top1CellInternalKey
  });
 
  LISTBOX_1.addItem(top1CellInternalKey, top1CellMemberDescription + ": " + dataCell.formattedValue + " ( float: " + Convert.floatToString(top1) + ") ");
}
if(top2 > 0.0) {
  dataCell = DS_1.getData("4FW8C4WXM3HULQ4M1YPFT79EF", {
    "0BC_PERS1": top2CellInternalKey
  });
 
  LISTBOX_1.addItem(top2CellInternalKey, top2CellMemberDescription + ": " + dataCell.formattedValue + " ( float: " + Convert.floatToString(top2) + ") ");
}
if(top3 > 0.0) {
  dataCell = DS_1.getData("4FW8C4WXM3HULQ4M1YPFT79EF", {
    "0BC_PERS1": top3CellInternalKey
  });
 
  LISTBOX_1.addItem(top3CellInternalKey, top3CellMemberDescription + ": " + dataCell.formattedValue + " ( float: " + Convert.floatToString(top3) + ") ");
}
if(top4 > 0.0) {
  dataCell = DS_1.getData("4FW8C4WXM3HULQ4M1YPFT79EF", {
    "0BC_PERS1": top4CellInternalKey
  });
 
  LISTBOX_1.addItem(top4CellInternalKey, top4CellMemberDescription + ": " + dataCell.formattedValue + " ( float: " + Convert.floatToString(top4) + ") ");
}
if(top5 > 0.0) {
  dataCell = DS_1.getData("4FW8C4WXM3HULQ4M1YPFT79EF", {
    "0BC_PERS1": top5CellInternalKey
  });
 
  LISTBOX_1.addItem(top5CellInternalKey, top5CellMemberDescription + ": " + dataCell.formattedValue + " ( float: " + Convert.floatToString(top5) + ") ");
}

Result

in my case I just push all known details to listbox.


Application

You can download the application from:

DesignStudioBiAppRepository/SCN_TOP5_SCTIPT at master · KarolKalisz/DesignStudioBiAppRepository · Gi...


Summary

I know that this is not the "best possible code", but it will work in DS 1.3 SP1 (tested there).

have fun.

13 Comments