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
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
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
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
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
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
The screen shot below shows the result for Bottom 5 Countries by Sales.
Bottom 5 Countries by Sales
The screen shot below shows the result for Top 8 Countries by Sales.
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.
- SAP Managed Tags:
20 Comments
