We know now we have Top N and Bottom N feature in Run time in the context menu of the Cross tab as Filter by Measure. Many people ask whether this Top N or Bottom N is possible by default or in Design time or in script. The best way recommended and many will agree is to have conditions in the back end to handle the Top/Bottom N requirement.

But, just in case if you still need to do it in Design Studio, here is one way to implement Top N or Bottom N using BIAL script for any combination of a dimension and a measure. Using this method, it is also possible to display the total of the selected measure for the top N members of the selected dimension. For simplicity the example here demonstrates Top 5 and Bottom 5 for a dimension and measure combination.


After we create a data source in an application we have the option of creating simple calculations in the data source. We need to add a dynamic calculation on the desired measure and select Rank Number. The dynamic calculation feature was added in the Design Studio 1.6 release. You can add the Rank Number for the measures in the source query (back end) itself and then create a data source on this query.


In the following example “0MEASURES0000000000000009_Formula1” is the Rank Measure created and used.

Create Rank in Initial view of the data source.JPG 

Create Rank in Initial view of the data source

Now in the On Startup event we can write the following script to open the report to show only the Top 5 products by sales on initial load by default.

  Top 5 products – initial load.JPG

Top 5 products – initial load


var members = DS_1.getMembers("ZPRDCT", 100000);
var topBotNmembers = "";
var rankValue = 0.0;
var count = 0;
var n = 5;
members.forEach(function(element, index) {
     if(count != n) {
          rankValue = DS_1.getData("0MEASURES0000000000000009_Formula1", {"ZPRDCT": element.internalKey}).value;
          if (rankValue <= n) {
                topBotNmembers = topBotNmembers + element.internalKey + ";";
                count = count + 1;
          }
     }
});
DS_1.setFilter(dimension, topBotNmembers.split(";"));

This script makes use of the rank measure that we just created. It extracts the rank value for each product and compares it with n, which is equal to 5 in our case. Any member with rank lesser than or equal to 5 will be selected for filtering the data source.

Or if we wanted to show the Bottom 5 products by sales then we can write the following script:

  Bottom 5 products – initial load.JPG

Bottom 5 products – initial load



var members = DS_1.getMembers("ZPRDCT", 100000);
var topBotNmembers = "";
var rankValue = 0.0;
var count = 0;
var n = 5;
var totalNumber = members.length - n;
members.forEach(function(element, index) {
     if(count != n) {
          rankValue = DS_1.getData("0MEASURES0000000000000009_Formula1", {"ZPRDCT": element.internalKey}).value;
          if (rankValue > totalNumber) {
                topBotNmembers = topBotNmembers + element.internalKey + ";";
                count = count + 1;
          }
      }
});
DS_1.setFilter(dimension, topBotNmembers.split(";"));

This is similar to what we did for the Top 5 with only a minor difference. Here we will not be comparing the rank with n; instead we will compare it with n subtracted from the total number of members of that dimension. For example if total members = 50 then we compare rank with 45 and any member with rank greater than 45 will be selected for filtering.

Based on the script above we can enable Top N or Bottom N members of any dimension by a selected measure. We may need a few other components to make Top/Bottom selection, dimension selection, measure selection, and most importantly the value for N.

One problem with this approach is limitations with the ability to pass dynamic JSON pairs. Since this is based on JSON, we may have to write a script for every combination of dimension and measure that we provide in the respective selectors. Also we will have to add rank, in the initial view, for each measure that we provide in the selector.

Below is an example to demonstrate the logic for the scenario we just described. Here we have a script for Top N/Bottom N for 2 different dimensions (Product and Country) by sales. If we provide a selector for measures then we will just need another global variable that holds the technical name of the corresponding rank measure. We can then replace the rank measure in the getData function by the new global variable.

Top N-Bottom N scenario for 2 different dimensions.jpg

Top N/Bottom N scenario for 2 different dimensions

 


