Skip to Content
Author's profile photo Karol Kalisz

How to get “Top X” our of resultset

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.

Assigned Tags

      13 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mike Howles
      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.

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog 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..

      Author's profile photo Mike Howles
      Mike Howles

      My stab at it.

      Design Studio SDK - Get Top/Bottom N rankings with a little less code

      I don't think mine was any better, but an alternate approach 🙂

      Thanks for sharing!

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      hi all,

      we have probably a good solution now (thanks to the friendly collaboration with Michael Howles)

      see Design Studio SDK: Collection Util Component

      Karol

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Dirk Mayrock
      Dirk Mayrock

      Hi Karol,

      thanks for looking for a solution that for a long time...

      Dirk

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo naveen raju
      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.

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      please refer to the official documentation, this property is explained there.

      Author's profile photo naveen raju
      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

      Author's profile photo Former Member
      Former Member

      Hi Karol,

      Great article! I tried your script and I'm receiving this error below. "Couldn't resolve reference to Variable 'dataCell'.

      It seems to be an issue with the initialisation of the variable in the if statement. I'm using DS 1.6

      Could you please help.

      Your advice is very much appreciated.

      Thanks,

      Luis

       

       

       

      Author's profile photo Karol Kalisz
      Karol Kalisz
      Blog Post Author

      Hi,

      data cell is defined with "var" in the foreach block.

      allMembers.forEach(function(member, index) {

      var memberKey = member.internalKey;
      var memberText = member.text;

      var dataCell = DS_1.getData("4FW8C4WXM3HULQ4M1YPFT79EF", { "0BC_PERS1": memberKey });

      [...]

      can you check your script?