Skip to Content

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)

/wp-content/uploads/2014/09/20140912_150922_capture_539803.gif

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 · GitHub


Summary

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

have fun.

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Mike Howles

    I’m hoping that we get Arrays soon for BIAL.  I feel like I’m on my TI-BASIC computer from 1983 without them.  I mean I know you cannot help it, but this code scales very poorly as you know.  Imagine wanting Top 10?  My goodness it’s just so unconventional to do this in this way.

    (0) 
    1. Karol Kalisz Post author

      I am fully with you – BIAL is not (yet) a programming language so some functions are still missing… I hope as well that those can come soon, but this requires extension in the xtext logic which are not so easy..

      (0) 
  2. Kristi Sigler

    Great post!  I was able to get this working as outlined.  However, is it possible to get the TOP X for a dimension if a hierarchy is activated and the hierarchy texts and associated values are needed?

    (0) 
  3. Antonio Minkov

    Hi Karol,

    I have the Top N working but for some reason I can’t get the Bottom N to work. Would you be able to have a look at the checks and let me know what I’m doing wrong?

    var allMembers = DS_2.getMembers(“OBJ_87”, 9999); 

    var bot1 = 0.0; 

    var bot2 = 0.0; 

    var bot3 = 0.0; 

    var bot4 = 0.0; 

    var bot5 = 0.0; 

    var bot1CellInternalKey = “”; 

    var bot2CellInternalKey = “”; 

    var bot3CellInternalKey = “”; 

    var bot4CellInternalKey = “”; 

    var bot5CellInternalKey = “”; 

    allMembers.forEach(function(member, index) { 

      var memberKey = member.internalKey;

      var memberText = member.text; 

      var dataCells = DS_2.getData(“OBJ_284”, { 

      “OBJ_87”: memberKey 

      }); 

       

      if(dataCells.formattedValue != “”) { 

      var value = dataCells.value; 

     

      if(value < bot1) { 

      /*

      * bottom4

      * bottom3

      * bottom2

      * bottom1

      * value

      */

      bot5 = bot4;

      bot4 = bot3;

      bot3 = bot2;

      bot2 = bot1;

      bot1 = value;

      bot5CellInternalKey = bot4CellInternalKey; 

      bot4CellInternalKey = bot3CellInternalKey; 

      bot3CellInternalKey = bot2CellInternalKey; 

      bot2CellInternalKey = bot1CellInternalKey; 

      bot1CellInternalKey = memberKey;

      } 

     

      else if(value < bot2) { 

      /*

      * bottom4

      * bottom3

      * bottom2

      * value

      * bottom1

      */

      bot5 = bot4;

      bot4 = bot3;

      bot3 = bot2;

      bot2 = value;

      bot5CellInternalKey = bot4CellInternalKey; 

      bot4CellInternalKey = bot3CellInternalKey; 

      bot3CellInternalKey = bot2CellInternalKey; 

      bot2CellInternalKey = memberKey; 

      } 

      else if(value < bot3) {

      /*

      * bottom4

      * bottom3

      * value

      * bottom2

      * bottom1

      */ 

      bot5 = bot4;

      bot4 = bot3;

      bot3 = value; 

      bot5CellInternalKey = bot4CellInternalKey; 

      bot4CellInternalKey = bot3CellInternalKey; 

      bot3CellInternalKey = memberKey; 

      } 

     

      else if(value < bot4) {

      /*

      * bottom4

      * value

      * bottom3

      * bottom2

      * bottom1

      */ 

      bot5 = bot4;

      bot4 = value;

      bot5CellInternalKey = bot4CellInternalKey; 

      bot4CellInternalKey = memberKey; 

      } 

     

      else if(value < bot5) {

      /*

      * value

      * bottom4

      * bottom3

      * bottom2

      * bottom1

      */ 

      bot5 = value;

      bot5CellInternalKey = memberKey;

      }} 

    }); 

    DS_2.setFilter(“OBJ_87”,[bot1CellInternalKey,bot2CellInternalKey,bot3CellInternalKey,bot4CellInternalKey,bot5CellInternalKey]);

    Many thanks!

    Regards,

    Antonio

    (0) 
    1. Antonio Minkov

      Never mind. Solved it. I didn’t realise the bot variables were all set to 0 and there was no way the variable value could be less than 0.

      Antonio

      (0) 
  4. naveen raju

    Hi Karol,

         i have tried this code and it worked. thanks for the post.. i wanted to know the purpose of pixel-based scrolling property of design studio cross tab..please do give your insights on it .

    Thank you.

    (0) 
  5. naveen raju

    Hi Karol,

    Can we change the dimension and measure name dynamically in the code for top 10 to get ranking for that dimension

    Thank you

    (0) 

Leave a Reply