DS_1.clearFilter(dimension);
var newDimension = DROPDOWN_2.getSelectedValue();
DS_1.swapDimensions(dimension, newDimension);
DS_2.swapDimensions(dimension, newDimension);
dimension = newDimension;
N = Convert.stringToInt(INPUTFIELD_1.getValue());
var members = DS_1.getMembers(dimension, 100000);
var topBotNmembers = "";
var rankValue = 0.0;
var count = 0;
var totalNumber = members.length - N;
if (DROPDOWN_1.getSelectedValue() == "TOPN") {
     if (dimension == "ZPRDCT") {
          members.forEach(function(element, index) {
               if(count != N) {
                    rankValue = DS_1.getData("0MEASURES0000000000000009_Formula1", {"ZPRDCT": element.internalKey}).value;
                    if (rankValue <= N) {
                         topBotNmembers = topBotNmembers + element.internalKey + ";";
                         count = count + 1;
                    }
               }
         });
     }
     else if (dimension == "ZCUNTRY") {
          members.forEach(function(element, index) {
               if (count != N) {
                    rankValue = DS_1.getData("0MEASURES0000000000000009_Formula1", {"ZCUNTRY": element.internalKey}).value;
                    if (rankValue <= N) {
                         topBotNmembers = topBotNmembers + element.internalKey + ";";
                         count = count + 1;
                    }
               }
        });
     }
 
     DS_2.sortByMeasure("0MEASURES0000000000000009_Formula1", false);
}
else {
     if (dimension == "ZPRDCT") {
          members.forEach(function(element, index) {
               if (count != N) {
                    rankValue = DS_1.getData("0MEASURES0000000000000009_Formula1", {"ZPRDCT": element.internalKey}).value;
                    if (rankValue > totalNumber) {
                         topBotNmembers = topBotNmembers + element.internalKey + ";";
                         count = count + 1;
                    }
               }
          });
     }
    
     else if (dimension == "ZCUNTRY") {
          members.forEach(function(element, index) {
               if (count != N) {
                    rankValue = DS_1.getData("0MEASURES0000000000000009_Formula1", {"ZCUNTRY": element.internalKey}).value;
                    if (rankValue > totalNumber) {
                         topBotNmembers = topBotNmembers + element.internalKey + ";";
                         count = count + 1;
                    }
               }
          });
     }
     DS_2.sortByMeasure("0MEASURES0000000000000009_Formula1", true);
}
DS_1.setFilter(dimension, topBotNmembers.split(";"));
DS_1.sortByMeasure("0MEASURES0000000000000009_Formula1", false);

Here are a few screen shots of the application in runtime. The chart always shows the Top N/Bottom N items and for reference the table below shows all the data with a rank for each member.

Top 5 Products by Sales – initial load.JPG

Top 5 Products by Sales – initial load

The screen shot below shows the result for Top 5 countries by sales, after making changes in the selectors above and applying it to the data source using the script.

Top 5 Countries by Sales.JPG

Top 5 Countries by Sales

 

The screen shot below shows the result for Bottom 5 Countries by Sales.

Bottom 5 Countries by Sales.JPG

Bottom 5 Countries by Sales

The screen shot below shows the result for Top 8 Countries by Sales.

Top 8 Countries by Sales.JPG

Top 8 Countries by Sales

At the end I would definitely recommend you to use the Top/Bottom N feature from the back end.


Back end in the above example is SAP BW. Attached files contain the scripts for reference.

To report this post you need to login first.

9 Comments

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

  1. Chandra Sekhar K

    Hi Swapnil Koti,

    Thanks for giving valuable information.

    When I try Top N scripting in Design Studio 1.5 output shows only 0’s values(Bottom values shows ),

    Error1.PNG

    DS_1.setFilter(dimension, topBotNmembers.split(“;”));  //In dimension place replace by our dimension material placed//


    Please share your valuable inputs.

    Thanks,

    Chandra.K


    (0) 
    1. Swapnil Koti Post author

      Hi Chandra,

      Sorry for a late reply. You are seeing all zeros maybe because the bottom 5 materials actually have zero net sales.

      I don’t know why you are even seeing those materials in the result set when there is no sale of those materials. Can you please check if there are any records actually posted in the database for these materials?

      Thank you.

      Regards,

      Swapnil Koti

      (0) 
      1. Chandra Sekhar K

        Hi Swapnil Koti,

        Thanks for respond.

        When I try to change data source and apply top 5 quantity based on State it shows values from bottom to top 5 values and also shows warning message at output,

        ranking error.PNG

        Ranking Warning in Output.PNG

        Thanks,

        Chandra.K

        (0) 
        1. Swapnil Koti Post author

          Hi Chandra,

          Did you use the rank measure or the quantity measure in the getData function?

          You will need the rank measure for this approach to work.

          In the above example “0MEASURES0000000000000009_Formula1” is the Rank Measure used.

          The blog didn’t have this detail, so I have updated the blog and added this as well.

          Thank you.

          Swapnil Koti

          (0) 
          1. RAHUL KHANNA

            Hi Swapnil
            can you please tell me which version of Design Studio you are using I am using

            Design Studio 16.0.5
            and I am unable to view any of these options “Add Dynamic Calculation”, “Calculate totals As” in measures in design studio

            snap.PNG

            (0) 
            1. Swapnil Koti Post author

              Hi Rahul,

              What is the back end for the application? There are some limitations depending on the back end being used. In the example above I have used BW as back end.

              Swapnil Koti

              (0) 

Leave a Reply